Insert blank row to result after ORDER BY

oracle, sql

Solution

You can, pretty much as Michael and Gordon did, just tack an empty row on with `union all`, but you need to have it before the `order by`:

...
and to_date(to_char(t.enddatetime, 'DD-MON-YYYY')) <=
    to_date('?DATE2::?','MM/DD/YYYY')
union all
select null, null, null, null, null, null, null, null
from dual
order by eventid, starttime, actionsequence;

... and you can't use the `case` that Gordon had directly in the `order by` because it isn't a selected value - you'll get an ORA-07185. (Note that the column names in the `order by` are the aliases that you assigned in the `select`, not those in the table; and you don't include the table name/alias; and it isn't necessary to alias the `null` columns in the union part, but you may want to for clarity).

But this relies on `null` being sorted after any real values, which may not always be the case (not sure, but might be affected by NLS parameters), and it isn't known if the real `eventkey` can ever be `null` anyway. So it's probably safer to introduce a dummy column in both parts of the query and use that for the ordering, but exclude it from the results by nesting the query:

select crewactionfactid, crewkey, eventid, actionsequence, type,
    starttime, endtime, duration
from (
    select 0 as dummy_order_field,
        t.crewactionfactid,
        t.crewkey,
        t.eventkey as eventid,
        t.actionsequence,
        case t.actiontype
            when 'DISPATCHED' then '2-Dispatched'
            when 'ASSIGNED' then '1-Assigned'
            when 'ENROUTE' then '3-Enroute'
            when 'ARRIVED' then '4-Arrived'
            else 'unknown'
        end as type,
        t.startdatetime as starttime,
        t.enddatetime as endtime,
        t.duration
    from schema_name.table_name t
    where to_date(to_char(t.startdatetime, 'DD-MON-YYYY')) >=
        to_date('?DATE1::?','MM/DD/YYYY')
    and to_date(to_char(t.enddatetime, 'DD-MON-YYYY')) <=
        to_date('?DATE2::?','MM/DD/YYYY')
    union all
    select 1, null, null, null, null, null, null, null, null
    from dual
)
order by dummy_order_field, eventid, starttime, action sequence;

The date handling is odd though, particularly the `to_date(to_char(...))` parts. It looks like you're just trying to lose the time portion, in which case you can use `trunk` instead:

where trunc(t.startdatetime) >= to_date('?DATE1::?','MM/DD/YYYY')
and trunc(t.enddatetime) <= to_date('?DATE2::?','MM/DD/YYYY')

But applying any function to the date column prevents any index on it being used, so it's better to leave that alone and get the variable part in the right state for comparison:

where t.startdatetime >= to_date('?DATE1::?','MM/DD/YYYY')
and t.enddatetime < to_date('?DATE2::?','MM/DD/YYYY') + 1

The `+ 1` adds a day, so id `DATE2` was `07/12/2012`, the filter is `< 2012-07-13 00:00:00`, which is the same as `<= 2012-07-12 23:59:59`.

Problem

Original source