Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2010
    Posts
    3

    Unanswered: Decode from oracle to MS SQL Server assistance

    I have a decode sql from oracle, which i need to translate into SQL Server, it's fairly complex for an amatuer like me..

    I know the equivalent is the Case function, but I cant seen to write the case equivalent of the decode, could anyone please assist?

    Code:
    nvl(decode(totalcost,0,null,totalcost),
    nvl(decode(submitcost,0,null,submitcost),
    nvl(decode(apprvalue,0,null,apprvalue),
    nvl(decode(defestvalue,0,null,defestvalue),0)))) as "COST"

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    MHajat, I know nothing about Oracle but the 2 functions used (NVL and DECODE) seem simple enough. How they are being used is strange.

    DEDCODE is an if, then, Else type structure and can be replace by CASE.

    DECODE(expression, search, result (,search, result,...) (, default))

    Looking at one DECODE statement decode(totalcost,0,null,totalcost), TOTALCOST is the expression, 0 is the search, NULL is the result, and TOTALCOST is the default. One way to write the CASE would be:
    Code:
    CASE TOTALCOST
         WHEN 0 THEN NULL
                ELSE TOTALCOST
    END
    NVL checks the first value. If it is NOT NULL it will be returned. If is NOT NULL, the second value will be returned.

    Sol,what the entire nested statement is doing is checking 4 columns in sequence. If a column value is NOT 0 use that column value. If it IS 0, use a NULL instead. Then the nested NVL functions finds the first non-null value. IF they are all null return a 0.

    I believe this CASE will replace all the NVL and DECODE funciton nesting.
    Code:
    CASE
      WHEN TOTALCOST   <> 0 THEN TOTALCOST
      WHEN SUBMITCOST  <> 0 THEN SUBMITCOST
      WHEN APPRVALUE   <> 0 THEN APPRVALUE
      WHEN DEFESTVALUE <> 0 THEN DEFESTVALUE
                            ELSE 0
    END

  3. #3
    Join Date
    Jan 2010
    Posts
    3
    the way it has been written checks for 0's and nulls, if either them two are present, they are ignored, the case statement you have written only checks for 0's? am i right in saying that?
    the data we hold isnt exactly great, some fields are 0's and some are nulls, and if either are present, then they need to be ignored, it is a nested NVL statement, i think the NVL equivalent on SQL server is ISNULL, how would i incorporate that into the above code?

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    This may work better
    Code:
    CASE
      WHEN    TOTALCOST   <> 0 
           OR TOTALCOST   IS NOT NULL THEN TOTALCOST
      WHEN    SUBMITCOST  <> 0 
           OR SUBMITCOST  IS NOT NULL THEN SUBMITCOST
      WHEN    APPRVALUE   <> 0 
           OR APPRVALUE   IS NOT NULL THEN APPRVALUE
      WHEN    DEFESTVALUE <> 0 
           OR DEFESTVALUE IS NOT NULL THEN DEFESTVALUE
                            ELSE 0
    END

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this may work better
    Code:
    COALESCE(NULLIF(totalcost,0)
            ,NULLIF(submitcost,0)
            ,NULLIF(apprvalue,0)
            ,NULLIF(defestvalue,0)
            ,0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Much nicer, r937. I will need to remember NULLIF.

    Besides I think my OR's should have been AND's in the CASE example. But I would just use your example.

  7. #7
    Join Date
    Jan 2010
    Posts
    3
    yep brilliant guys.. thank you..

  8. #8
    Join Date
    Aug 2009
    Posts
    262
    NVL taking more then 2 arguments ??

    impossible
    Last edited by mishaalsy; 01-06-10 at 01:39.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Where are you seeing that? The original post has the NVL functions nested.

    Coalesce() FTW.
    George
    Home | Blog

  10. #10
    Join Date
    Aug 2009
    Posts
    262
    you are right sir ,..

    when i copy paste it on slqdeveloper i saw the nesting as per brackets


    do you have a working experience of oracle ? what i mean to say is oracle another of your expertise ?

    asking plain out of any admiration . for one's just knowledge

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nope, never had the pleasure of working with Oracle. The company I work for is a big Microsoft lover, so SQL Server is my bag!
    George
    Home | Blog

  12. #12
    Join Date
    Aug 2009
    Posts
    262
    te code in oracle doesnot make any sense to me. if you stay with me and read through what i write i am surely wish you to point out if there is any mistake

    here first
    nvl is a function taking two argument
    The NVL function is used to replace NULL values by another value.
    NVL( val1 (if null) , replace with val2 )

    decode (1,1,2,3)
    returnes 2
    caz decode 1,1,2,3 = ( if 1 =1 then 2 else 3)

    -----------------------------------------------------------
    nvl(decode(totalcost,0,null,totalcost),
    nvl(decode(submitcost,0,null,submitcost),
    nvl(decode(apprvalue,0,null,apprvalue),
    nvl(decode(defestvalue,0,null,defestvalue)

    ---------------------------------------------------------------------
    //

    isnull ( totalcost =0 then null else totalcost) ,
    isnull( submitcost=0 then null else submitcost) ,
    isnull ( apprvalue = 0 then null else apprvalue),
    isnull ( defestvalue = 0 then null else defestvalue)


    missing 2nd argument ( isnull is purely NVL equallent)
    ------------------------------------------------------


    now


    nvl(decode(totalcost,0,null,totalcost),
    nvl(decode(submitcost,0,null,submitcost),
    nvl(decode(apprvalue,0,null,apprvalue),
    nvl(decode(defestvalue,0,null,defestvalue),
    0)))) as "COST" ---

    ------------------------------------------------------------------------

    isnull ( (totalcost =0 then null else totalcost) ,
    isnull( (submitcost=0 then null else submitcost) ,
    isnull ( (apprvalue = 0 then null else apprvalue),
    isnull ( (defestvalue = 0 then null else defestvalue) ,
    0 ))))

    -----------------------------------------

    query is not complete . part of it is not complete even

    there is some thing before the first ISNULL

    the brackets were closed after the '0' just to close all brackets .

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Before we go too far in to this, you should avoid using the IsNull() function anyway; Coalesce() is the standard and is far more powerful.

    For your second SQL statement, you have trailing commas at the end of the first 3 lines, suggesting stuff has been nested in some way, but as you've pointed out, it hasn't been done correctly.

    However, in the 4th query the nesting is complete. If we simplify it further (remove the decode/if/case bit for now then it may be easier to see.

    Code:
    -- Your code from above
    isnull ( (totalcost =0 then null else totalcost) ,
    isnull( (submitcost=0 then null else submitcost) ,
    isnull ( (apprvalue = 0 then null else apprvalue),
    isnull ( (defestvalue = 0 then null else defestvalue) ,
    0 ))))
    
    -- Simplified
    isnull ( (totalcost =0 then null else totalcost) ,
    isnull( (submitcost=0 then null else submitcost) ,
    isnull ( (apprvalue = 0 then null else apprvalue),
    isnull ( (defestvalue = 0 then null else defestvalue) ,
    0 ))))
    
    -- Reformat
    IsNull(totalcost,
        IsNull(submitcost,
            IsNull(apprvalue,
                IsNull(defestvalue,
                  0
                )
            )
        )
    )
    As you can see the IsNull() function is nested just right!

    Now we move on to Coalesce(), the standard function. Coalesce differs from IsNull() as it accepts [practically] unlimited parameters which means our nested IsNull() becomes:
    Code:
    Coalesce(totalcost, submitcost, apprvalue, defestvalue, 0)
    Hope this helps
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •