Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    1

    Unanswered: select statement using union ,order by and decode

    SELECT DECODE(c.market_name,'Retail', 'CONSUMER'
    ,'Distribution','ELEC DISTR'
    ,'Original Equipment Manufacturer','ORIG EQ MFGR')REPORT_LABEL
    FROM CUSTOMER C
    GROUP BY decode(c.market_name,'Retail', 'CONSUMER'
    ,'Distribution','ELEC DISTR'
    ,'Original Equipment Manufacturer', 'ORIG EQ MFGR')

    UNION ALL

    SELECT DECODE(c.region_code , '6',
    'INTERNATIONAL','SUBSIDIARY') REPORT_LABEL
    FROM CUSTOMER C
    GROUP BY decode( c.region_code , '6',
    'INTERNATIONAL','SUBSIDIARY')
    );

    I am gettin the out put as
    CONSUMER
    ELEC DISTR
    ORIG EQ MFGR
    INTERNATIONAL
    SUBSIDIARY

    the output i need is to be in
    CONSUMER
    ELEC DISTR
    ORIG EQ MFGR
    SUBSIDIARY
    INTERNATIONAL

    i tried using the order by ..but it errors out...can any one help
    thanks in advance

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I dont quite understand you criteria for ordering but when you have a union query you have to order by <column index> where column index is the number of the column i.e. to order by the first column just do order by 1.

    Alan
    Last edited by AlanP; 12-10-04 at 05:42.

  3. #3
    Join Date
    Dec 2004
    Posts
    19
    (SELECT * FROM TABLE1
    UNION ALL
    SELECT * FROM TABLE) ORDER BY <COLUMNNUMBER>

    ORDER BY 1 IF 1 ST COLUMN 2 IF 2 ND COLUMN AND SO ON


    Quote Originally Posted by gopimullapudi
    SELECT DECODE(c.market_name,'Retail', 'CONSUMER'
    ,'Distribution','ELEC DISTR'
    ,'Original Equipment Manufacturer','ORIG EQ MFGR')REPORT_LABEL
    FROM CUSTOMER C
    GROUP BY decode(c.market_name,'Retail', 'CONSUMER'
    ,'Distribution','ELEC DISTR'
    ,'Original Equipment Manufacturer', 'ORIG EQ MFGR')

    UNION ALL

    SELECT DECODE(c.region_code , '6',
    'INTERNATIONAL','SUBSIDIARY') REPORT_LABEL
    FROM CUSTOMER C
    GROUP BY decode( c.region_code , '6',
    'INTERNATIONAL','SUBSIDIARY')
    );

    I am gettin the out put as
    CONSUMER
    ELEC DISTR
    ORIG EQ MFGR
    INTERNATIONAL
    SUBSIDIARY

    the output i need is to be in
    CONSUMER
    ELEC DISTR
    ORIG EQ MFGR
    SUBSIDIARY
    INTERNATIONAL

    i tried using the order by ..but it errors out...can any one help
    thanks in advance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •