Calculated Field

Toad Data Point
1

IF “AHGLDETAL1”.GBOBJ =‘5626’

THEN “AHGLDETAL1”.OBJ_ACCT_MODIFIED = '5160"

ELSE “AHGLDETAL1”.OBJ_ACCT_MODIFIED =“AHGLDETAL1”.GBOBJ

I am getting the following error message…

Error 9/1/2009 1:48:15 PM 0:00:00.640 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token . was not valid. Valid tokens: , FROM INTO. 1 0

It only happens when I create a calculated field that has an if then statement (which I believe I’m entering incorrectly)

Can you help?

Here’s the statement

Btw, I am connected to a JDE table via Access db.

Message was edited by: antonio.herrera_125

2

I don’t have access to an iSeries right now. But looking at the If…then, the quotations see off. Look how that last character of this is a double quote. It should be a single quote. (I’m assuming you did a cut and paste)

THEN “AHGLDETAL1”.OBJ_ACCT_MODIFIED = '5160"

Debbie

3

, “AHGLDETAL1”.“GMR001”

, “AHGLDETAL1”.“GMDL01”

, “AHGLDETAL1”.“MCRP25”

, “AHGLDETAL1”.“MCRP2501”

, “AHGLDETAL1”.“DRDL01”

, “AHGLDETAL1”.“GMMCU01”

, “AHGLDETAL1”.“MCRP06”

, “AHGLDETAL1”.“GBAN01”

, “AHGLDETAL1”.“GBAN02”

, “AHGLDETAL1”.“GBAN03”

, “AHGLDETAL1”.“GBAN04”

, “AHGLDETAL1”.“GBAN05”

, “AHGLDETAL1”.“GBAN06”

, “AHGLDETAL1”.“GBAN07”

, “AHGLDETAL1”.“GBAN08”

, “AHGLDETAL1”.“GBAN09”

, “AHGLDETAL1”.“GBAN10”

, “AHGLDETAL1”.“GBAN11”

, “AHGLDETAL1”.“GBAN12”

, “AHGLDETAL1”.TOTYR, IF

,ELSE “AHGLDETAL1”.OBJ_ACCT_ALT =“AHGLDETAL1”.GBOBJ AS OBJ_ACCT_ALT

FROM REPORTLIB.AHGLDETAL1 “AHGLDETAL1”

WHERE (“AHGLDETAL1”.“GMR001” NOT IN (‘PRO’, ‘GRM’, ‘B/S’, ‘OIE’))

Hi Debbie,

I think it will help if I send you the whole thing… See below.

I have a hunch it is choking on my THEN stament… Since I’m asking it to write to a column that does not exits until the AS statement.

Can you help me reword it so that it works?

Here’s the error I’ getting…

Error 9/2/2009 3:58:21 PM 0:00:00.109 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token . was not valid. Valid tokens: , FROM INTO. 1 0

SQL CODE:

SELECT “AHGLDETAL1”.GBFY , “AHGLDETAL1”.GBLT, “AHGLDETAL1”.GBOBJ , “AHGLDETAL1”.GBSUB, “AHGLDETAL1”.GBMCU, “AHGLDETAL1”.GBAPYC, “AHGLDETAL1”.GBAPYN"AHGLDETAL1".GBOBJ =‘5626’ ,THEN “AHGLDETAL1”.GBOBJ=‘5160’ AS OBJ_ACCT_ALTMessage was edited by: antonio.herrera_125

Message was edited by: antonio.herrera_125

4

I think you need to use the CASE statement.
See this link.
http://search400.techtarget.com/tip/0,289483,sid3_gci820138,00.html
Debbie