Using a Formula instead of value to populate a field in DB2

ggarze · Post by **ggarze** » Fri Dec 04, 2009 10:34 am

When updating a DB2 table using the DB2/UDB stage I'm trying to get the database to calculate the field value based on the columns I pass it but it's treating what I pass it as a string not a calculation for DB2 to execute. For example: If I run this statement in Command Center it works and the numeric value of the calculation is displayed on the table properly.
UPDATE TABLE
SET EP_NUMERATOR=SCDLR_EFF_HRS + SCDLR_UNPAID_BREAK_HRS
WHERE FIELDA = '01'

Note:Both SCDLR_EFF_HRS and SCDLR_UNPAID_BREAK_HRS are fields on TABLE.

But when I run this in Datastage
UPDATE TABLE
SET EP_NUMERATOR=?
WHERE FIELDA = '01'

Note: '?' is resolved from the incoming file which has the calculation SCDLR_EFF_HRS + SCDLR_UNPAID_BREAK_HRS (no quotes either in the file)

The error I get is that 'EP_NUMERATOR has been truncated to SCDLR_EFF_HR'

So it looks like DataStage is trying just to pass this equation in as a String into the field which is defined as a decimal.

Is what I'm trying possible in DataStage and I'mjustmissing something or does DataStage need an actual value to update?

Thanks

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Dec 04, 2009 10:43 am

Do you mean you want the formula to be dynamically substituted ?

If it is always addition, why not do in datastage and pass the result ?

You need to provide more information.

ggarze · Post by **ggarze** » Fri Dec 04, 2009 10:51 am

[quote="Sainath.Srinivasan"]Do you mean you want the formula to be dynamically substituted ?

If it is always addition, why not do in datastage and pass the result ?

You need to provide more information. ...[/quote]

Yes, I want the formula substituted. The formula is not always the same and can change per row and per run. Ultimately each row will have 5 seperate buckets all containing different formulas. Right now I'm just trying to get one to work.

ggarze · Post by **ggarze** » Fri Dec 04, 2009 11:22 am

So the next thing I tried is making the update User Defined SQL and replaced the '?' with an actual formula as if I was writing it in command center, not taking it from the input file. It worked that way. So, for some reason the stage deosn't like when the formula is coming in from a source.

chulett · Post by **chulett** » Fri Dec 04, 2009 11:36 am

Sure, because when it is 'coming in from the source' it is just a string value you want to load into that field.

ggarze · Post by **ggarze** » Fri Dec 04, 2009 11:51 am

Yeah t he field is a string, but in the file there are no quotation marks around it so when it replaces the '?' In the update statement it shoul looks just as it did when I hard coded the formula in it and it worked. Right?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Dec 04, 2009 1:47 pm

Maybe you can construct a case statement and pass a pointer value to indication the expression to be evaluated.