|
View previous topic :: View next topic
|
| Author |
Message |
tecnokrat
Warnings : 1
Active User
Joined: 22 May 2009
Posts: 160
Location: Bangalore
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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  |
|
| Back to top |
|
|
tecnokrat
Warnings : 1
Active User
Joined: 22 May 2009
Posts: 160
Location: Bangalore
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Because eff_dt could be later than today. . .  |
|
| Back to top |
|
|
tecnokrat
Warnings : 1
Active User
Joined: 22 May 2009
Posts: 160
Location: Bangalore
|
|
|
|
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 |
|
|
sambit_mech
New User
Joined: 14 Jul 2010
Posts: 13
Location: India
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
| Back to top |
|
|
|
|
