1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Case when then expression with alias

Discussion in 'SQL PL/SQL' started by rosy, Sep 24, 2015.

  1. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Dear everyone please hepl me this Oracel command as below:
    case when company = 'US' then round(1/ currency_rate*CONV_FACTOR,4)
    when company = 'UG' or company = 'UCJ' then round(1/ currency_rate*CONV_FACTOR,4)
    else round(1/ currency_rate*CONV_FACTOR,4)
    END

    With above command it show column name "case............." but I want to display column name is "US" or "UG" or "Other" I also change command as below but error occur,
    case when company = 'US' then round(1/ currency_rate*CONV_FACTOR,4) AS "US"
    when company = 'UG' or company = 'UCJ' then round(1/ currency_rate*CONV_FACTOR,4) AS "UG"
    else round(1/ currency_rate*CONV_FACTOR,4) AS "OTHER"
    END

    Thank you!
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    As per my knowledge we can't achieve this.

    after end what ever we gave in one valid word, it will come as column name.

    Also you need all values in one column. Otherwise, If you want separate columns then we can give separate alias names with multiple case statements.

    Ex:
    select
    case when company = 'US' then round(1/ currency_rate*CONV_FACTOR,4) end us
    ,case when company = 'UG' or company = 'UCJ' then round(1/ currency_rate*CONV_FACTOR,4) end "UG or UCJ"
    ,case when company not in ('US','UG','UCJ') then round(1/ currency_rate*CONV_FACTOR,4) end other
    from table
    END
     
    rosy likes this.
  3. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Thank you,
    How about with Decode command it maybe same?
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    yes,

    It is also same.
     
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    As Jagadekara says -- changing the column header in this fashion is not possible. Even if it *could* be done, the way your query is written would be logically flawed. There is only one column header for column in a given query. If different rows from your CASE were to return different header values -- which one would be used?

    Can you explain why you are trying to do this? I don't really see a purpose in what you have asked because you already know the value since it's coming from the table itself. Also as I stated above, the value is applicable on a row-level rather than a query-level.
     
    rosy likes this.
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Better you can use three different columns naming US, UG and Other. If Company is US then the corresponding value is populated under that column and if the record is Non-US then that column remain null, the same way for other 2 columns (UG and Other)
     
  7. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    I also using three Case when for this one.
    Thank you so much.