| IF to DECODE [message #371443] |
Thu, 19 October 2000 11:11  |
Kathy
Messages: 10
Registered: October 2000
|
Junior Member |
|
|
Hi all,
Can I translate the following IF into a DECODE? Unsure how to get the <= into the decode statement.
sum(If year_no = this year
and
month_no <= this month
then amount
else 0)
Thanks!
|
|
|
|
| Re: IF to DECODE [message #371447 is a reply to message #371443] |
Thu, 19 October 2000 13:37  |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
Kathy,
You dont have to break up the date into year and month to do that. I'll assume that the date field was ORDER_DATE for my example in which case the followng sql will do
SELECT Sum(DECODE(GREATEST(TRUNC(ORDER_DATE, 'MM'),TRUNC(SYSDATE, 'MM')), TRUNC(SYSDATE, 'MM'), Amount, 0)) FROM ORDER_TABLE;
The trunc() is used to set both the dates on 01 of whatever month and year they are because we need to compare them for the same month and year only. The greatest will return the greatest of the two. If this is equal to the trunc(sysdate, 'mm'), the first part of decode will hold true.
If the month and year are in two different fields instead of a date field, then you'll have to construct the date somewhat like this
SELECT Sum(DECODE(GREATEST(TO_DATE('01-'||MONTH_FIELD||'-'||YEAR_FIELD, 'DD-MM-YYYY'),TRUNC(SYSDATE, 'MM')), TRUNC(SYSDATE, 'MM'), Amount, 0)) FROM ORDER_TABLE;
hth
Prem :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
