[ORA-SQL]geen output genereren bij NULL

[hellknight]

All,

Ik heb een probleempje met een query.
Het doel is het volgende:
check of er in een 2-tal tabellen data van de afgelopen 2 uur aanwezig is. Is dit niet het geval, dan moet er een pre-defined text als output gegeven worden. Dit werkt allemaal.
Echter, als er wel output is (dus case when count(*) = 0 wordt geëvalueerd als FALSE) moet er geen output gegeven worden - 0 rows, dus. Op dit moment krijgen we echter een lege cell/lege string als output.

de query is als volgt:

SQL:

select case when count(*) = 0 then 'Check FTP logs - no traffic received in last 2 hours' else NULL end AS Status
from (
SELECT distinct inbox_trans_old.file_id, datetime_arrival
    FROM inbox_trans_old, transaction_status, inbox_old, inbox_trans_tag_old
   WHERE substr(inbox_trans_old.file_id,1,3) IN ('ADV','ORD','ITM','CUS')
     AND to_char(inbox_old.datetime_arrival, 'YYYY-MM-DD HH-MM-SS') between to_char(sysdate -(2/24),'YYYY-MM-DD HH-MM-SS') and to_char(sysdate,'YYYY-MM-DD HH-MM-SS')
     AND (inbox_trans_old.trans_status = transaction_status.trans_status(+))
     AND (inbox_trans_old.file_id = inbox_old.file_id)
     AND (inbox_trans_old.file_id = inbox_trans_tag_old.file_id
          AND inbox_trans_old.transaction_id = inbox_trans_tag_old.transaction_id
          AND inbox_trans_tag_old.tag = '1004')
UNION ALL
SELECT distinct inbox_trans.file_id, datetime_arrival
    FROM inbox_trans, transaction_status, inbox, inbox_trans_tag
   WHERE substr(inbox_trans.file_id,1,3) IN ('ADV','ORD','ITM','CUS')
     AND to_char(inbox.datetime_arrival, 'YYYY-MM-DD HH-MM-SS') between to_char(sysdate - (2/24),'YYYY-MM-DD HH-MM-SS') and to_char(sysdate,'YYYY-MM-DD HH-MM-SS')
     AND (inbox_trans.trans_status = transaction_status.trans_status(+))
     AND (inbox_trans.file_id = inbox.file_id)
     AND (inbox_trans.file_id = inbox_trans_tag.file_id
          AND inbox_trans.transaction_id = inbox_trans_tag.transaction_id
          AND inbox_trans_tag.tag = '1004')
          )

De reden dat we echt geen output moeten hebben ipv lege cel/string is dat deze query via een tooltje om het half uur moet gaan draaien, en bij output deze doormailen als attachment. Dit is voor mij en mij collega op applicatiebeheer een trigger om in de logs te duiken, omdat er hoogstwaarschijnlijk problemen zijn met het EDI verkeer van de klant naar ons. Zoals de query nu is krijgen we altijd output, en zouden we dus ieder half uur de attachment moeten openen om te checken of het een alert is, of enkel een lege cell/string, waardoor de query praktisch nutteloos is at the moment.

De oorzaak is vrijwel zeker de "ELSE NULL", maar hoe ik dit moet aanpassen om het gewenste resultaat te bereiken durf ik niet te zeggen

[NMe]

HAVING Status IS NOT NULL?

[hellknight]

HAVING Status IS NOT NULL wordt niet geaccepteerd, als ik HAVING count(*) IS NOT NULL toevoeg aan einde query, dan is het resultaat onveranderd

[SteeringWheel]

Subquery dan maar? (select z.status from (select ...<je originele query hier>...) as z where z.status is not null). Of misschien weet iemand een mooier alternatief

[JaQ]

hellknight schreef op dinsdag 29 juni 2010 @ 12:54:
HAVING Status IS NOT NULL wordt niet geaccepteerd, als ik HAVING count(*) IS NOT NULL toevoeg aan einde query, dan is het resultaat onveranderd

Klopt, maar zoiets als dit moet werken:

code:

select 'Check FTP logs - no traffic received in last 2 hours' as bla, count(*) 
from ( 
SELECT distinct inbox_trans_old.file_id, datetime_arrival 
    FROM inbox_trans_old, transaction_status, inbox_old, inbox_trans_tag_old 
   WHERE substr(inbox_trans_old.file_id,1,3) IN ('ADV','ORD','ITM','CUS') 
     AND to_char(inbox_old.datetime_arrival, 'YYYY-MM-DD HH-MM-SS') between to_char(sysdate -(2/24),'YYYY-MM-DD HH-MM-SS') and to_char(sysdate,'YYYY-MM-DD HH-MM-SS') 
     AND (inbox_trans_old.trans_status = transaction_status.trans_status(+)) 
     AND (inbox_trans_old.file_id = inbox_old.file_id) 
     AND (inbox_trans_old.file_id = inbox_trans_tag_old.file_id 
          AND inbox_trans_old.transaction_id = inbox_trans_tag_old.transaction_id 
          AND inbox_trans_tag_old.tag = '1004') 
UNION ALL 
SELECT distinct inbox_trans.file_id, datetime_arrival 
    FROM inbox_trans, transaction_status, inbox, inbox_trans_tag 
   WHERE substr(inbox_trans.file_id,1,3) IN ('ADV','ORD','ITM','CUS') 
     AND to_char(inbox.datetime_arrival, 'YYYY-MM-DD HH-MM-SS') between to_char(sysdate - (2/24),'YYYY-MM-DD HH-MM-SS') and to_char(sysdate,'YYYY-MM-DD HH-MM-SS') 
     AND (inbox_trans.trans_status = transaction_status.trans_status(+)) 
     AND (inbox_trans.file_id = inbox.file_id) 
     AND (inbox_trans.file_id = inbox_trans_tag.file_id 
          AND inbox_trans.transaction_id = inbox_trans_tag.transaction_id 
          AND inbox_trans_tag.tag = '1004'
         ) 
)
having count(*) > 0
group by bla

Overigens maak je ook wat veel gebruik van datum conversies naar mijn smaak. Als je veel data in je tabel krijgt, ga je daar een probleem mee krijgen (tenzij je een function based index gaat aanleggen).

[ Voor 5% gewijzigd door JaQ op 29-06-2010 13:31 ]

[justmental]

select 'blabla'
from dual
where not exists ()

[NMe]

hellknight schreef op dinsdag 29 juni 2010 @ 12:54:
HAVING Status IS NOT NULL wordt niet geaccepteerd, als ik HAVING count(*) IS NOT NULL toevoeg aan einde query, dan is het resultaat onveranderd

Een count kan sowieso nooit null teruggeven, dus nogal wiedes dat die conditie altijd naar true evalueert.

[hellknight]

NMe schreef op dinsdag 29 juni 2010 @ 13:36:
[...]

Een count kan sowieso nooit null teruggeven, dus nogal wiedes dat die conditie altijd naar true evalueert.

ik was even niet wakker geloof ik, dat gaat inderdaad niet werken.
@JaQ - dat was hem bijna, dank je (Ik wil een waarschuwing, dus output, hebben als er geen data gevonden is over de afgelopen 2 uur, dus count =0, en niet >0)
Ik heb hem nu volgens mij:

SQL:

select 'Check FTP logs - no traffic received in last 2 hours' AS Warning, count(*) 
from ( 
SELECT distinct inbox_trans_old.file_id, datetime_arrival 
    FROM inbox_trans_old, transaction_status, inbox_old, inbox_trans_tag_old 
   WHERE substr(inbox_trans_old.file_id,1,3) IN ('ADV','ORD','ITM','CUS') 
     AND to_char(inbox_old.datetime_arrival, 'YYYY-MM-DD HH24-MI-SS') between to_char(sysdate -(2/24),'YYYY-MM-DD HH24-MI-SS') and to_char(sysdate,'YYYY-MM-DD HH24-MI-SS') 
     AND (inbox_trans_old.trans_status = transaction_status.trans_status(+)) 
     AND (inbox_trans_old.file_id = inbox_old.file_id) 
     AND (inbox_trans_old.file_id = inbox_trans_tag_old.file_id 
          AND inbox_trans_old.transaction_id = inbox_trans_tag_old.transaction_id 
          AND inbox_trans_tag_old.tag = '1004') 
UNION ALL 
SELECT distinct inbox_trans.file_id, datetime_arrival 
    FROM inbox_trans, transaction_status, inbox, inbox_trans_tag 
   WHERE substr(inbox_trans.file_id,1,3) IN ('ADV','ORD','ITM','CUS') 
     AND to_char(inbox.datetime_arrival, 'YYYY-MM-DD HH24-MI-SS') between to_char(sysdate - (2/24),'YYYY-MM-DD HH24-MI-SS') and to_char(sysdate,'YYYY-MM-DD HH24-MI-SS') 
     AND (inbox_trans.trans_status = transaction_status.trans_status(+)) 
     AND (inbox_trans.file_id = inbox.file_id) 
     AND (inbox_trans.file_id = inbox_trans_tag.file_id 
          AND inbox_trans.transaction_id = inbox_trans_tag.transaction_id 
          AND inbox_trans_tag.tag = '1004'
         ) 
)
having count(*) = 0

[justmental]

Daar moet dan nog wel een 'group by' bij.
Having bestaat niet zonder group by.

[Remus]

justmental schreef op woensdag 30 juni 2010 @ 13:02:
Daar moet dan nog wel een 'group by' bij.
Having bestaat niet zonder group by.

Having kan wel zonder group by, want het ontbreken van group by impliceert een group by over alle kolommen.

Actually it does, because a missing group by is an implicit group by over all columns.

[ Voor 17% gewijzigd door Remus op 30-06-2010 13:14 . Reden: Te veel Engels op werk ;) ]

[NMe]

A HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions. However, the SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. To accommodate both standard SQL and the MySQL-specific behavior of being able to refer columns in the SELECT list, MySQL 5.0.2 and up allows HAVING to refer to columns in the SELECT list, columns in the GROUP BY clause, columns in outer subqueries, and to aggregate functions.

For example, the following statement works in MySQL 5.0.2 but produces an error for earlier versions:

SQL:

SELECT COUNT(*) FROM t GROUP BY col1 HAVING col1 = 2;

Mag dus wel, maar is MySQL-specifiek als afkorting.

[The Eagle]

Wat jij zoekt is de NVL functie
http://www.orafaq.com/wiki/NVL

Is een Oracle PL/SQL functie dus bij andere DB's niet aanwezig, maar werkt als een tierelier

Je zou ook even naar de nvl2 functie kunnen kijken, maar ik meen dat die er pas vanaf 11g inzit.

[hellknight]

Thanks all,
Ik heb de query getest door hem over de productie-db te draaien (waar de EDI momenteel goed loopt) en over de test-db, welke identiek is qua opbouw, maar waar momenteel geen EDI binnenkomt. In de testomgeving krijg ik netjes een waarschuwing, in productie krijg ik netjes geen output.
De query werkt prima (db is Oracle 9i)

[JaQ]

hellknight schreef op dinsdag 29 juni 2010 @ 14:30:
@JaQ - dat was hem bijna, dank je (Ik wil een waarschuwing, dus output, hebben als er geen data gevonden is over de afgelopen 2 uur, dus count =0, en niet >0)

Je snapt het idee (en ik zat niet op te letten )

NMe schreef op woensdag 30 juni 2010 @ 13:24:
Mag dus wel, maar is MySQL-specifiek als afkorting.

De vraag was voor Oracle toch? Justmental heeft in Oracle lingo gelijk

The Eagle schreef op woensdag 30 juni 2010 @ 13:30:
Wat jij zoekt is de NVL functie
http://www.orafaq.com/wiki/NVL

Is een Oracle PL/SQL functie dus bij andere DB's niet aanwezig, maar werkt als een tierelier

Juist niet, want het idee was enkel output als count(*) > 0 is. Dat gaat imo nvl niet oplossen.

en technisch is nvl geen PL/SQL functie, aangezien deze in C in de kernel van Oracle zit gebakken, maar dat is piet-leut-gezeik

The Eagle schreef op woensdag 30 juni 2010 @ 13:30:
Je zou ook even naar de nvl2 functie kunnen kijken, maar ik meen dat die er pas vanaf 11g inzit.

Vanaf 8.1.7 (release 3)

[ Voor 5% gewijzigd door JaQ op 30-06-2010 15:15 ]

[NMe]

JaQ schreef op woensdag 30 juni 2010 @ 15:14:
[...]

De vraag was voor Oracle toch? Justmental heeft in Oracle lingo gelijk

Ik zeg toch ook niet dat één van de twee gelijk of ongelijk heeft?

[hellknight]

JaQ schreef op woensdag 30 juni 2010 @ 15:14:
[...]

Juist niet, want het idee was enkel output als count(*) > 0 is. Dat gaat imo nvl niet oplossen.

NVL had misschien gekund, want er moest output komen bij count(*)=0, alleen zou ik zo snel niet weten hoe ik deze zou moeten toepassen i.c.m. de union all. Als de query over 1 set tabellen was gegaan, had ik de NVL mogelijk wel toegepast.

[JaQ]

NMe schreef op woensdag 30 juni 2010 @ 15:25:
Ik zeg toch ook niet dat één van de twee gelijk of ongelijk heeft?

Je schreef het niet op, maar je opmerking impliceerde wel dat je één van beide gelijk gaf

hellknight schreef op woensdag 30 juni 2010 @ 15:27:
want er moest output komen bij count(*)=0,

doe ik het weer

hellknight schreef op woensdag 30 juni 2010 @ 15:27:
NVL had misschien gekund, want er moest output komen bij count(*)=0, alleen zou ik zo snel niet weten hoe ik deze zou moeten toepassen i.c.m. de union all. Als de query over 1 set tabellen was gegaan, had ik de NVL mogelijk wel toegepast.

Ik zie niet hoe een NVL iets kan doen in deze selectie. Je wilt immers enkel output als count(*)=0 nu wel goed . Als count(*)>0 dan wil je toch geen output?

Eventueel zou decode wel kunnen: decode(count(*), 0, 'verhaal',null).
nvl2 gaat ook niet werken, immers die gaat uit van een NULL return (maar je krijgt 0 of >0)

[hellknight]

JaQ schreef op woensdag 30 juni 2010 @ 15:47:
Ik zie niet hoe een NVL iets kan doen in deze selectie. Je wilt immers enkel output als count(*)=0 nu wel goed . Als count(*)>0 dan wil je toch geen output?

Eventueel zou decode wel kunnen: decode(count(*), 0, 'verhaal',null).
nvl2 gaat ook niet werken, immers die gaat uit van een NULL return (maar je krijgt 0 of >0)

Een NVL zou moeten werken, als ik de union niet had, en dus maar 1 set tabellen moest raadplegen.
Iets als

SQL:

SELECT nvl(distinct inbox_trans_old.file_id,'Check FTP logs - no traffic received in last 2 hours')
    FROM inbox_trans_old, transaction_status, inbox_old, inbox_trans_tag_old
   WHERE substr(inbox_trans_old.file_id,1,3) IN ('ADV','ORD','ITM','CUS')
     AND to_char(inbox_old.datetime_arrival, 'YYYY-MM-DD HH-MM-SS') between to_char(sysdate -(2/24),'YYYY-MM-DD HH-MM-SS') and to_char(sysdate,'YYYY-MM-DD HH-MM-SS')
     AND (inbox_trans_old.trans_status = transaction_status.trans_status(+))
     AND (inbox_trans_old.file_id = inbox_old.file_id)
     AND (inbox_trans_old.file_id = inbox_trans_tag_old.file_id
          AND inbox_trans_old.transaction_id = inbox_trans_tag_old.transaction_id
          AND inbox_trans_tag_old.tag = '1004') 

zou volgens mij moeten werken - als de select geen data oplevert, dus NULL, moet er een waarschuwing als output komen.
Ik heb de NVL eigenlijk enkel bij mn Oracle SQL Fundamentals 1 training gebruikt, en later nooit meer, dus kan best zijn dat ik me vergis)

[JaQ]

hellknight schreef op woensdag 30 juni 2010 @ 16:37:
Een NVL zou moeten werken, als ik de union niet had, en dus maar 1 set tabellen moest raadplegen.
Iets als

SQL:

SELECT nvl(distinct inbox_trans_old.file_id,'Check FTP logs - no traffic received in last 2 hours')
    FROM inbox_trans_old, transaction_status, inbox_old, inbox_trans_tag_old
   WHERE substr(inbox_trans_old.file_id,1,3) IN ('ADV','ORD','ITM','CUS')
     AND to_char(inbox_old.datetime_arrival, 'YYYY-MM-DD HH-MM-SS') between to_char(sysdate -(2/24),'YYYY-MM-DD HH-MM-SS') and to_char(sysdate,'YYYY-MM-DD HH-MM-SS')
     AND (inbox_trans_old.trans_status = transaction_status.trans_status(+))
     AND (inbox_trans_old.file_id = inbox_old.file_id)
     AND (inbox_trans_old.file_id = inbox_trans_tag_old.file_id
          AND inbox_trans_old.transaction_id = inbox_trans_tag_old.transaction_id
          AND inbox_trans_tag_old.tag = '1004') 

zou volgens mij moeten werken - als de select geen data oplevert, dus NULL, moet er een waarschuwing als output komen.
Ik heb de NVL eigenlijk enkel bij mn Oracle SQL Fundamentals 1 training gebruikt, en later nooit meer, dus kan best zijn dat ik me vergis)

bijna je hebt tenminste een sql fundamentals training gedaan, dat scheelt al....

code:

SELECT nvl(distinct inbox_trans_old.file_id,'Check FTP logs - no traffic received in last 2 hours')

geeft voor iedere output rij die niet NULL is vervolgens distinct inbox_trans_old.file_id terug (en dat kunnen er best veel zijn). Lijkt me niet helemaal de bedoeling?

Een ander grappig iets is de select distinct, union all, select distinct. Is er een reden waarom je dat doet ipv een select union select? (volgens mij doet dat namelijk impliciet hetzeflde, enkel doe je 1 sort ipv 2)

Maar goed, genoeg gemierenneukt.

[The Eagle]

hellknight schreef op woensdag 30 juni 2010 @ 16:37:
[...]

Een NVL zou moeten werken, als ik de union niet had, en dus maar 1 set tabellen moest raadplegen.
Iets als

SQL:

SELECT nvl(distinct inbox_trans_old.file_id,'Check FTP logs - no traffic received in last 2 hours')
    FROM inbox_trans_old, transaction_status, inbox_old, inbox_trans_tag_old
   WHERE substr(inbox_trans_old.file_id,1,3) IN ('ADV','ORD','ITM','CUS')
     AND to_char(inbox_old.datetime_arrival, 'YYYY-MM-DD HH-MM-SS') between to_char(sysdate -(2/24),'YYYY-MM-DD HH-MM-SS') and to_char(sysdate,'YYYY-MM-DD HH-MM-SS')
     AND (inbox_trans_old.trans_status = transaction_status.trans_status(+))
     AND (inbox_trans_old.file_id = inbox_old.file_id)
     AND (inbox_trans_old.file_id = inbox_trans_tag_old.file_id
          AND inbox_trans_old.transaction_id = inbox_trans_tag_old.transaction_id
          AND inbox_trans_tag_old.tag = '1004') 

zou volgens mij moeten werken - als de select geen data oplevert, dus NULL, moet er een waarschuwing als output komen.
Ik heb de NVL eigenlijk enkel bij mn Oracle SQL Fundamentals 1 training gebruikt, en later nooit meer, dus kan best zijn dat ik me vergis)

Als de NVL je handig lijkt en je wilt het zonder union oplossen, dan maak je toch twee views met de losse queries als basis? Levert twee waarden op; als een van beide op een bepaalde waarde staat moet je je zorgen gaan maken
Dan doe je dus een query op die twee tabellen: select sum(a.waarde + b.waarde) from tabel1 A, tabel2 B;

Met die output kun je gegarandeerd iets