CODEWITHSUNDEEP
sqlsap-asesybase-ase15
Karel Klíma
Karel Klíma

Reputation: 25

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

Upvotes: 1

Views: 56

Answers (1)

markp-fuso
markp-fuso

Reputation: 33749

Assumptions/understandings:

  • OP is using Sybase ASE which means we cannot use window functions (ie, ASE does not have support for window functions)
  • top 1 + order by togo ASC indicates we're looking for the row that contains min(togo)

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:

  • if togo is unique within this subset of rows then togo = min(f2.togo) should return one row
  • if togo is not unique within this subset of rows then togo = min(f2.togo) will return multiple rows (in this scenario OP will need to provide more details for determining which row is desired)
  • if OP chooses to go with the 3-way UNION then I believe this same subquery could be added to each of the 3x WHERE clauses to address the order by issue, and if togo is not unique within a given dataset then top 1 should be sufficient to limit each query to generating a single row

Upvotes: 0

Related Questions