Left-of-decimal digits truncated Error 1

[jj22171]

My PS Query is giving out this error message:
SQL error. Stmt #: 0 Error Position: 0 Return: 8150 - Left-of-decimal digits truncated from FETCHed value (SQLSTATE PS150.

Has any encountered it and how is it resolved? The query runs perfectly using SQL Query Analyzer with some rows showing .00 in the summed column.

Thanks for the help.

 

[Rofeu]

Never seen it before. If you care to post the actual SQL, we might see if we can find out what is causing it.

Cheers,

Roel

 

[jj22171]

Here's the query:

SELECT A.FIELD_NUMBER, SUM(CASE WHEN B.ERNCD = A.ERNCD THEN B.OTH_EARNS ELSE 0 END) AS SUM_OTH_EARNS
FROM PS_AAA_PAY A, PS_BBB_PAY B
WHERE A.ERNCD = B.ERNCD
AND B.COMPANY = :1
GROUP BY A.FIELD_NUMBER

Thanks.

 

[notadba]

What is the field format of your case expression column? Sounds like you have not allowed enough digits in the length to cater for the summed value.

 

[Rofeu]

I'll have to second notadba's assumption.

2 solutions come to mind:

1. Change the number of decimals the expression can take
2. ROUND(SUM(CASE WHEN B.ERNCD = A.ERNCD THEN B.OTH_EARNS ELSE 0 END),X) where X is the number of decimals

Cheers,

Roel

 

[jj22171]

My computer was replaced and still waiting for the PStools to be re-installed. Will try rofeu's suggestion and let you know. Thanks, guys.

 

[jj22171]

Thank you guys. As a newbie to PSQuery, I didn't know you can define the expression's format. It was in char format. I changed it to numbers with 2 decimal, and it works.

Appreciate your help.