Supress Blanks

[sammybee]

Hi All,

I have a script where the results look as follows

B C D
row1 1 3 6
row2
row3 4 5 6


How could I ammend my query to not show row2 as there are no results for this row???

Many thanks

Sam

 

[carp]

Add the following WHERE clause to your query:

WHERE b IS NOT NULL
OR c IS NOT NULL
OR d IS NOT NULL

 

[sammybee]

Hi Carp, I tried adding that to my where clause and get the following error:


ORA-00920: invalid relational operator

Any ideas?

Cheers

Sam

 

[sammybee]

Sorry Carp, I'm getting invalid column error ORA 0904

 

[carp]

Please post your query so we can see where the problem is.

 

[sammybee]

Hi Carp,

As below:


SET linesize 9999
COL a heading "<25|days|old" format 99,999
COL b heading "<35|days|old" format 99,999
COL c heading "<56|days|old" format 99,999
COL d heading ">56|days|old" format 99,999
SELECT gan_char_value, sdco_name,
SUM (CASE
WHEN als_created_date BETWEEN SYSDATE - 25 AND SYSDATE
THEN 1
END
) b,
SUM (CASE
WHEN als_created_date BETWEEN SYSDATE - 35 AND SYSDATE
THEN 1
END
) b,
SUM (CASE
WHEN als_created_date BETWEEN SYSDATE - 56 AND SYSDATE
THEN 1
END
) b,
SUM (CASE
WHEN als_created_date > SYSDATE - 56
THEN 1
END) b
FROM applic_list_entries,
applic_list_stage_decisions,
general_answers,
housing.stage_decision_codes
WHERE ( a is not null or b IS NOT NULL
OR c IS NOT NULL
OR d IS NOT NULL)
AND ale_lst_code = 'CURR'
AND ale_app_refno = als_ale_app_refno(+)
AND als_rls_code IN ('S64', 'DECNREVU', 'OFFREVU')
AND als_ale_app_refno = gan_app_refno(+)
AND gan_que_refno = 677
AND als_rsd_hrv_lsd_code = sdco_code(+)
GROUP BY gan_char_value, sdco_name


Cheers Sam

 

[SantaMufasa]

Sam and Carp,

I hope you don't mind my jumping into the fray, but there are several issues with your query that we should look at. First, Sam, are you sure that your CASE statements are working as you wish? When I posted the CASE code for you yesterday, it was counting date entries only once. Your current code could count the same row up to 4 times -- if a "als_created_date" contains yesterday's date, it will tally for each of your four conditions since it is between 25, 35, 55, and greater days ago and today.

Second, you have given a column alias of "b" to each of your four CASE columns.

Third, you cannot refer to a column alias in your WHERE clause of the same SELECT. In other words, you cannot say:

...WHERE ( a is not null or  b IS NOT NULL
      OR  c IS NOT NULL
      OR   d IS NOT NULL)

...since b is an alias and a,c, and d should give you "undefined" errors.

Frankly, I cannot see how the code you posted gave the results you posted. Are you sure you are not using some "smoke and mirrors" on us?

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:06 (21Dec04) UTC (aka "GMT" and "Zulu"),
@ 10:06 (21Dec04) Mountain Time

 

[carp]

SantaMufasa - As far as I'm concerned, "mi fray, su fray"! Jump in any time!
Sam -
As Dave quite rightly points out - your code is shot through with problems and he has pointed them out for you. Get things fixed up IAW Dave's suggestions and give it another shot.

 

[sammybee]

Hey Both,

Sorry about the errors, noticed as soon as I re-run, that the b aliases for the CASE's were wrong they should be a, b, c and d respectively. The results however are correct I do want to count those already counted in A in B, C and D too. The issue I'm having if I can't use aliases in the where clause is how to suppress the blanks.

Sorry for making things complex, any ideas?

Cheers

Sam

 

[sem]

SELECT * FROM (<your query>)
WHERE b IS NOT NULL
OR c IS NOT NULL
OR d IS NOT NULL

Regards, Dima

http://seminihin.narod.ru

 

[hoinz]

Hi,

good point, sem!

I would have suggested the canonical way:
In your where clause you have to substitute "a" by what it stands for, i.e.
SUM (CASE WHEN als_created_date BETWEEN SYSDATE - 25 AND SYSDATE THEN 1 END)
And accordingly for b, c, d.

Another idea:
It seems that it suffices to check d.
If d is not null, than a, b, c cannot be null either. Correct?

regards

 

[hoinz]

Sorry,
Just noticed that it is the other way round:
Only check a.

 

[hoinz]

sorry once again

"only check D" was correct, but the my original reasoning was wrong:
If d is null, a, b, d must be null as well.
And if d is not null, it doesn't matter whether a, b, c are null.

Seems I should go home.

 

[sammybee]

Hi All,

Dima that was perfect, cheers all for help;-)

Sam