DB2 USER DEFINED SQL

[rajeevm]

HI ALL,

I AM FACING A PROBLEM IN EXECUTING THE FOLLOWING USERDEFINED SQL WHICH I GAVE IN DB2 STAGE USING CASE EXPRESSION THE SQL IS AS BELOW:

CASE
WHEN
DEP_DATETIME BETWEEN CAST('2006-10-12 00:00:00.000000' AS TIMESTAMP) - 28 DAYS
AND CAST('2006-10-12 00:00:00.000000' AS TIMESTAMP) - 21 DAYS
THEN 'N'

WHEN
DEP_DATETIME BETWEEN CAST('2006-10-12 00:00:00.000000' AS TIMESTAMP) - 35 DAYS
AND CAST('2006-10-12 00:00:00.000000' AS TIMESTAMP) - 28 DAYS
THEN 'U'
ELSE DEP_DATETIME
END INDICATOR_FLAG

I AM GETTING THE ERROR AS
"THE SYNTAX OF THE STRING REPRESENTATION OF DATETIME VALUE IS INCORRECT"

AS WHEN IT SATISFIES THE TWO CONDITIONS THEN IT SHOULD RETURN N OR U ELSE IT SHOIULD RETURN THE SAME DATE WHICH IS IN THE "DEP_DATETIME"COLUMN.

tHANKS FOR UR HELP

[ambasta]

I think i cxan help you on this,but can u please explain your requirement in simple words [DEP_DATETIME BETWEEN CAST('2006-10-12 00:00:00.000000' AS TIMESTAMP) - 28 DAYS
AND CAST('2006-10-12 00:00:00.000000' AS TIMESTAMP) - 21 DAYS
THEN 'N'

WHEN
DEP_DATETIME BETWEEN CAST('2006-10-12 00:00:00.000000' AS TIMESTAMP) - 35 DAYS
AND CAST('2006-10-12 00:00:00.000000' AS TIMESTAMP) - 28 DAYS
THEN 'U'
ELSE DEP_DATETIME
END INDICATOR_FLAG
]

is it 21 DAYS subtracted from DEP_DATETIME ??
please explain...

[ray.wurlod]

Etiquette Note
All upper case (except in commands) is interpreted as "shouting" and therefore as rude. Please don't use all upper-case in posts. Do you use all upper case in your documentation?

[ambasta]

Hi Ray,
Actually i was not aware of that fact.from now on i will definitely take care of these Etiquettes.Thanks for your guidance.

[ray.wurlod]

Why not simply select the DEP_DATETIME and perform the mapping within DataStage? You can use a stage variable in a Transformer stage, for example.