Solved

# if then else logic

Posted on 2014-02-12
314 Views
Hi,
I have below oracle function how can i covert this to if then else

COALESCE(NVL(EDESC_08,CODE_08),
NVL(EDESC_07,CODE_07),
NVL(EDESC_06,CODE_06),
NVL(EDESC_05,CODE_05),
NVL(EDESC_04,CODE_04),
NVL(EDESC_03,CODE_03),
NVL(EDESC_02,CODE_02),
NVL(EDESC_01,lCODE_01))  EDESC_08

doing below do that make sense.

IF IsNull(EDESC_08) THEN CODE_08
ELSE IF IsNull(EDESC_07) THEN CODE_07
ELSE IF IsNull(EDESC_06) THEN CODE_06
ELSE IF IsNull(EDESC_05) THEN CODE_05
ELSE IF IsNull(EDESC_04) THEN CODE_04
ELSE IF IsNull(EDESC_03) THEN CODE_03
ELSE IF IsNull(EDESC_02) THEN CODE_02
ELSE IF IsNull(EDESC_01) THEN CODE_01
ELSE EDESC_08

Thanks
0
Question by:sam2929
[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
• 7
• 5
• 2

LVL 13

Expert Comment

ID: 39854631
The first version is easier to read, why would you change it to the second?
0

Author Comment

ID: 39854647
I am writing requirment doc they want in if then else format
0

LVL 74

Expert Comment

ID: 39854687
SQL doesn't really have if/else

The closest it has is CASE which isn't too bad here.

CASE
WHEN edesc_08 IS NOT NULL THEN edesc_08
WHEN code_08 IS NOT NULL THEN code_08
WHEN edesc_07 IS NOT NULL THEN edesc_07
WHEN code_07 IS NOT NULL THEN code_07
WHEN edesc_06 IS NOT NULL THEN edesc_06
WHEN code_06 IS NOT NULL THEN code_06
WHEN edesc_05 IS NOT NULL THEN edesc_05
WHEN code_05 IS NOT NULL THEN code_05
WHEN edesc_04 IS NOT NULL THEN edesc_04
WHEN code_04 IS NOT NULL THEN code_04
WHEN edesc_03 IS NOT NULL THEN edesc_03
WHEN code_03 IS NOT NULL THEN code_03
WHEN edesc_02 IS NOT NULL THEN edesc_02
WHEN code_02 IS NOT NULL THEN code_02
WHEN edesc_01 IS NOT NULL THEN edesc_01
WHEN code_01 IS NOT NULL THEN code_01
END

Your original coalesce doesn't have a "catch all else" if everything is null, so this one doesn't either.  If all 16 values are null, the case, like the coalesce will return null.

Otherwise, it will return the first non null value it finds, using the same order as found in the coalesce and nested nvls

Note your if/else pseudo code also drops half of the values.  the "edesc" are never returned but could be in the coalesce.  MY case mimics the coalesce, not the pseudo procedural code
0

LVL 13

Expert Comment

ID: 39854706
That last line appears to be not needed.  In the code, that's the column alias, not the final ELSE.  End your IF-ELSE at the second to last line, with the 01
0

Author Comment

ID: 39854715
This case don't seem right as we need to put not null if not found in level 8 then go level 7 check there if 7 is null then go level 6 but if you find level 6 stop

0

LVL 74

Expert Comment

ID: 39854720
>> This case don't seem right

try it

if you find a counter example where the case and the coalesce don't produce the same results, post the sample data here along with the expected results
0

LVL 74

Expert Comment

ID: 39854731

I did, that's why I built the case the way I did.

but, let's check it out....

COALESCE(
NVL(edesc_08, code_08),
NVL(edesc_07, code_07),
NVL(edesc_06, code_06),
NVL(edesc_05, code_05),
NVL(edesc_04, code_04),
NVL(edesc_03, code_03),
NVL(edesc_02, code_02),
NVL(edesc_01, lcode_01)
)

coalesce says return the first non-null value in the list.

If desc8 is not null, NVL returns it and the coalesce stops looking
if desc8 is null then code8 is returned
if code8 is null the coalesce moves to next nvl

If desc7 is not null, NVL returns it and the coalesce stops looking
if desc7 is null then code7 is returned
if code7 is null the coalesce moves to next nvl

If desc6 is not null, NVL returns it and the coalesce stops looking
if desc6 is null then code6 is returned
if code6 is null the coalesce moves to next nvl

If desc5 is not null, NVL returns it and the coalesce stops looking
if desc5 is null then code5 is returned
if code5 is null the coalesce moves to next nvl
etc

the case does the same thing  look at desc first, then code if needed, if still null, move to next lower number repeat until you find one or run out of values
0

LVL 13

Expert Comment

ID: 39856941
Umm, the author of the question said he's writing the support doc from the existing script, not trying to figure out the script.  It must be doing what he wants if this is what is already coded so why are you picking over the code?
0

LVL 74

Expert Comment

ID: 39857074
>>>  why are you picking over the code?

Umm, because he asked me to     ;)

"Please look at coalese "   http:#a39854715

Read my response again, I wasn't questioning or implying whether the coalesce did what it was supposed to do or not.  I was explaining what it DOES do (regardless of whether it's correct or not)  and hence use that explanation that the cascading WHEN conditions of the CASE perform the same if/else logic.

using the same explanation, hopefully the asker would be able to justify a further simplification to just using this...

COALESCE(
edesc_08,
code_08,
edesc_07,
code_07,
edesc_06,
code_06,
edesc_05,
code_05,
edesc_04,
code_04,
edesc_03,
code_03,
edesc_02,
code_02,
edesc_01,
code_01
)

it's the exact same logic as the original coalesce with nested nvl's, hopefullly easier to read though.
0

LVL 74

Expert Comment

ID: 39857135
Here's an ultra simple test case.  There are only 65536 possible combinations (16 values, either null or not-null)

So, it's simple to test them all

WITH x1
AS (SELECT 1 edesc_01 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x2
AS (SELECT 2 code_01 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x3
AS (SELECT 3 edesc_02 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x4
AS (SELECT 4 code_02 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x5
AS (SELECT 5 edesc_03 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x6
AS (SELECT 6 code_03 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x7
AS (SELECT 7 edesc_04 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x8
AS (SELECT 8 code_04 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x9
AS (SELECT 9 edesc_05 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x10
AS (SELECT 10 code_05 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x11
AS (SELECT 11 edesc_06 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x12
AS (SELECT 12 code_06 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x13
AS (SELECT 13 edesc_07 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x14
AS (SELECT 14 code_07 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x15
AS (SELECT 15 edesc_08 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL),
x16
AS (SELECT 16 code_08 FROM DUAL
UNION ALL
SELECT NULL FROM DUAL)
SELECT *
FROM (SELECT COALESCE(
NVL(edesc_08, code_08),
NVL(edesc_07, code_07),
NVL(edesc_06, code_06),
NVL(edesc_05, code_05),
NVL(edesc_04, code_04),
NVL(edesc_03, code_03),
NVL(edesc_02, code_02),
NVL(edesc_01, code_01)
)
original_version,
CASE
WHEN edesc_08 IS NOT NULL THEN edesc_08
WHEN code_08 IS NOT NULL THEN code_08
WHEN edesc_07 IS NOT NULL THEN edesc_07
WHEN code_07 IS NOT NULL THEN code_07
WHEN edesc_06 IS NOT NULL THEN edesc_06
WHEN code_06 IS NOT NULL THEN code_06
WHEN edesc_05 IS NOT NULL THEN edesc_05
WHEN code_05 IS NOT NULL THEN code_05
WHEN edesc_04 IS NOT NULL THEN edesc_04
WHEN code_04 IS NOT NULL THEN code_04
WHEN edesc_03 IS NOT NULL THEN edesc_03
WHEN code_03 IS NOT NULL THEN code_03
WHEN edesc_02 IS NOT NULL THEN edesc_02
WHEN code_02 IS NOT NULL THEN code_02
WHEN edesc_01 IS NOT NULL THEN edesc_01
WHEN code_01 IS NOT NULL THEN code_01
END
case_version,
COALESCE(
edesc_08,
code_08,
edesc_07,
code_07,
edesc_06,
code_06,
edesc_05,
code_05,
edesc_04,
code_04,
edesc_03,
code_03,
edesc_02,
code_02,
edesc_01,
code_01
)
all_coalesce_version
FROM x1,
x2,
x3,
x4,
x5,
x6,
x7,
x8,
x9,
x10,
x11,
x12,
x13,
x14,
x15,
x16)
WHERE NVL(original_version, 0) != NVL(case_version, 0)
OR NVL(original_version, 0) != NVL(all_coalesce_version, 0)

note, this returns 0 rows, because all 3 versions are functionally equivalent
remove the where clause and you'll get all 65536 possible combinations

The fact that I made up values is not important.  The coalesce/case/nvl is only checking for null or not null.  So the actual content of not-null is irrelevant.
0

LVL 13

Expert Comment

ID: 39857761
I guess I'm lost; is this a case of backfilling documentation to existing code or a case of trying to code without documentation?
I still think the original coalesce/nvl to if-else needs the last 'else' removed because that's the column alias in the coalesce section, not a final default value.
Are we now on a hunt for how to provide the final default value? What does the spec require? But there is no spec, so what ought the final default value be?
0

LVL 74

Expert Comment

ID: 39857807
the question was to rewrite the coalesce in if/else.   No such syntax exists in sql; but there are other options that might be easier to read understand.  I think both of the ones I posted are more logical representations.  The original with nested nvl's is needlessly complex and cumbersome.

I don't think the pseudo-code should not be considered a functional guide, but rather a logical guide.

However, it's not really necessary as the coalesce and nvl itself provides all of the logic as well as a functional requirement.

The final answer is going to be somewhat subjective.

Mimicking the functionality is objective.  I've done that and demonstrated that I did it correctly.  However,  the choice of what "looks" the most logical and easiest to document is not so clear cut.

To me the if/else/if/else as described in words with conditions back to other quasi-levels is WAAAAAY too complex for such a logically simple condition.  It's a simple 16-element list.  The original didn't display it as such, but that's because it's too complex.
0

LVL 13

Expert Comment

ID: 39859746
If we take the opening comment:
"Hi, I have below oracle function how can i covert this to if then else"
combined with the followup as to why:
"I am writing requirment doc they want in if then else format"

It seems that the function is in place and doing whatever it is doing and what's needed is some pseudo-code in IF-ELSE format to document the existing function.  The IF-ELSE doesn't seem meant to be replacing the running code, just describing it, perhaps for some analyst who isn't good at SQL but understands general programming concepts.
0

LVL 74

Accepted Solution

sdstuber earned 500 total points
ID: 39859766
ok, if what is needed is just a text description of the functionality  that's simple enough.

I already outlined 8 through 5 above in a code-to-text literal translation.
See post http:#a39854731

But, if you want to simply describe the functionality then it's much easier...

All that code above does is this:

if edesc_08 is not null then return edesc_08
else if code_08 is not null then return code_08
else if edesc_07 is not null then return edesc_07
else if code_07 is not null then return code_07
else if edesc_06 is not null then return edesc_06
else if code_06 is not null then return code_06
else if edesc_05 is not null then return edesc_05
else if code_05 is not null then return code_05
else if edesc_04 is not null then return edesc_04
else if code_04 is not null then return code_04
else if edesc_03 is not null then return edesc_03
else if code_03 is not null then return code_03
else if edesc_02 is not null then return edesc_02
else if code_02 is not null then return code_02
else if edesc_01 is not null then return edesc_01
else if code_01 is not null then return code_01
else then return null

the final "else return null" is implicit in all 3 code forms above, but in text description it should probably be spelled out.

If the description seems overly simplistic,  it's not.  It's just that the original code was overly complex.
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Migration from SQL server to oracle (XML input) 4 69
Updating a temp table inside a PL/SQL block 3 64
Oracle Errors 11 87
construct a query sql 11 42
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacyâ€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from thâ€¦
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of demâ€¦
###### Suggested Courses
Course of the Month6 days, 12 hours left to enroll