Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Requiring assistance in a query
virtual3001
post Apr 29 2010, 07:11 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 29-April 10
Member No.: 43,017



Hi

I have the following resultset:

Jan-10 B 10
Jan-10 C 20
Feb-10 A 40
Feb-10 C 25
Mar-10 A 10
Mar-10 B 20
Mar-10 C 30

What I intend to get in the resultset is:

Jan-10 A 0
Jan-10 B 10
Jan-10 C 20
Feb-10 A 40
Feb-10 B 0
Feb-10 C 25
Mar-10 A 10
Mar-10 B 20
Mar-10 C 30

That is, the second column should always be consistent. If there is no value, then it should automatically be inserted with a default value of zero in the third column.

I use the decode function to get the values for the second column.

Is it possible to achieve this through a query? Kindly help me.
Go to the top of the page
 
+Quote Post
burleson
post Apr 29 2010, 07:48 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,640
Joined: 26-January 04
Member No.: 13



Hi,

>> If there is no value, then it should automatically be inserted with a default value of zero in the third column.

This sounds like a job for the NVL function:

http://dba-oracle.com/t_oracle_nvl.htm

Just wrap your decode in the NVL clause.

If you want to learn advanced Oracle SQL fast, see here:

http://www.rampant-books.com/book_0802_adv...programming.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
virtual3001
post Apr 29 2010, 08:19 AM
Post #3


Newbie
*

Group: Members
Posts: 2
Joined: 29-April 10
Member No.: 43,017



Hi

Thanks for your support. But what I intend to get is, in my above example, Jan 2010 does not have the value 'A' in the database. Similarly, Feb 2010 does not have 'B', whereas March 2010 has all three values A, B and C. The third column basically shows the count of A, B and C in these three months. Now what I require is every month should display A, B, C irrespective of whether it is present in that month. If it is not present, then it should be made available and the count for the same should be zero. I am basically looking for having a kind of static list of all possible values in the database in the second column (that is, A, B and C) and then getting the corresponding count in the third column.

Kindly let me know if this is possible.
Go to the top of the page
 
+Quote Post
HAL9000
post Apr 29 2010, 04:16 PM
Post #4


Advanced Member
***

Group: Members
Posts: 880
Joined: 25-September 07
Member No.: 12,336



"If it is not present, then it should be made available and the count for the same should be zero"

Yes. I would write this in PL/SQL.

If you don't know how to write a computer program to implement your specs, see here:

http://www.rampant-books.com/book_0501_easy_plsql.htm

Go to the top of the page
 
+Quote Post
Michael Coughlin
post Jul 20 2010, 12:29 PM
Post #5


Newbie
*

Group: Members
Posts: 1
Joined: 20-July 10
From: Pickerington, OH
Member No.: 43,512



Here is a NOT-thought out response, but I wanted to throw something together that seemed to be a real simple solution. I imagine that it would depend on the size of the data set if this was a real bad Idea and the "letterlist" needs to come from somewhere, that can be done a different way if selecting a distinct list from the main source isn't valid. But in essence this meets the question asked.

create table schemaname.my_table (date1 date, letter varchar2(1));
declare
begin
delete from SCHEMANAME.MY_TABLE;

for i in 1..10 loop
insert into SCHEMANAME.MY_TABLE values
(to_date('2010-01-10','yyyy-mm-dd hh24:mi:ss'), 'B');
insert into SCHEMANAME.MY_TABLE
Values(
to_date('2010-03-10','yyyy-mm-dd hh24:mi:ss'),
'A')
;
end loop;
for i in 1..20 loop
insert into SCHEMANAME.MY_TABLE values
(to_date('2010-01-10','yyyy-mm-dd hh24:mi:ss'), 'C');
insert into SCHEMANAME.MY_TABLE
Values(
to_date('2010-03-10','yyyy-mm-dd hh24:mi:ss'),
'B')
;
end loop;
for i in 1..30 loop
insert into SCHEMANAME.MY_TABLE values
(to_date('2010-03-10','yyyy-mm-dd hh24:mi:ss'), 'C');
end loop;
for i in 1..25 loop
insert into SCHEMANAME.MY_TABLE values
(to_date('2010-02-10','yyyy-mm-dd hh24:mi:ss'), 'C');
end loop;
for i in 1..40 loop
insert into SCHEMANAME.MY_TABLE values
(to_date('2010-02-10','yyyy-mm-dd hh24:mi:ss'), 'A');
end loop;
commit;
end;



select date1, letter, count(*) from SCHEMANAME.MY_TABLE group by date1, letter order by date1, letter;

select distinct letter as letterlist from SCHEMANAME.MY_TABLE;

select date1, letterlist, sum(decode(letterlist,letter,1,0))
from
(select distinct letter as letterlist from SCHEMANAME.MY_TABLE),
SCHEMANAME.MY_TABLE group by date1, letterlist order by 1,2,3;

You can see the final query takes advantage of a cartesian join. This may be a bad idea in many cases, but it might be justifiable as the best solution since this does exactly what you asked. Just 2 cents, I'm sure this can be improved upon or maybe proven just plain dumb.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 1st November 2014 - 08:13 AM