#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    3
    Rep Power
    0

    Pls help with the logic


    I am in charge of implementing a few changes into a SQL code of a 1000 lines... Its taking me a while to comprehend how it is all tied together. I am stuck with this little piece of code and would appreciate if someone would explain clearly what happens here

    Code:
        when (CASE  when min(value(CLB.REC,'XXX')) <> 'XXX' 
              then min(value(CLB.REC,'XXX'))
              else Min(GLI.SEC)  
                    END)  in ('CR','FS')  then 'S.Liberty'
    There are a lot of these types of cases in the SQL and understanding this one will shed some light on the rest.
    thank you!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    as far as i know, there is no VALUE() function in mysql (the forum you posted in)*

    however, it appears to be doing the same as COALESCE

    thus, if CLB.REC is null, then the result of the VALUE() function will be 'XXX'

    then it compares the MIN of these values to 'XXX' and if it's something else, then that value will be the result of the nested CASE statement

    otherwise, the minimum GLI.SEC value is used for the result of the nested CASE statement

    then the result of the nested CASE statement is checked against 'CR' or 'FS', and if it's either of these values, then
    'S.Liberty' is the result of the (partially missing) outer CASE

    *edit: a quick google suggests you are using DB2, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    3
    Rep Power
    0
    Thank you so much for your help. I am sorry I didnt know that it was DB2

    I was wondering how would I include another "if" statement after the validation is over. You are stating that
    ...then the result of the nested CASE statement is checked against 'CR' or 'FS', and if it's either of these values, then
    'S.Liberty' is the result of the (partially missing) outer CASE
    I would like to insert a criteria before "then"? so it takes the value and checks it against 'CR' or 'FS' and if that is true I need to check if the field CLB.LOC = ABC and only THEN to return ... how would i do that?
    Thank you
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Originally Posted by ka_boom
    ... how would i do that?
    CASE syntax is really simple --
    Code:
    CASE WHEN somecolumn = 'somevalue'
         THEN 'someresult'
         WHEN somecolumn = 'someothervalue'
         THEN 'someotherresult'
         ELSE 'defaultresult'
     END
    you may nest another CASE for any of the 'results' above

    go ahead, give it a try
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    3
    Rep Power
    0
    That was VERY helpful! thank you

    I'll give a shot to one more question...

    column "ownership" is created through a nested case which has A LOT of criteria on how to decide what should be in that field. Next column "asset" is dependent on the value that ended up being in "ownership". If I just try to reference "ownership" the query fails... any idea how to work around it?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Originally Posted by ka_boom
    any idea how to work around it?
    yes

    put your ownership CASE expression into your query, then make that query a subquery in the FROM clause of a larger query
    Code:
    SELECT column1
         , column2
         , ownership * 9.37 AS asset
      FROM ( SELECT column1
                  , column2
                  , CASE WHEN ... END AS ownership
               FROM daTable ) AS subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo