Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
issues with COALESCE function

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Wed Jul 28, 2010 9:12 am    Post subject: issues with COALESCE function
Reply with quote

Hi Im facing an issue with the below COALESCE function
Code:

SELECT   COALESCE(MAX(EFF_DT),CURRENT DATE) 
INTO    :EFF-DT  INDICATOR :NULL-INDICATOR
FROM  EFF_TAB                               
WHERE  P1     = 102456               
  AND   P2      = '01'           
     


running the above query in the SPUFI is giving me the correct results i.e 07-27-2010 but the same when executed in a program is giving me the date as 07-30-2010.

Please help me out in whats wrong being done by me.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jul 28, 2010 9:38 am    Post subject:
Reply with quote

Hello,

I suspect the queries are not exactly the same as spufi does not use host variables?

What value is eff_dt when sputi is used? I suspect the current date is greater than "nothing"?

I'm not where i can run an experiment, so this is mostly thinking on the keyboard icon_smile.gif
Back to top
View user's profile Send private message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Wed Jul 28, 2010 9:43 am    Post subject:
Reply with quote

Hi dick,
You are correct as in SPUFI we do not use the HOST variables.

When i use this query in the SPUFI i get todays date that is system date(now 07-28-2010) and the same is giving a date greater than the CURRENT DATE.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jul 28, 2010 9:45 am    Post subject:
Reply with quote

Hello,

Because eff_dt could be later than today. . . icon_confused.gif
Back to top
View user's profile Send private message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Wed Jul 28, 2010 9:54 am    Post subject:
Reply with quote

Thanks Dick,

i Have found the solution for this.
Quote:

If P3(Primary key) is greater than 99 then the event will be posted onto the next day

As for every insert in the table the P3 will be incremented.

Sorry for the confusion.
Back to top
View user's profile Send private message
sambit_mech

New User


Joined: 14 Jul 2010
Posts: 13
Location: India

PostPosted: Wed Jul 28, 2010 11:08 am    Post subject:
Reply with quote

I don't think that COALESCE and Indicator Variable can be used simultaneously, any one of them should be used. Also, it is true that in SPUFI, we cannot use indicator variable.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jul 28, 2010 7:14 pm    Post subject:
Reply with quote

Hello,

Why is P3 now mentioned? Where did the quote originate?

The events that cause P3 to change appear to have nothing to do with the coalesce question icon_confused.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us