Brock
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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.
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
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.
ASKER
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.