| I would like to FORCE Oracle to show me what it doesn't want to show me [message #1457] |
Thu, 02 May 2002 07:44  |
massa
Messages: 12
Registered: April 2002
|
Junior Member |
|
|
Hy,
I have a table that contains a lot of columns but two are interesting : name(char) and ind(number).
The name can be repeated a lot of time and so for each name, there can be different ind.
It's a bit like this:
name..........ind
------------ ------------
John..........21
Alex..........10
John..........15
Harry........30
John..........21
Alex..........25
John..........21
Alex..........21
As you can see above, a name can have the same ind a lot of time (see John with 21)
Let's see the following request and it's result:
select name, count(ind) from table where ind=21;
name..........count(ind)
------------ ----------
John..........3
Alex..........1
The problem is that Harry isn't display because Harry doesn't have any ind=21 and I would like to see all the name even if there is a 0 in the count column.
How can I manage to have Oracle show me what I want?
Thanks for the help.
|
|
|
|
|
|
| Re: I would like to FORCE Oracle to show me what it doesn't want to show me [message #1470 is a reply to message #1457] |
Fri, 03 May 2002 07:12  |
Bianka
Messages: 1
Registered: May 2002
|
Junior Member |
|
|
Hi!
How about using PL/SQL? This one should work:
declare
cursor c1 is
select distinct name from yourTable;
var1 varchar2(30);
var2 integer;
begin
dbms_output.put_line( 'Name .... count(ind) ');
dbms_output.put_line( '----------------------------');
open c1;
loop
fetch c1 into var1;
exit when c1%notfound;
select count(ind) into var2 from yourTable where name=var1 and ind=21;
dbms_output.put_line( var1 || '....' || var2);
end loop;
end;
/
Bianka
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
