Query regarding date field in DB2 unload SQL

k.kutty · #1 (**permalink**) 06-01-09, 13:15

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)))

Peter.Vanroose · #2 (**permalink**) 06-01-09, 14:20

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   ...

k.kutty · #3 (**permalink**) 06-04-09, 04:08

Hi Peter,

Thanks a lot .It works fine for me

k.kutty · #4 (**permalink**) 06-04-09, 04:16

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

umayer · #5 (**permalink**) 06-04-09, 04:38

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( ...

k.kutty · #6 (**permalink**) 06-04-09, 06:48

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

umayer · #7 (**permalink**) 06-04-09, 07:28

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

Cougar8000 · #8 (**permalink**) 06-04-09, 09:36

Correct, SQL can not read your txt. you would have to loaded the values of txt into a var and supply it

Internet Services

Web Development

Digital Marketing

Consumer Tech