473,396 Members | 2,093 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

SQL0206N, case statement and time calculation

DTV12345
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
1 2348
Never mind...all I have to do is use CURRENT_DATE instead of CURRENT_DAY.

It works fine now.
Jul 13 '07 #2

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

Similar topics

3
by: Prince Kumar | last post by:
Hi All, I am trying to a get a trigger retrieved from Oracle to work on DB2 UDB 8.1. I am getting the following error when trying to create the trigger. How would I resolve this? create table...
1
by: Zri Man | last post by:
Procedure Get_RowCount works fine when called independently. When called from Another procedure it gives the following error. SQL0206N "C" is not valid in the context where it is used....
13
by: Fei Liu | last post by:
Hi Group, I've got a problem I couldn't find a good solution. I am working with scientific data files in netCDF format. One of the properties of netCDF data is that the actual type of data is only...
4
by: jefftyzzer | last post by:
Friends: I have a query similar in structure to the following: SELECT T1.IBMREQD FROM SYSIBM.SYSDUMMY1 T1 LEFT JOIN TABLE
0
by: KiranKGone | last post by:
Hello All, I need to define a trigger for updating the multiple columns of a target table when an insert happens on a subject table. I have written the following trigger, however, getting the...
1
by: 7h0r | last post by:
When trying to insert this trigger: CREATE TRIGGER ad_des_part_jon AFTER DELETE ON DESIGJON.PART_LIST REFERENCING OLD AS o FOR EACH ROW mode db2sql UPDATE DEFINJON.PART_LIST A SET...
2
by: csolomon | last post by:
Hello: I am creating a form that will calculate a value based on the value selected from a case statement in a function. The function I created is called GetYield and accepts 3 arguments. I...
1
Uncle Dickie
by: Uncle Dickie | last post by:
Hi All, I have the following bit of code which looks at a parts list and then checks on incoming and outgoing movements in the next 60 days SELECT pa.PartNumber ...
40
by: chhines | last post by:
I have a very long IIf statement. I think maybe I've reached the limit of how many choices you can have in the control source "Build" statement of a text box on a form. Really, the IIF statement is...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.