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
- SELECT
- LVDURATION.PGM_DESIGNATOR,
- PROGRAM_GUIDEREP.TITLE,
- SUM(LVDURATION.DURATION)/60 AS "TOTAL_DURATION_IN_MINUTES",
- COUNT (DISTINCT CAST(LVDURATION.ACCOUNTID AS CHAR(15))) AS
- "TOTAL_ACCOUNTS",
- LVDURATION.CONTCHANID,
- LVDURATION.DAY_EASTERN,
- CASE
- WHEN day(CURRENT_DAY) = 1 and month(CURRENT_DAY) = 4
- THEN LVDURATION.START_TIME - 7 HOURS
- WHEN day(CURRENT_DAY) = 1 and month(CURRENT_DAY) = 11
- THEN LVDURATION.START_TIME - 8 HOURS
- END "START_TIME"
- FROM
- AMS.LVDURATION LVDURATION,
- AMS.PROGRAM_GUIDEREP PROGRAM_GUIDEREP
- WHERE
- PROGRAM_GUIDEREP.PGM_DESIGNATOR = LVDURATION.PGM_DESIGNATOR
- AND LVDURATION.DAY_EASTERN=CAST(LVDURATION.START_TIME AS DATE)
- GROUP BY
- LVDURATION.PGM_DESIGNATOR,
- PROGRAM_GUIDEREP.TITLE,
- LVDURATION.CONTCHANID,
- LVDURATION.DAY_EASTERN,
- LVDURATION.START_TIME
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.