|
View previous topic :: View next topic
|
| Author |
Message |
Rajan2511
New User
Joined: 25 Nov 2018
Posts: 3
Location: India
|
|
|
|
Hi All,
Could you please help me with this query below.
Select Business_id,
Coalesce(cast ((round(quantity*price/100,2)) As
Decimal(15,2)),SI_Asset_value) as Asset value
From..
I have issue here when i am executing the query its throwing me error as
DSNT418I SQLSTATE = 01507 SQLSTATE RETURN CODE.
I did my analysis and found that i have data where Quantity has value however Price value is Null as well as SI_Asset_value is also null, so its returning null which should not happen i believe as we are using Coalesce.
I tried changing the query as
Select Business_id,
Coalesce(cast ((round(quantity*price/100,2)) As
Decimal(15,2)),SI_Asset_value, quantity) as Asset value
From..
However my job is still failing and setting RC=08
Job details are as..
Job is using DB2TIAUL utility to unload data by clubbing them together.
Could someone please check and give any information.
Let me know if more details are required. |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Code: |
Select Business_id,
CASE WHEN value(quantity,0) = 0 then VALUE(SI_Asset_value,0)
WHEN value(price,0) = 0 then VALUE(SI_Asset_value,0)
WHEN cast((round(quantity*price/100,2)) As
Decimal(15,2)) = 0.00 THEN VALUE(SI_Asset_value,0)
ELSE cast((round(quantity*price/100,2)) As Decimal(15,2))
END as asset_value
From.. |
|
|
| Back to top |
|
|
Rajan2511
New User
Joined: 25 Nov 2018
Posts: 3
Location: India
|
|
|
|
Thank you Rohit, query seems to be working fine and giving expected result through Spufi, however when i am running the same query through job it is ended with RC=08, all the error messages i can is..
| Code: |
SQLCODE=00 SUCCESSFUL EXECUTION
SQLSTATE = 01507 SQLSTATE RETURN CODE
SQLERRP = SQL PROCEDURE DETECTING ERROR
SQLERRD = -115 0 61 - 1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X 'FFFFFF8D' X'00000000'....... |
i am not sure if this is query that is creating problem or the utility...any information on this would be helpful.
Let me know if you want me to paste the full sql query here.
Cod'ed |
|
| Back to top |
|
|
Rajan2511
New User
Joined: 25 Nov 2018
Posts: 3
Location: India
|
|
|
|
| Hi Rohit, i was able to crack the error that was coming. Thank you for your suggestions as well which was really helpful. Learnt something new. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Welcome Rajan!! |
|
| Back to top |
|
|
|
|
