Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    9

    Unanswered: Query regarding date field in DB2 unload SQL

    Hi,


    I have a DB2 SQL like this to unload one target table .Here under where clause I have one date field which i have to pass as a parameter.Currently I am passing ('2009-01-01') in YYYY-MM-DD format.With that format this SQL is working fine,I am able to unload the table successfully.But As per my requirement, I have to pass in YYYYMM format .But,When I tried to pass YYYYMM format I am getting the below mentioned error code .Could you suggest any solution for this.


    MESSAGE: [IBM][CLI Driver][DB2/AIX64] SQL0180N The syntax of the string representation of

    a datetime value is incorrect. SQLSTATE=22007

    Please find the SQL below ,In this i have passed the datein YYYYMM

    char(month(DB2_DT)) BETWEEN char(year(date('200901') - 2 months)) ||

    char(month(date('200901') - 2 months)) AND char(year(date('200901') - 2 months)) ||

    char(month(date('200901') - 2 months)))
    Last edited by k.kutty; 06-07-09 at 11:31.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Replace any occurrence of "date('200901')" by
    Code:
    timestamp_format('200901', 'YYYYMM')
    Actually, this function returns a TIMESTAMP datatype, but since you pass its result to either the YEAR() or the MONTH() function that should work as well.
    If not, pass it as argument to the DATE() function first.

    Actually, a more direct way to obtain month and year from the subtraction of two months from a 'YYYYMM' expression is with two CASE expressions:
    Code:
    SELECT CASE substr('200901',5)
           WHEN '01' THEN '11' WHEN '02' THEN '12' WHEN '03' THEN '01' ... END
           AS month, ....
    FROM   ...
    and
    Code:
    SELECT CASE WHEN substr('200901',5) >= '03'
           THEN SUBSTR('200901',1,4)
           ELSE char(int(SUBSTR('200901',1,4))-1) END AS year, ....
    FROM   ...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jun 2009
    Posts
    9
    Hi Peter,

    Thanks a lot .It works fine for me

  4. #4
    Join Date
    Jun 2009
    Posts
    9
    Hi,

    One more help I need,
    From the above SQL I have to read all the carrier codes from a text file.I have this file under bin directory and I have to pass the file name as a parameter while executing my application.But ,during the run time it shows the below mentioned error message. Could you please suggest a solution for this.

    MESSAGE: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "GROUP" was found following "ting_carr_code.txt')". Expected tokens may include: ")". SQLSTATE=42601
    Last edited by k.kutty; 06-07-09 at 11:33.

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    there are 31 left parenthesis in your query, but only 30 right paranthesis.


    might be that paranthesis is the odd one:

    ... AND ((NOT LOCATE(rtrim( ...
    Last edited by umayer; 06-04-09 at 05:52.

  6. #6
    Join Date
    Jun 2009
    Posts
    9
    Hi,

    Thanks ,I resolved it.But now my application is unloading zero records and running successfully with other steps.Please help me to resolve this.
    Actually my text file contains data as like
    'LA',
    'LP',
    'XL',
    '3M'
    '--',etc
    Last edited by k.kutty; 06-07-09 at 11:34.

  7. #7
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by k.kutty

    ... AL1.MKT_CARR_CD IN ('devl/apps/edw/dwp_ap00/dwpav/bin/marketing_carr_code.txt' ...
    I'm not familiar with DB2 for AIX but I think DB2 is checking the column MKT_CARR_CD against the text string 'devl/apps/edw...' and not against the content of that file
    Last edited by umayer; 06-04-09 at 08:32.

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Correct, SQL can not read your txt. you would have to loaded the values of txt into a var and supply it
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •