Nakuru1234
asked on
Informix SQL to Oracle CASE Statement
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
TIA,
N
Informix-SQL-to-Oracle-CASE-Stat.docx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HainKurt:
How about the rest? TIA...
N
How about the rest? TIA...
N
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/yyy y') -- if m,d,y is not 0 padded
other than these, I dont see anything else...
Informix --> Oracle
Month(Current) --> to_number(to_char(sysdate,
Year (CURRENT) --> to_number(to_char(sysdate,
MDY (m,d,y) --> to_date(m||d||y,'mmddyyyy'
MDY (m,d,y) --> to_date(m||'/'||d||'/'||y,
other than these, I dont see anything else...
ASKER
Yah, thats pretty much it. I'll tryt make some sense out of this and finish the rest. Thx.
N
N
ASKER
I did the necessary changes but I am getting errors...any advice?
TIA,
N
Informix-SQL-to-Oracle-CASE-Stat.docx
TIA,
N
Informix-SQL-to-Oracle-CASE-Stat.docx
I cannot open the attachment. What is the error message you are receiving?
ASKER
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
TIA,
N
>>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
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
ASKER
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
TIA,
N
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?
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?
ASKER
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.
N.
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.
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.
ASKER
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_Am t 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_Am t 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_Am t
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_Am t
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_Am t 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_Am t
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_Am t
ELSE
0
END)
END
TIA,
N
Qtr 3 – Nbr Closed this Qtr
CASE WHEN Month(CURRENT) >= 1 AND Month(CURRENT) <= 9
THEN
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of
Sfhd_Loan_Activity.Date_Of
THEN 1 ELSE 0 END )
ELSE
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of
Sfhd_Loan_Activity.Date_Of
THEN 1 ELSE 0 END )
END
ORACLE:
CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE,
THEN
SUM(
CASE
WHEN sfhd_loan_activity.date_of
ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), -12)
AND sfhd_loan_activity.date_of
ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), -9) - 30
THEN
1
ELSE
0
END)
ELSE
SUM(
CASE
WHEN sfhd_loan_activity.date_of
AND sfhd_loan_activity.date_of
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
Sfhd_Loan_Activity.Date_Of
THEN Sfhd_Loan_Activity.Loan_Am
ELSE
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of
Sfhd_Loan_Activity.Date_Of
THEN Sfhd_Loan_Activity.Loan_Am
END
ORACLE:
CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE,
THEN
SUM(
CASE
WHEN sfhd_loan_activity.date_of
ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), -12)
AND sfhd_loan_activity.date_of
ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), -9) - 30
THEN
Sfhd_Loan_Activity.Loan_Am
ELSE
0
END)
ELSE
SUM(
CASE
WHEN sfhd_loan_activity.date_of
AND sfhd_loan_activity.date_of
ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), 9) - 30
THEN
Sfhd_Loan_Activity.Loan_Am
ELSE
0
END)
END
Qtr 4 - Nbr Closed this Qtr
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of
Sfhd_Loan_Activity.Date_Of
THEN 1 ELSE 0 END )
ORACLE:
CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE,
THEN
SUM(
CASE
WHEN sfhd_loan_activity.date_of
ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), -12)
AND sfhd_loan_activity.date_of
ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), -12) - 31
THEN
1
ELSE
0
END)
ELSE
SUM(
CASE
WHEN sfhd_loan_activity.date_of
AND sfhd_loan_activity.date_of
ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), 12) - 31
THEN
1
ELSE
0
END)
END
Qtr 4 - Amount
SUM ( CASE WHEN Sfhd_Loan_Activity.Date_Of
Sfhd_Loan_Activity.Date_Of
THEN Sfhd_Loan_Activity.Loan_Am
ORACLE:
CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE,
THEN
SUM(
CASE
WHEN sfhd_loan_activity.date_of
ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), -12)
AND sfhd_loan_activity.date_of
ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), -12) - 31
THEN
Sfhd_Loan_Activity.Loan_Am
ELSE
0
END)
ELSE
SUM(
CASE
WHEN sfhd_loan_activity.date_of
AND sfhd_loan_activity.date_of
ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), 12) - 31
THEN
Sfhd_Loan_Activity.Loan_Am
ELSE
0
END)
END
TIA,
N
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sdstuber - I understand...
ASKER
I was looking for advice on how to write the query but it was hard to follow the solution. Hainkurt query was helpful though...
why the B?
your previous response said "I understand", but your closing comment says "hard to follow"
if you needed further explanation, just ask
your previous response said "I understand", but your closing comment says "hard to follow"
if you needed further explanation, just ask
ASKER