-1

I have a simple query to pull DAYOFWEEK from SYSIBM/SYSDUMMY1 to determine whether or not to clear a table that tracks yesterday sales.

    DELETE FROM DAILYSALES WHERE (CASE WHEN (SELECT DAYOFWEEK(CURRENT DATE) 
  FROM SYSIBM/SYSDUMMY1)   
  BETWEEN 3 AND 6 THEN 'YES' ELSE 'NO' END) = 'YES';

Since the report that runs on Monday (dayofweek=2) needs Friday (dayofweek=6), Saturday (dayofweek=7), Sunday (dayofweek=1) sales (today-1 to calculate date), it would seem logical that I should see sales figures for days 6, 7, and 1.

However, it seems to be retaining Thursday's (dayofweek=5) sales and not pulling Sunday sales (dayofweek=1) when it runs on Monday morning. This leads me to believe the date being used by SYSDUMMY1 doesn't flip until long after midnight, even though our system date flips at midnight EST.

It runs fine the rest of the week, providing the correct sales data for 'yesterday'. When I manually run Select DAYOFWEEK(current date) from SYSIBM.SYSDUMMY1, it always shows the correct dayofweek. What am I missing?

Nancy Wilson
  • 9
  • 1
  • 3
  • FYI you can use the DAYOFWEEK function directly in the SQL statement. Do not need the "select from sysibm/sysdummy1" stuff. – RockBoro Feb 25 '21 at 17:12

1 Answers1

0

I don't see any correlation (usage) of a column in DAILYSALES..

Thus

(CASE WHEN (SELECT DAYOFWEEK(CURRENT DATE) 
  FROM SYSIBM/SYSDUMMY1)   
  BETWEEN 3 AND 6 THEN 'YES' ELSE 'NO' END)

Is only evaluated one time and if the process runs on Monday, would return 2==>NO so no rows would be deleted.

I suspect instead of DAYOFWEEK(CURRENT DATE) you want DAYOFWEEK(SALESDATE) where SALESDATE is a column in the DAILYSALES table.

Lastly note that SYSIBM.SYSDUMMY1 has only a single char(1) column IBMREQ and a single row with the value of that column set to 'Y'. Nothing is ever updated in this table.

Charles
  • 21,637
  • 1
  • 20
  • 44
  • I am deleting the entire file Tues-Friday, so the extract of "yesterday" can populate the file. The logic works every day during the week, Except Saturday. It also works on Sunday, because it is not deleting the file when it runs Sunday. The CASE is a sub select to determine the day of week and set to yes or no. If you look at the entire statement, it only deletes the ones set to YES. DELETE FROM DAILYSALES WHERE (CASE WHEN (SELECT DAYOFWEEK(CURRENT DATE) FROM SYSIBM/SYSDUMMY1) BETWEEN 3 AND 6 THEN 'YES' ELSE 'NO' END) = 'YES' – Nancy Wilson Feb 22 '21 at 20:30
  • Hi Chris, if you run this statement, it will pull DAYOFWEEK - select dayofweek(CURRENT DATE) FROM SYSIBM/SYSDUMMY1. You can pull all sorts of things from this table. Not sure how it works, but system info you want can be pulled from there. If I run SELECT TIMESTAMP(CURRENT DATE) (or TIME) from SYSIBM/SYSDUMMY1, it pulls timestamp or time, but the time is always populated to 0. (here is the link: https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_bif_dayofweek.html) – Nancy Wilson Feb 22 '21 at 20:44