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!

Using CASE to map column data

Discussion in 'SQL PL/SQL' started by rudolf_r, Jun 24, 2014.

  1. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Hi All,

    How can I use CASE statement in a select statement to map data from sometimes the same column to be represented like:

    Where xxx_? is the COLUMN name:

    xxx_type xxx_1 xxx_2 xxx_3 xxx_4 xxx_5
    CC COMPANY_NAME COMPANY_REG_NO DATE_OF_BIRTH ID_NO Not used
    COMP COMPANY_NAME COMPANY_REG_NO DATE_OF_BIRTH ID_NO Not used
    GRN COMPANY_NAME COMPANY_REG_NO DATE_OF_BIRTH ID_NO Not used
    MUN COMPANY_NAME COMPANY_REG_NO DATE_OF_BIRTH ID_NO Not used
    OTHER COMPANY_NAME COMPANY_REG_NO DATE_OF_BIRTH ID_NO Not used
    PP SURNAME FIRST_NAMES DATE_OF_BIRTH ID_NO CD_MARTIAL_TYPE
    TRUST COMPANY_NAME COMPANY_REG_NO DATE_OF_BIRTH ID_NO Not used

    My idea is to use CASE in the following way:

    CASE
    WHEN CD_ENTITY_TYPE != 'PP' THEN xxx_type, xxx_1, xxx_2, xxxx_3, xxx_4, xxx_5 columns must be represented, ELSE IF CD_ENTITYPE = 'PP' then LOUMN values like SURNAME, FIRST_NAME, DATE OF BIRTH, ID_NO and CD_MARITAL_TYPE must be represented.


    Thank you.
    Rudolf
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Rudolf,

    The CASE function in a SQL statement returns a value based on a condition and thus cannot be used as such to return multiple columns (different sets of columns based on a condition on another column). You may opt to concatenate the columns (after a couple of conversion to strings), but I wonder whether it would be of any use for your requirement.

    You could use inline views to do it though (for ex. ... I don't have the exact names of your table/columns):

    Code (SQL):
    SELECT xtype,
      x1,
      x2,
      x3,
      x4,
      x5
    FROM
      (SELECT cd_entity_type xtype,
        company_name x1,
        company_reg_no x2,
        date_of_birth x3,
        id_no x4,
        'Not used' x5
      FROM mytable
      WHERE cd_entity_type != 'PP'
      UNION
      SELECT cd_entity_type xtype,
        surname x1,
        first_name x2,
        DATE OF birth x3,
        id_no x4,
        cd_marital_type x5
      FROM mytable
      WHERE cd_entity_type = 'PP'
      );
    Another option is to use CASE but in PL/SQL where you can build your queries dynamically.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    it is possible to add that :

    When using UNION is necessary to consider data types and to lead to uniform type.

    When using CASE in SQL it is necessary to consider data types and to lead to uniform type.
    since differently it is possible to receive ora-00932 error

    Code (SQL):


    rem simple test1
    SELECT
       CASE WHEN 1 = 1 THEN to_char(1) ELSE 'This is TRUE ' END x1
    FROM dual;
    rem simple test2
    SELECT
       CASE WHEN 1 = 2 THEN 1 ELSE 'This is FALSE' END x1
    FROM dual;

    SQL>
     
    X1
    --
    1
     
    SELECT
    CASE WHEN 1 = 2 THEN 1 ELSE 'This is FALSE' END x1
    FROM dual
     
    ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
     
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Agree 100% with you Sergey !
    The data types should be consistent.

    However, with PL/SQL you'll have all the flexibility to display any column and any number of columns in any order as long as you declare and use your variables correctly.