[ORA-SQL] alleen output tussen bepaalde tijden

[hellknight]

De query uit mijn vorige topic ([ORA-SQL]geen output genereren bij NULL) werkt prima, maar ik loop tegen 1 nadeel op:
Doordat de query ook 's nachts en in het weekend blijft draaien (query is in een externe applicatie iedere 30 min gedraaid) en er dan meestal geen EDI verkeer is, hebben we iedere ochtend een flinke zooi alarm-mails in onze mailbox
De applicatie bied helaas niet de mogelijk in te stellen dat een query enkel ma-vr 08.00 - 17.00 gedraaid moet worden. Dit zou ik nu dus in de query zelf willen verzorgen: ik wil enkel output tussen die tijden (en bij voorkeur ook enkel op ma t/m vr)
Voor de beperking op de tijden had ik het volgende in gedachte:

SQL:

where to_char(sysdate,'HH24') BETWEEN 08 AND 17

Dit blijkt niet te werken.

totale query is dus

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'))
where to_char(sysdate,'HH24') BETWEEN 08 AND 17
having count(*) = 0

Zorgen dat we enkel output krijgen op ma-vr was ik nog niet aan uit hoe dat zou moeten - ongetwijfeld ook via de sysdate functie, maar verder ben ik nog niet gekomen.
Hopelijk kan een van jullie me vertellen wat ik verkeerd doe

[The Eagle]

Zoek maar eens op dbms.scheduler:
http://www.orafaq.com/wiki/DBMS_SCHEDULER

[Motrax]

1) Je vergelijkt de sysdate tegen de bedrijfsuren, maar dat is dus het moment waarop jouw select query draait, niet handig. Je zal op de aanmaakdatum van de logregel zelf moeten selecteren.
2) Je doet een TO_CHAR(), maar je vergelijkt met een integer, dat is niet zuiver. Oracle lijkt het wel te accepteren.

Succes

Edit: sysdate is wel correct omdat je geen output wil hebben wanneer het buiten de kantooruren is.

[ Voor 13% gewijzigd door Motrax op 12-07-2010 13:35 ]

[KabouterSuper]

Wat geeft to_char(sysdate,'HH24') dan terug? Ik zie (afgezien van het datatype) geen probleem waarom het niet zou werken.

Qua dag in de week.....cast je sysdate naar de dag in de week in het Engels en zorg dat deze niet met een S begint.... zo even uit mijn hoofd

code:

substr(to_char(sysdate,'Day','NLS_DATE_LANGUAGE = AMERICAN'),1,1)!='S'

[Motrax]

Ik kwam zo even op het volgende uit, dit werkt alleen voor de maandag op het moment:

SQL:

WHERE DECODE(TO_CHAR(SYSDATE, 'D', 1, 1, 0) = 1

Grappig dat je op een andere benadering uit komt. Bij mijn query kies ik voor het nummer van de dag van de week om geen taalinstelling te benaderen. Jij hebt er voor gekozen om de taalinstelling juist hard op te nemen in de query. Weer wat geleerd En je query werkt trouwens, net even op DUAL losgelaten. Ik krijg een mooie 'M' terug.

KabouterSuper schreef op maandag 12 juli 2010 @ 13:45:
Wat geeft to_char(sysdate,'HH24') dan terug? Ik zie (afgezien van het datatype) geen probleem waarom het niet zou werken.

Het geeft op moment van schrijven een hele mooie '13' terug. Een integer in de vergelijking wordt geaccepteerd, maar ik neem aan dat Oracle het omzet naar een string om te vergelijken met '13'. Ik hou van dingen zuiver te houden, dus als je een TO_CHAR() om iets heen zet, dan moet je ook met een string vergelijken en niet met een integer.

Het korte antwoord is: het werkt allebei

justmental schreef op maandag 12 juli 2010 @ 14:02:
Let op dat dagnummers ook wisselen tussen language instellingen:

code:

alter session set nls_territory='america';
select to_char(sysdate,'d') from dual;
alter session set nls_territory='the netherlands';
select to_char(sysdate,'d') from dual;

Major oeps

[ Voor 52% gewijzigd door Motrax op 12-07-2010 14:05 . Reden: Post is van 13:xx en niet van 14:xx, de edit wel... ]

[justmental]

Let op dat dagnummers ook wisselen tussen language instellingen:

code:

alter session set nls_territory='america';
select to_char(sysdate,'d') from dual;
alter session set nls_territory='the netherlands';
select to_char(sysdate,'d') from dual;

[Remus]

Motrax schreef op maandag 12 juli 2010 @ 13:56:
[...]
Major oeps

Is geen oeps, in de VS is zondag de eerste dag van de week, in Nederland is dat maandag.

[KabouterSuper]

Maar veel databases staan niet ingesteld met Nederlandse settings....

Je kunt wellicht in je to_char een nls_territory specificeren. De vraag is echter of dit foolproof is (wellicht hangt het ook af van andere nls-settings). Checken op de eerste letter is foolproof en intuitief (ook al is het een mazzeltje dat alle weekend-dagen in het Engels met een S beginnen).

[Verwijderd]

KabouterSuper schreef op dinsdag 13 juli 2010 @ 08:44:
Checken op de eerste letter is foolproof en intuitief (ook al is het een mazzeltje dat alle weekend-dagen in het Engels met een S beginnen).

Foolproof misschien wel, maar intuitief? Als ik de volgende code zie:

SQL:

..WHERE SUBSTR(TO_CHAR(datum, 'DAY'), 1, 1) != 'S'

en vergelijk met

SQL:

..WHERE TO_CHAR(datum, 'DAY') NOT IN ('Sat', 'Sun')

dan is de tweede toch een heel stuk intuitiever..

OT: de reden dat de WHERE clause van de OP niet werkt, ligt waarschijnlijk aan het feit dat TO_CHAR een VARCHAR2 teruggeeft, waarna dit wordt vergeleken met twee getallen. Oracle zal de getallen omzetten naar VARCHAR2's; de WHERE clause

SQL:

..WHERE TO_CHAR(datum, 'HH24') BETWEEN 08 AND 17

wordt dan

SQL:

..WHERE TO_CHAR(datum, 'HH24') BETWEEN '8' AND '17'

Aangezien de string '17' alfabetisch gesproken kleiner is dan de string '8', komt hier nooit wat uit. Dit probleem kan simpel worden opgelost door het resultaat van TO_CHAR expliciet om te zetten naar een getal:

SQL:

..WHERE TO_NUMBER(TO_CHAR(datum, 'HH24')) BETWEEN 8 AND 17

[hellknight]

Helaas, ook dat werkt niet - als ik de query in de testomgeving draai, waar geen EDI is ontvangen, en ik wijzig de criteria naar 11 and 17, dan krijg ik nog altijd de waarschuwing.

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'))
WHERE TO_NUMBER(TO_CHAR(sysdate, 'HH24')) BETWEEN 12 AND 17
--AND TO_CHAR(sysdate, 'DAY') NOT IN ('Sat', 'Sun')
having count(*) = 0

[justmental]

Denk nog eens over je logica dan.
Je legt een extra beperking op de records op en je meldt de waarschuwing bij 0 records.
Dus je krijgt vaker een waarschuwing.

[Remus]

justmental schreef op dinsdag 13 juli 2010 @ 10:58:
Denk nog eens over je logica dan.
Je legt een extra beperking op de records op en je meldt de waarschuwing bij 0 records.
Dus je krijgt vaker een waarschuwing.

Waarschijnlijk werkt een CASE in de SELECT waarbij afhankelijk van de tijd wel of geen boodschap wordt getoond beter. En anders moet je de huidige select als inner-select opnemen in een nieuwe select die gebaseerd op de tijd wel of niet de waarschuwing retourneert.

[leuk_he]

justmental schreef op dinsdag 13 juli 2010 @ 10:58:
Denk nog eens over je logica dan.
Je legt een extra beperking op de records op en je meldt de waarschuwing bij 0 records.
Dus je krijgt vaker een waarschuwing.

Ik zou het nog een nivo hoger willen tillen. In het verleden hebben we in het order systeem wel eens een alive systeem geintroduceerd. Elk uur een order inschieten in systeem 1 (SAP) en vervolgens kijken of er elk uur bij de dummy leverancier (systeem 3) een order binnen komt. Daarmee vang je meer stappen van je bedrijfkritisch systeem af.

[hellknight]

justmental schreef op dinsdag 13 juli 2010 @ 10:58:
Denk nog eens over je logica dan.
Je legt een extra beperking op de records op en je meldt de waarschuwing bij 0 records.
Dus je krijgt vaker een waarschuwing.

Hmm, je hebt gelijk - daar had ik even niet aan gedacht

Remus schreef op dinsdag 13 juli 2010 @ 11:09:
[...]
...En anders moet je de huidige select als inner-select opnemen in een nieuwe select die gebaseerd op de tijd wel of niet de waarschuwing retourneert.

Dat is volgens mij de oplossing - ik heb nu deze query staan, en vooralsnog lijkt deze te werken.
Ik heb hem even op de testomgeving gedraaid, en wederom getest door de uren aan te passen in de query, en dat ziet er allemaal goed uit.
Query zoals hij nu dus is:

SQL:

SELECT *
FROM (
    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)
WHERE TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) BETWEEN 8 AND 17
AND TO_CHAR(SYSDATE, 'DAY') NOT IN ('Sat', 'Sun')

@all - bedankt voor de bijdragen so far - ik heb van dit en vorig topic best wat zaken bijgeleerd.

[Verwijderd]

Die laatste regel in je WHERE clause zal niet veel doen - mijn voorbeeldje was min of meer ongeteste pseudocode, sorry! Om die laatste regel werkend te krijgen zal je gebruik moeten maken van de NLS_DATE_LANGUAGE parameter, zoals KabouterSuper beschrijft, en moeten kijken naar de output van het TO_CHAR commando. Tip: het 'DAY' format geeft de dagnamen onverkort terug.

[hellknight]

SQL:

select to_char(sysdate,'DAY') from dual

geeft vandaag Wednesday als output, dus als ik simpelweg sat / sun vervang door 'Saturday', 'Sunday' zou het volgens mij moeten werken, zonder de NLS_DATE_LANGUAGE right? (Ik heb nog even gechecked, en NLS_DATE_LANGUAGE is American)

[Verwijderd]

Het meegeven van de NLS_DATE_LANGUAGE zorgt ervoor dat je query onafhankelijk wordt van de taalinstellingen van de database. Dit voorkomt vervelende verrassingen mocht je ooit migreren naar een ander systeem of een andere database. De tijd die je kwijt bent met het intypen van de extra parameter weegt niet op tegen de tijd die je mogelijk later kwijt bent om te achterhalen waarom je query het niet meer doet op een nieuw systeem. Typisch gevalletje van 'kleine moeite, veel plezier'.

Overigens: bij mij (Oracle 10g) geeft 'DAY' als resultaat 'WOENSDAG'; 'Day' geeft 'Woensdag' en 'day' geeft 'woensdag'.

[hellknight]

je hebt gelijk, ik had niet op de caps gelet - DAY geeft inderdaad THURSDAY als output (Oracle 9g)
De query migratie-proof maken is in dit geval nutteloos - de klant waarvoor dit systeem draait heeft bekend gemaakt het contract niet te verlengen, en het systeem zal dan ook begin '11 definitief plat gaan. Een migratie naar andere DB of ander systeem gaat er dus niet van komen