sqlsybasesap-asesybase-ase15

Sybase - How to use "ORDER BY" in derived table

After a while I'm again asking for help, as I couldn't think of solution.

I have this SQL query:

SELECT * FROM (SELECT TOP 1 
ac_registr,
event,
CASE 
WHEN dimension = "C" THEN "Cycles"
END AS "dimension",
togo AS "togo cycles",
CEILING (0) AS "togo days",
FLOOR (0) AS "togo hours",
absolute_due_at_ac AS "Due at cycles",
CONVERT( char(10), 0) AS "Due at date",
FLOOR (0) AS "Due at hours",
CONVERT( char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
event_display
FROM forecast 
WHERE ac_registr IN ('HEU')        
      AND dimension = 'C'
      AND expected_date <= 19669
      AND expected_date <> 0
/*ORDER BY togo ASC*/) C 

UNION

SELECT * FROM (SELECT TOP 1 
ac_registr,
event,
CASE 
WHEN dimension = "D" THEN "Days"
END AS "dimension",
0 AS "togo cycles",
CEILING (togo/1439) AS "togo days",
FLOOR (0) AS "togo hours",
0 AS "Due at cycles",
CONVERT( char(10), dateadd(day, absolute_due_at_ac, 'DEC 31 1971'), 104) AS "Due at date",
FLOOR (0) AS "Due at hours",
CONVERT( char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
event_display
FROM forecast 
WHERE ac_registr IN ('HEU')        
      AND dimension = 'D'
      AND expected_date <= 19669
      AND expected_date <> 0
/*ORDER BY togo ASC*/) D

UNION

SELECT * FROM(SELECT TOP 1 
ac_registr,
event,
CASE 
WHEN dimension = "H" THEN "Hours"
END AS "dimension",
0 AS "togo cycles",
CEILING (0) AS "togo days",
FLOOR (togo/60) AS "togo hours",
0 AS "Due at cycles",
CONVERT( char(10), 0) AS "Due at date",
FLOOR (absolute_due_at_ac/60) AS "Due at hours",
CONVERT( char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
event_display
FROM forecast 
WHERE ac_registr IN ('HEU')        
      AND dimension = 'H'
      AND expected_date <= 19669
      AND expected_date <> 0
/*ORDER BY togo ASC*/) H

The query contain three "blocks" of sub-queries, each returning only one row of values, which are needed. All the data comes from one table, but some of them (the important ones) needs to be calculated or converted. That is not a problem as shown data are in right format.

The trouble is, I did each block separately, so I could see, if the result is the one we needed. Then I connected the results together via UNION command, and if I put all ORDER BY clauses into commentary, it shows results in appropriate format, but with incorrect data (because they are no longer ordered).

I also tried to think of different solution, where I work with ordered data, but I don't know, how to select only "top row" of each dimension. The query for that is:

SELECT ac_registr,
       event,
       event_type,
       CASE
    WHEN dimension = "C" THEN "Cycles"
    WHEN dimension = "D" THEN "Days"
    WHEN dimension = "H" THEN "Hours"
END AS "dimension",
       togo,
       absolute_due_at_ac,
    CONVERT( char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date"
FROM forecast  
WHERE ac_registr IN ('HEU')        
      AND dimension IN ('C', 'D', 'H')      
      AND expected_date <= 19669
      AND expected_date <> 0
ORDER BY dimension, togo ASC

but there I have no idea, how to select only relative rows (top one for each dimension). I couldn't use fixed row number, as that may vary, and when I tried to use CASE command, it returned errors.

Is it possible to somehow order the data in the derived tables in first case? Or how to select the first row for each dimension in second case ?

I genuinely have no idea, so I would appreciate any help.

Also, I'll add a note, it is not possible to only order the data by forecast.togo column, since all the requirements are in different value size, as you may noticed in the first query.

Thank you in advance, Sincerely, Karel

Solution

  • Assumptions/understandings:

    One approach would be a subquery that finds the min(togo) for the subset of rows of interest, eg:

    SELECT ac_registr,
       event,
       event_type,
       CASE WHEN dimension = "C" THEN "Cycles"
            WHEN dimension = "D" THEN "Days"
            WHEN dimension = "H" THEN "Hours"
       END AS "dimension",
       togo,
       absolute_due_at_ac,
       CONVERT(char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date"

FROM   forecast f1

WHERE  ac_registr    IN ('HEU')        
AND    dimension     IN ('C', 'D', 'H')
AND    expected_date <= 19669
AND    expected_date <> 0

AND    togo = (select min(f2.togo)

               from   forecast f2

               WHERE  f2.ac_registr    IN ('HEU')        
               AND    f2.dimension      = f1.dimension     
               AND    f2.expected_date <= 19669
               AND    f2.expected_date <> 0)

    NOTES: