Link to home
Start Free TrialLog in
Avatar of Brock
BrockFlag for Canada

asked on

Replace the integer portion in CAST with a column

I would like to do this :

SELECT CAST(X.NC_PREMIUM AS DECIMAL (8,def.ROUND_TO))
FROM PS_NC_BEN_CLIENT X
INNER JOIN PS_LIFE_ADD_TBL LTB ON LTB.BENEFIT_PLAN = X.DESCR
INNER JOIN PS_BN_FORM_DEF def ON LTB.BN_FORMULA_ID = def.BN_FORMULA_ID

Error results because the def.Round_to is expecting an integer.  But this can vary.  

Any suggestions would be appreciated.

Lucia
SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brock

ASKER

Thank you.

I thought I  couldn't do that :-).  But I always waste my work cycles trying :-).

I did try Jeff's suggestion.  This works fine.  I am working in peoplesoft and so I will put this in a batch program step that will run this.

Thank you to all for your prompt response.

Lucia.
I may have made an improper assumption that your query returns only one row. If not, dynamic SQL won't work for you, either. Have you considered the ROUND function? That is:

SELECT ROUND(X.NC_PREMIUM AS def.ROUND_TO)
FROM PS_NC_BEN_CLIENT X
INNER JOIN PS_LIFE_ADD_TBL LTB ON LTB.BENEFIT_PLAN = X.DESCR
INNER JOIN PS_BN_FORM_DEF def ON LTB.BN_FORMULA_ID = def.BN_FORMULA_ID

Open in new window


The rub is that your underlying data type will still be whatever X.NC_PREMIUM is, though each result will be rounded to the appropriate number decimal places. I'm not sure there's any other good way to achieve what you're trying to. Ultimately, a column in a result set can have only one data type, and a decimal with different scale is considered a different data type.