Post reply

Using Case with 2 different variables in one query

  • October 17, 2017 at 3:46 pm

    #402605

    Hello Experts,
    I am trying to use 2 cases in the same query and is not working, this is the code:

    Select item as "Item",
    case koor
      when 1 then 'Production Order'
       when 2 then 'Purchase Order'
       when 3 then 'Sales Order'
    END as "Order Type"  ,
    case kost
        when 4 "Purchase Receipt"
        else "Test"
    end,
    trdt_loc as "location"
    from whinr110
    where item like '%CISN-UCSC-PSU2-1200=%'

    Do you have any suggestions?

    Thank you!

  • October 17, 2017 at 4:19 pm

    #1964112

    montserrat.deza - Tuesday, October 17, 2017 3:46 PM

    Hello Experts,
    I am trying to use 2 cases in the same query and is not working, this is the code:

    Select item as "Item",
    case koor
      when 1 then 'Production Order'
       when 2 then 'Purchase Order'
       when 3 then 'Sales Order'
    END as "Order Type"  ,
    case kost
        when 4 "Purchase Receipt"
        else "Test"
    end,
    trdt_loc as "location"
    from whinr110
    where item like '%CISN-UCSC-PSU2-1200=%'

    Do you have any suggestions?

    Thank you!

    Please describe what you mean by "not working".  Also, what do you want to call the column name for each of the CASEs?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 17, 2017 at 4:25 pm

    #1964114

    Also, koor and kost aren't variables.

  • October 17, 2017 at 7:59 pm

    #1964134

    you have a syntax error with "then" and you must use ' for text value and " for alias 


    Select item as "Item",
    case koor
      when 1 then 'Production Order'
      when 2 then 'Purchase Order'
      when 3 then 'Sales Order'
    END as "Order Type"  ,
    case kost
      when 4 THEN 'Purchase Receipt'
      else 'Test'
    end,
    trdt_loc as "location"
    from whinr110
    where item like '%CISN-UCSC-PSU2-1200=%'

  • October 18, 2017 at 12:57 pm

    #1964269

    Thank you!!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply