?
Solved

if then else logic

Posted on 2014-02-12
14
Medium Priority
?
316 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
[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
  • 7
  • 5
  • 2
14 Comments
 
LVL 13

Expert Comment

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

Author Comment

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

Expert Comment

by:sdstuber
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 13

Expert Comment

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

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

Please look at coalese
0
 
LVL 74

Expert Comment

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

by:sdstuber
ID: 39854731
>>>> 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
 
LVL 13

Expert Comment

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

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

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

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

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

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

by:
sdstuber earned 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

762 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