IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need Help with on of the coalesce query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Rajan2511

New User


Joined: 25 Nov 2018
Posts: 3
Location: India

PostPosted: Mon Dec 21, 2020 4:48 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2789
Location: NYC,USA

PostPosted: Mon Dec 21, 2020 10:03 pm
Reply with quote

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
View user's profile Send private message
Rajan2511

New User


Joined: 25 Nov 2018
Posts: 3
Location: India

PostPosted: Tue Dec 22, 2020 1:42 pm
Reply with quote

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
View user's profile Send private message
Rajan2511

New User


Joined: 25 Nov 2018
Posts: 3
Location: India

PostPosted: Tue Dec 22, 2020 10:54 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2789
Location: NYC,USA

PostPosted: Wed Dec 23, 2020 12:21 am
Reply with quote

Welcome Rajan!!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts DB2: Need helping below DB2 query DB2 13
No new posts DB2 SQL query to read and update data... DB2 12
No new posts Cobol-db2 : SQL query is running for ... DB2 4
No new posts Reading subsequent rows in a query. DB2 12
No new posts SYSIBM Tables Query DB2 8
Search our Forums:

Back to Top