Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Informix SQL to Oracle CASE Statement

Posted on 2011-03-16
21
Medium Priority
?
517 Views
Last Modified: 2012-08-14
Please help to re-write the SQL on the left side highlighted in black to look like the one on the right highlighted in blue...

TIA,
N
Informix-SQL-to-Oracle-CASE-Stat.docx
0
Comment
Question by:Nakuru1234
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 3
  • 3
  • +1
21 Comments
 
LVL 60

Accepted Solution

by:
HainKurt earned 1200 total points
ID: 35151254
maybe 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
                         Sfhd_Loan_Activity.Loan_Amt
                      ELSE
                         0
                   END)
            ELSE
               SUM(CASE
                      WHEN Sfhd_Loan_Activity.Date_Of_Closing >=
                              TO_DATE ('0101' || Year (SYSDATE), 'mmddyyyy')
                           AND Sfhd_Loan_Activity.Date_Of_Closing <=
                                 TO_DATE ('0331' || Year (SYSDATE),
                                          'mmddyyyy')
                      THEN
                         Sfhd_Loan_Activity.Loan_Amt
                      ELSE
                         0
                   END)
         END

Open in new window

0
 
LVL 60

Assisted Solution

by:HainKurt
HainKurt earned 1200 total points
ID: 35151268
more fix...
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
                         Sfhd_Loan_Activity.Loan_Amt
                      ELSE
                         0
                   END)
            ELSE
               SUM(CASE
                      WHEN Sfhd_Loan_Activity.Date_Of_Closing >=
                              TO_DATE ('0101' || TO_CHAR (SYSDATE, 'yyyy'),
                                       'mmddyyyy')
                           AND Sfhd_Loan_Activity.Date_Of_Closing <=
                                 TO_DATE (
                                    '0331' || TO_CHAR (SYSDATE, 'yyyy'),
                                    'mmddyyyy'
                                 )
                      THEN
                         Sfhd_Loan_Activity.Loan_Amt
                      ELSE
                         0
                   END)
         END

Open in new window

0
 

Author Comment

by:Nakuru1234
ID: 35157103
I went ahead and deleted the other question...so I opened a new one...therefore please don't delete this one. There should be more experts like HainKurt...who ready and willing to help rather than ask unnecessary questions. HainKurt...I really appreciate your help so far...thx.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:Nakuru1234
ID: 35157197
HainKurt:

How about the rest? TIA...

N
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 35166522
here are some tips so you can work on the rest:

Informix --> Oracle

Month(Current) --> to_number(to_char(sysdate,'mm'))
Year (CURRENT) --> to_number(to_char(sysdate,'yyyy'))
MDY (m,d,y) --> to_date(m||d||y,'mmddyyyy') -- assuming m,d, y are string, m & d is in this format xx, and y is in XXXX format
MDY (m,d,y) --> to_date(m||'/'||d||'/'||y,'mm/dd/yyyy') -- if m,d,y is not 0 padded

other than these, I dont see anything else...
0
 

Author Comment

by:Nakuru1234
ID: 35180738
Yah, thats pretty much it. I'll tryt make some sense out of this and finish the rest. Thx.

N
0
 

Author Comment

by:Nakuru1234
ID: 35182687
I did the necessary changes but I am getting errors...any advice?

TIA,
N
Informix-SQL-to-Oracle-CASE-Stat.docx
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35183048
I cannot open the attachment.  What is the error message you are receiving?
0
 

Author Comment

by:Nakuru1234
ID: 35189200
I did not mean to say errors...sorry? I wanted to request that you check to make sure I did the correct changes.

TIA,
N
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35189358
>>make sure I did the correct changes.

Do you get the correct results?  We don't know your code, data, ???  You should have test cases that you can run against your code to see if it is producing the correct results.

You need to learn what the Oracle functions are doing.  Once you do that, it should be a pretty easy conversion.

That said, I don't think you converted it properly.  Again, I cannot say for sure since I cannot test against your data/system but on a quick scan...

The old code:
CASE WHEN Month(CURRENT) >= 1 AND Month(CURRENT) <= 6

Does not seem to match what you use in:
CASE WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'q')) = 1 THEN


0
 

Author Comment

by:Nakuru1234
ID: 35197826
If thats the case can you advice what to do on the rest of the code. I got help to do the first two queries and they work fine. So, I need help to write the correct syntax similar to the first two queries.

TIA,
N
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35197900
Experts-Exchange is a technology question and answer site.  It really isn't set up for consulting services.

What we have been trying to tell you is, we cannot rewrite your code for you.  If you require someone to come in and port all your code, I suggest you look into hiring a consultant.

You should understand the logic in the Informix code.  HainKurt provided the mapping between the Informix calls and the Oracle counterparts.

You need to convert the logic.  The flaw I posted above was just a pretty obvious one.  There can easily be smaller ones that we cannot see.

Only you know if the code works or not.

Aside from 'rewrite all my code for me', is there any specific Oracle function you are having trouble understanding?
0
 

Author Comment

by:Nakuru1234
ID: 35198015
I understand. I've rewritten most of the code and I was only seeking your advice on writing something close to the first two queries in CASE STATEMENT logic on the Oracle side. However, I don't think you're willing to help which is the same case I experienced with you on another question. Maybe you should defer this question to another expert or Guru in Oracle...
N.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35198068
please post your code and errors.

don't put them in a docx.

Just use text, the code and errors will be in text so it's not only the most portable format, it should be the easiest too.
0
 

Author Comment

by:Nakuru1234
ID: 35198467
sdstuber - I'll definitely post my code...see below. I thought putting in a word document will make it easy. I do like your positive attitude...

Qtr 3 – Nbr Closed this Qtr
CASE WHEN Month(CURRENT) >= 1 AND Month(CURRENT) <= 9
THEN
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= MDY(07,01,Year( CURRENT )-1) AND
Sfhd_Loan_Activity.Date_Of_Closing <= MDY(09,30,Year( CURRENT )-1)
THEN 1 ELSE 0 END )
ELSE
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= MDY(07,01,Year( CURRENT )) AND
Sfhd_Loan_Activity.Date_Of_Closing <= MDY(09,30,Year( CURRENT ))
THEN 1 ELSE 0 END )
END

ORACLE:
      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) - 30
                       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'), 9) - 30
                       THEN
                           1
                       ELSE
                           0
                   END)
       END


Qtr 3 - Amount
CASE WHEN Month(CURRENT) >= 1 AND Month(CURRENT) <= 9
THEN
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= MDY(07,01,Year( CURRENT )-1)  AND
Sfhd_Loan_Activity.Date_Of_Closing <= MDY(09,30,Year( CURRENT )-1)
THEN Sfhd_Loan_Activity.Loan_Amt  ELSE 0 END )
ELSE
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= MDY(07,01,Year( CURRENT ))  AND
Sfhd_Loan_Activity.Date_Of_Closing <= MDY(09,30,Year( CURRENT ))
THEN Sfhd_Loan_Activity.Loan_Amt  ELSE 0 END )
END

ORACLE:
      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) - 30
                       THEN
                          Sfhd_Loan_Activity.Loan_Amt  
                       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'), 9) - 30
                       THEN
                          Sfhd_Loan_Activity.Loan_Amt  
                       ELSE
                           0
                   END)
       END


Qtr 4 - Nbr Closed this Qtr
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= MDY(10,01,Year( CURRENT )-1) AND
Sfhd_Loan_Activity.Date_Of_Closing <= MDY(12,31,Year( CURRENT )-1)
THEN 1 ELSE 0 END )

ORACLE:
      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'), -12) - 31
                       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'), 12) - 31
                       THEN
                           1
                       ELSE
                           0
                   END)
       END

Qtr 4 - Amount
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of_Closing >= MDY(10,01,Year( CURRENT )-1)  AND
Sfhd_Loan_Activity.Date_Of_Closing <= MDY(12,31,Year( CURRENT )-1)
THEN Sfhd_Loan_Activity.Loan_Amt  ELSE 0 END )

ORACLE:
      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'), -12) - 31
                       THEN
                          Sfhd_Loan_Activity.Loan_Amt  
                       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'), 12) - 31
                       THEN
                          Sfhd_Loan_Activity.Loan_Amt  
                       ELSE
                           0
                   END)
       END

TIA,
N
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 35200048
CASE WHEN Month(CURRENT) >= 1 AND Month(CURRENT) <= 9


So,  if Today is somewhere between January and September  then .....

your Oracle version is

  WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'q')) = 1


if Today is first quarter,  i.e.  January, February, March
and both of those are under your heading "Qtr 3"  
which I assume means 3rd quarter (July, August, September) so neither version seems to be correct


looks like you're using the right functions, but you're not using them correctly
similar problems are in your other cases

MDY(10,01,Year( CURRENT )-1)   is October 1, of last year
MDY(12,31,Year( CURRENT )-1  is December 31, of last year

I don't see anything in your Oracle code that looks like it's even trying to find October.

I do see this which looks like an attempt at December but
ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), 12) - 31

this finds January 1 of next year then subtracts 31 days,  so it will be December 1 of this year

Your question about case syntax is fine,  you're using CASE correctly near as I can tell.
The problem is the individual conditions you are checking, you're not doing date manipulations that correspond between Informix and Oracle syntax



0
 

Author Comment

by:Nakuru1234
ID: 35206778
sdstuber - I understand...
0
 

Author Closing Comment

by:Nakuru1234
ID: 35257812
I was looking for advice on how to write the query but it was hard to follow the solution. Hainkurt query was helpful though...
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35257866
why the B?

your previous response said "I understand", but your closing comment says "hard to follow"

if you needed further explanation, just ask
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question