| View previous topic :: View next topic |
| Author |
Message |
seekaysk
Beginner
Joined: 31 Aug 2007
Posts: 49
Topics: 15
|
 Posted: Fri Apr 11, 2008 9:12 am Post subject: Use of variable in Date functions |
 |
|
Hi,
I am using a date function and need to calculate 5 or 10 years before the date depending on some value.
Can I use below code ?
| Code: | if value = 0
move 10 to WS-ADJUSTED-DATE
else
move 5 to WS-ADJUSTED DATE
end-if
SELECT DATE(MIN(PM_ME_DATE)) - :WS-ADJUSTED-DATE
FROM TABLE |
or do i need to write the above sql query two times under if-endif (one for 5 years and other for 10 years).
thanks.
_________________
Thanks. |
|
| Back to top |
|
 |
seekaysk
Beginner
Joined: 31 Aug 2007
Posts: 49
Topics: 15
|
| Posted: Fri Apr 11, 2008 9:24 am Post subject: |
|
|
| seekaysk wrote: | Hi,
I am using a date function and need to calculate 5 or 10 years before the date depending on some value.
Can I use below code ?
| Code: | if value = 0
move 10 to WS-ADJUSTED-DATE
else
move 5 to WS-ADJUSTED DATE
end-if
SELECT DATE(MIN(PM_ME_DATE)) - :WS-ADJUSTED-DATE
FROM TABLE |
or do i need to write the above sql query two times under if-endif (one for 5 years and other for 10 years).
thanks. |
| Quote: |
SELECT DATE(MIN(PM_ME_DATE)) - :WS-ADJUSTED-DATE
FROM TABLE
|
I am sorry. The query should be:
| Code: |
SELECT DATE(MIN(PM_ME_DATE)) - :WS-ADJUSTED-DATE YEARS
FROM TABLE
|
Is it a correct query ?
_________________
Thanks. |
|
| Back to top |
|
|
videlord
Beginner
Joined: 09 Dec 2004
Posts: 147
Topics: 19
|
| Posted: Fri Apr 11, 2008 9:36 am Post subject: |
|
|
several options:
1. dynamical SQL - prepare SQL first
2. if - then, different stastic SQLs
3. get the column first, do calculation in your progrom
.... |
|
| Back to top |
|
|
seekaysk
Beginner
Joined: 31 Aug 2007
Posts: 49
Topics: 15
|
| Posted: Fri Apr 11, 2008 11:18 am Post subject: |
|
|
hmm...well looks I will have to do a longer way. if-then, different static sqls.
thanks videlord.
_________________
Thanks. |
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Fri Apr 11, 2008 12:11 pm Post subject: |
|
|
| videlord wrote: | several options:
1. dynamical SQL - prepare SQL first
2. if - then, different stastic SQLs
3. get the column first, do calculation in your progrom
.... |
NO. Please stick with things you know
SELECT DATE(MIN(PM_ME_DATE)) - :WS-ADJUSTED-DATE
FROM TABLE
Just add the into clause and if ws-adjusted-date is defined correctly it will work. |
|
| Back to top |
|
|
haatvedt
Beginner
Joined: 14 Nov 2003
Posts: 66
Topics: 0
Location: St Cloud, Minnesota USA
|
| Posted: Sun Apr 13, 2008 11:45 pm Post subject: |
|
|
seekaysk,
another option may be to just select the minimum date. Move it to a redefined work area so that you can reference the CCYY field as a PIC 9(4) value and then subtract the appropriate number of years from the redefined CCYY field.
SELECT MIN(PM_ME_DATE)
FROM TABLE
good luck.
_________________
Chuck Haatvedt
email --> clastnameatcharterdotnet
(replace lastname, at, dot with appropriate
characters) |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
