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!

Convert CASE to Decode

Discussion in 'SQL PL/SQL' started by kiran.marla, Jan 31, 2010.

  1. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    nice one raj, how can we convert the following case to decode.

    case
    when job ='CLERK' then
    ....
    when deptno = 10 then
    ...
    end

    here in the case multiple columns are used.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: CASE AND DECODE : Two powerfull constructs of SQL

    This is Not logical.

    Imagine for one record Job='CLERK' and DEPTNO=10 , which case section will be executed. Anyway we can have Multiple column with multiple nested case/decode.
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Re: CASE AND DECODE : Two powerfull constructs of SQL

    k i.e. for multiple colmns testing, we have to use nested decode, right. thank you
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Re: CASE AND DECODE : Two powerfull constructs of SQL

    hi , in the constraints, what is the difference between "foreign key" and "references". i need the functional difference but not the syntax difference. thanks in advance
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: CASE AND DECODE : Two powerfull constructs of SQL

    Hi Sadik,
    Please Move this section of thread to PL/SQL Section.

    @ kiran,

    I hope you are telling about Referential constraints Syntax in oracle. Foreign key is the referential constraint in RDBMS.

    Code (SQL):
    ALTER TABLE ORDERS
    ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);
     
    Here a refercial constraint (Foreign key) is created with the column customer_sid of table ORDERS referring SID column of table CUSTOMER.

    In Nutshell, the 'foreign key' always 'references' a Primary or unique key. ie Parent child relation exists between Primary/Unique key- foreign key combination.
     
  6. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    @Raj , done!