CODEWITHSUNDEEP
sqlinformix
Satish Parida
Satish Parida

Reputation: 31

Order by on single column

I have Informix code:

select data as DATA from (
(select distinct('Date|Message_Type|Total') as data,0 as sort from dual)
union
(select 'dt: '||day||'|'||trim(msg_type)||'|'||nvl(round(total,2),0) as data,1 as sort from weekly_report_1)
                 )order by sort)

Output:

data1
Date|Message_Type|Total
dt: 02/10/2015|1440|0.00
dt: 02/10/2015|Total|-26290965.84
dt: 02/11/2015|1240|-23550538.87
dt: 02/11/2015|1440|0.00
dt: 02/11/2015|Total|-23550538.87
dt: 02/10/2015|1240|-26290965.84
dt: 02/12/2015|1240|-24181913.23
dt: 02/14/2015|Total|0.00
dt: 02/12/2015|1440|0.00
dt: 02/12/2015|Total|-24181913.23
dt: 02/13/2015|1240|-24611999.80
dt: 02/13/2015|1440|0.00
dt: 02/13/2015|Total|-24611999.80
dt: 02/14/2015|1240|0.00
dt: 02/14/2015|1440|0.00

When I am trying to do the following:

select * from(
select data as DATA1 from (
(select distinct('Date|Message_Type|Total') as data,0 as sort from dual)
union
(select 'dt: '||day||'|'||trim(msg_type)||'|'||nvl(round(total,2),0) as data,1 as sort from weekly_report_1)
                 )order by sort) order by DATA1;

I am getting:

Error: General error.Syntax error (State:37000, Native Code: FFFFD4CC)

I need to sort the table.

Upvotes: 0

Views: 514

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753930

You can only have one ORDER BY clause. You don't need the second level of sub-query.

SELECT data AS Data1 
  FROM (SELECT DISTINCT('Date|Message_Type|Total') AS Data,
               0 AS Sort
          FROM dual
        UNION
        SELECT 'dt: '||day||'|'||trim(msg_type)||'|'||nvl(round(total,2),0) AS Data,
               1 AS Sort
          FROM weekly_report_1
       ) AS AnyName
ORDER BY Sort, Data1;

Unless you have a seriously archaic (many years overdue for replacement) version of Informix, you can sort by a column that isn't listed in the select-list. If you do have a sufficiently archaic version of Informix that this doesn't work, you'll need to select the Sort value too. Or you can rely on Date preceding dt in the code set you're using and simply omit the ordering on Sort.

Upvotes: 2

Related Questions