Solved

if then else logic

Posted on 2014-02-12
14
309 Views
Last Modified: 2014-02-16
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
Comment
Question by:sam2929
  • 7
  • 5
  • 2
14 Comments
 
LVL 13

Expert Comment

by:magarity
Comment Utility
The first version is easier to read, why would you change it to the second?
0
 

Author Comment

by:sam2929
Comment Utility
I am writing requirment doc they want in if then else format
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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

by:magarity
Comment Utility
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

by:sam2929
Comment Utility
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

Please look at coalese
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>> 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 73

Expert Comment

by:sdstuber
Comment Utility
>>>> Please look at coalese

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Expert Comment

by:magarity
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
>>>  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 73

Expert Comment

by:sdstuber
Comment Utility
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

by:magarity
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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

by:magarity
Comment Utility
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 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now