By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,745 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

SQL0206N, case statement and time calculation

DTV12345
P: 5
Hello!

I am attempting to create a query that calculates a time value from GMT to
Pacific time. It is, of course, based on the calendar date that I will
have to figure out whether or not daylight savings applies (i.e. subtract
7 or 8 hours to get Pacific time).

In this query below, "LVDURATION.START_TIME" is expressed in GMT time: I have to present it in the query output as Pacific time.

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2. LVDURATION.PGM_DESIGNATOR,
  3. PROGRAM_GUIDEREP.TITLE,
  4. SUM(LVDURATION.DURATION)/60 AS "TOTAL_DURATION_IN_MINUTES",
  5. COUNT (DISTINCT CAST(LVDURATION.ACCOUNTID AS CHAR(15))) AS
  6. "TOTAL_ACCOUNTS",
  7. LVDURATION.CONTCHANID,
  8. LVDURATION.DAY_EASTERN,
  9. CASE
  10. WHEN day(CURRENT_DAY) = 1 and month(CURRENT_DAY) = 4
  11. THEN        LVDURATION.START_TIME - 7 HOURS
  12. WHEN day(CURRENT_DAY) = 1 and month(CURRENT_DAY) = 11
  13. THEN        LVDURATION.START_TIME - 8 HOURS
  14. END "START_TIME"
  15. FROM
  16. AMS.LVDURATION LVDURATION,
  17. AMS.PROGRAM_GUIDEREP PROGRAM_GUIDEREP
  18. WHERE
  19. PROGRAM_GUIDEREP.PGM_DESIGNATOR = LVDURATION.PGM_DESIGNATOR
  20. AND LVDURATION.DAY_EASTERN=CAST(LVDURATION.START_TIME AS DATE)
  21. GROUP BY
  22. LVDURATION.PGM_DESIGNATOR,
  23. PROGRAM_GUIDEREP.TITLE,
  24. LVDURATION.CONTCHANID,
  25. LVDURATION.DAY_EASTERN,
  26. LVDURATION.START_TIME
When I ran this, I got this error message: ``SQL0206N "CURRENT_DAY" is
not valid in the context where it is used. ``

I cannot see what I am doing wrong. Can someone please provide a few
pointers? My configuration is:

IBM DB v9.1
Linux RH

Thanks.

AB.
Jul 13 '07 #1
Share this Question
Share on Google+
1 Reply


DTV12345
P: 5
Never mind...all I have to do is use CURRENT_DATE instead of CURRENT_DAY.

It works fine now.
Jul 13 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.