Show null results in SQL loop

[SidUK]

Hi - I suspect this is a basic SQL issue.

I'm trying to create a crosstab from three sets of tables and fields that are selected by the user. 1, the columns, 2, the rows and 3, the values.

I am using a three dynamic SQL cursors, one after the other, to show:
First, the columns (simple group by on the field selected in 1),

Second, the row value (group by on field selected in 2, and if it's the first value in this loop then also the total),

And then for each of the column values, I am showing the count of the field selected in 3, restricted to the row value in question.

The problem I'm having is that I need the nulls to be displayed.

Any ideas beyond using outer joins?

TIA, SidUK

 

[SantaMufasa]

Sid,

This is one of those cases where my finite little mind needs that picture to take the place of 1000 words. could you please post a sample listing of table contents plus the results you would like to display? Be sure to place those input and output samples inside of

 and

delimiters so that the results line up properly.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SidUK]

Hi Mufasa

On the sample of table contents, the user can select any table from a web page drop down box, and then the fields that relate to the table they have selected are displayed in another drop down box. This is done 3 times, one for the values that are needed in the rows (row_name and row_val in the code), another for the values in the columns (col_name and col_val) and another for the values themselves (val_name and val_val). The code within the procedure I am using is:

sql_code varchar2(1500);
 TYPE EmpCurTyp IS REF CURSOR;
  cur EmpCurTyp;
trow_extract varchar2(150);
tval_extract varchar2(150);
trow_num varchar2(150);

sql_code2 varchar2(1500);
  cur2 EmpCurTyp;
tcol_extract varchar2(150);
tcolval_extract varchar2(150);
tcol_num varchar2(150);

sql_code_col varchar2(1500);
  cur3 EmpCurTyp;
tcol_sum varchar2(150);

rowcheck number(6);

begin

htp.print('
<table>
<tr>
<td colspan="2">
<font face="arial" size="1">
Crosstab using the value of '||val_name||'_'||val_val||'
<tr>
<td>
<font face="arial" size="1">
'||row_name||'_'||row_val||'
<tr>
<td><td>
<font face="arial" size="1">
Totals');

sql_code_col := 
'select distinct
'||col_name||'.'||col_val||'  as col_extract
from 
--tables--
where
--statement--
group by '||col_name||'.'||col_val||'';

open cur3 for sql_code_col;
loop

fetch cur3 into tcol_sum;
exit when cur3%NOTFOUND;

htp.print('
<td>
<font face="arial" size="1">
'||tcol_sum||'
');

end loop;
close cur3;

sql_code := 
'select 
'||row_name||'.'||row_val||',
count(distinct '||val_name||'.'||val_val||') as val_extract
from 
--tables--
where
--statement--
group by '||row_name||'.'||row_val||'';

open cur for sql_code;
loop

fetch cur into trow_extract, tval_extract;
exit when cur%NOTFOUND;

rowcheck :=1;

htp.print('
<tr>
<td>
<font face="arial" size="1">
'||trow_extract||'');

if rowcheck = 1 then
htp.print('
<td align="right">
<font face="arial" size="1">
'||tval_extract||'
');
end if;

sql_code2 := 
'select
'||col_name||'.'||col_val||',
count(distinct '||val_name||'.'||val_val||') as val_extract
from 
--tables--
where
--statement-- and
'||row_name||'.'||row_val||'='''||trow_extract||'''
group by '||col_name||'.'||col_val||'';

open cur2 for sql_code2;
loop

fetch cur2 into tcol_extract, tcolval_extract;
exit when cur2%NOTFOUND;

rowcheck := rowcheck +1;

htp.print('
<td>
<font face="arial" size="1">
'||tcolval_extract||'
');

end loop;


close cur2;

end loop;
close cur;

I've replaced the tables list and statement with comment for simplicity.

Is this what you were after?

Rgds Sid

 

[SantaMufasa]

Sid, my man...It's 2:25 a.m. here...it's waaaay too late for "recreational code reading". Pleeeeease just post a simple, sample of a typical table, its columns, a few rows of data, and how you would want the output to appear following the user choices to which you refer.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SidUK]

Sorry!

Basically, I think my problem boils down to:

Country table:
ID Country
1 UK
2 US

Organisation_type table:
Country Type
1 Reseller
1 Reseller

Select
country.country,
count(ot.type)
from
organisation_type ot,
country
where
ot.country = country.id
group by country.country

And what I need is:

UK 2
US 0

It's the US 0 that's the problem. All I can get is:

UK 2

Sid

 

[SantaMufasa]

Ah, yes...That's exactly what the doctor ordered. To produce what you want from those two tables (as an example), you can use what's called an "left-outer join", that is a "join for left-out rows":

SQL> col country format a7
SQL> select * from country;

        ID COUNTRY
---------- -------
         1 UK
         2 US

2 rows selected.

SQL> select * from Organisation_type;

   COUNTRY TYPE
---------- ----------
         1 Reseller
         1 Reseller

2 rows selected.

select a.country,count(b.country)Count
from country a, Organisation_type b
where a.id = b.country(+)
group by a.country
/

COUNTRY      COUNT
------- ----------
UK               2
US               0

2 rows selected.

We can use the "(+)" symbol (as a "headstone" to represent "ghost rows") in the WHERE clause on the side of the comparison where Oracle needs to create "ghost rows"...in this case for "US rows" the "ORGANISATION_TYPE" table.

Let us know if this is what you needed.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SidUK]

Thanks for that Mufasa

If the SQL was:

select a.country,count(b.country)Count
from country a, Organisation_type b
where a.id = b.country(+) and b.type='reseller'
group by a.country

Could you do something similar?

Kind regards, Sid

 

[SantaMufasa]

Sure, but you will get "no rows selected" since your code requests 'reseller' but Organisation_type.type contains 'Reseller'. If you fix that, then your code and output will be:

select a.country,count(b.country)Count
from country a, Organisation_type b
where a.id = b.country(+) and b.type='[b]Reseller[/b]'
group by a.country;

COUNTRY      COUNT
------- ----------
UK               2

1 row selected.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SidUK]

Thanks very much for your efforts Mufasa - especially given the time in the morning.

Kind regards, Sid

 

[stefanhei]

I think the inhuman time Dave was answering made him misunderstand your last question. If you still wish to display all countries your code should look like:

select a.country,count(b.country)Count
from country a, Organisation_type b
where a.id = b.country(+) and b.type[COLOR=red](+)[/color]='Reseller'
group by a.country;

As without the second (+) the outer join is negated.
Stefan

 

[SantaMufasa]

Good catch, Stefan. I was wondering why Sid would want to see "b.type ='Reseller'" since that negatates the outer join. You are right...It didn't dawn on me that he was wonder about also outer joining "b.type(+)='Reseller'".

Thanks, Stefan, for your safety netting.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SidUK]

Thanks Guys - much appreciated.

Sid