Requiring assistance in a query Options

[virtual3001]

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.

[burleson]

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

[virtual3001]

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.

[HAL9000]

"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

[Michael Coughlin]

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.