Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

Oracle CASE Statement

I know that the MDY() function within the SQL will fail … this tells Informix to return the Date value for the values sent.  Also CURRENT will need to be switched out to SYSDATE, but what will the SQL need to look like to correct the CASE statement below?

Name:
Nbr Closed this Qtr
Definition:
CASE WHEN Month(CURRENT) >= 1 AND Month(CURRENT) <= 3
THEN
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= MDY(01,01,Year( CURRENT )-1) AND
Sfhd_Loan_Activity.Date_Of_Closing <= MDY(03,31,Year( CURRENT )-1)
     THEN 1 ELSE 0 END )
ELSE
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= MDY(01,01,Year( CURRENT )) AND
      Sfhd_Loan_Activity.Date_Of_Closing <= MDY(03,31,Year( CURRENT ))
           THEN 1 ELSE 0 END )
END


TIA,
N
0
Nakuru1234
Asked:
Nakuru1234
  • 6
  • 3
  • 2
  • +1
2 Solutions
 
slightwv (䄆 Netminder) Commented:
This is posted in an Oracle zone.  Is this Oracle or Informix?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
something like this, off the top of my head ...
CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'MM')) >= 1 AND TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) <= 3
THEN SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= TRUNC (TRUNC(SYSDATE, 'YYYY')-1 , 'YYYY') 
                                 AND Sfhd_Loan_Activity.Date_Of_Closing < ADD_MONTHS(3, TRUNC (TRUNC(SYSDATE, 'YYYY')-1 , 'YYYY'))
                       THEN 1 ELSE 0 END )
ELSE
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= TRUNC(SYSDATE, 'YYYY')  
                         AND Sfhd_Loan_Activity.Date_Of_Closing <= ADD_MONTHS(3,  TRUNC(SYSDATE, 'YYYY'))
           THEN 1 ELSE 0 END )
END

Open in new window

0
 
sdstuberCommented:
try this...

CASE
           WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'q')) = 1
           THEN
               SUM(
                   CASE
                       WHEN sfhd_loan_activity.date_of_closing >=
                                ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), -12)
                            AND sfhd_loan_activity.date_of_closing <=
                                    ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), -9) - 1
                       THEN
                           1
                       ELSE
                           0
                   END)
           ELSE
               SUM(
                   CASE
                       WHEN sfhd_loan_activity.date_of_closing >= TRUNC(SYSDATE, 'yyyy')
                            AND sfhd_loan_activity.date_of_closing <=
                                    ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), 3) - 1
                       THEN
                           1
                       ELSE
                           0
                   END)
       END

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sdstuberCommented:
angeliii - your add_months parameters are backwards

also,  the end date is 3/31  so after adjusting by 3 months you to 4/1 you need to subtract 1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you are surely correct about the add_months parameters ...
but :
<= MDY(03,31,Year( CURRENT )-1)
means to me the same as
< 1/4/ ....
or am I wrong there?
0
 
sdstuberCommented:
MDY(03,31,Year( CURRENT )-1)


is equivalent to

to_date('03/31/' || to_char(sysdate,'yyyy')-1,'mm/dd/yyyy')

so

3/31/2010
0
 
sdstuberCommented:
ah,  I see what you're saying now

 I didn't notice you switched the <= to <

0
 
Nakuru1234Author Commented:
So can you send me the correct SQL revised...am a little confused with all the comments.

TIA,
N
0
 
sdstuberCommented:
http:#35087493

should be correct

try it with your table and see, we can't test it, we don't have your data
0
 
sdstuberCommented:
if it doesn't work,  please provide sample data and expected output
0
 
Nakuru1234Author Commented:
When there is more data on the Oracle Instance I will try it and compare to Informix.  As of now it works for 1 qtr. Thanks for your help.

TIA,
N
0
 
Nakuru1234Author Commented:
Excellent answer and prompt help. Thanks.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now