Insert blank row to result after ORDER BY

Question

I have SQL Query, I want to add insert blank row in result so it is easy to see the result. I want to insert it after ORDER BY. don't know if it could be done.

Here is my select statement.

SELECT TableName.CREWACTIONFACTID
      ,TableName.CREWKEY as CrewKey
      ,TableName.EVENTKEY as EventID
      ,TableName.ACTIONSEQUENCE
      ,case TableName.ACTIONTYPE
            when 'DISPATCHED' then '2-Dispatched'
            when 'ASSIGNED' then '1-Assigned'
            when 'ENROUTE' then '3-Entoute'
            when 'ARRIVED' then '4-Arrived'
            else 'unknown'
            end  as Type
      ,TableName.STARTDATETIME as StartTime
      ,TableName.ENDDATETIME as EndTIme
      ,TableName.DURATION as Duration

  FROM DatabaseName.TableName TableName
  where 
    To_Date(to_char(TableName.STARTDATETIME, 'DD-MON-YYYY')) >= To_Date('?DATE1::?','MM/DD/YYYY')
    AND To_Date(to_char(TableName.ENDDATETIME, 'DD-MON-YYYY')) <= To_Date('?DATE2::?','MM/DD/YYYY')
   ORDER BY TableName.EVENTKEY, TableName.STARTDATETIME,TableName.ACTIONSEQUENCE

Answer 1

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.

Answer 2

Your question is rather complicated. SQL only guarantees the ordering of results, through the order by. It does not guarantee what happens afterwards. So, you have to put in the blank row and then add the information afterwords:

<your select query minus the order by>
union all
select NULL as CrewActionFatId, . . .
order by (case when CrewActionFactId is NULL then 1 else 0 end),
         TableName.EVENTKEY, TableName.STARTDATETIME,TableName.ACTIONSEQUENCE

In practice, @Michael's solution would normally work. But it is not guaranteed.

Also, you should decide whether you want blanks or NULLs. I'm guessing the first id is a number, so I'm setting it to NULL.

In general, such presentation niceties are handled by the calling application. Perhaps you need a better SQL query tool to see the data more cleanly.

Here is what the full query would look like (with all fields set to NULL, you can change to blank if you prefer):

SELECT TableName.CREWACTIONFACTID, TableName.CREWKEY as CrewKey,
       TableName.EVENTKEY as EventID, TableName.ACTIONSEQUENCE,
       (case TableName.ACTIONTYPE
            when 'DISPATCHED' then '2-Dispatched'
            when 'ASSIGNED' then '1-Assigned'
            when 'ENROUTE' then '3-Entoute'
            when 'ARRIVED' then '4-Arrived'
            else 'unknown'
        end) as Type,
       TableName.STARTDATETIME as StartTime,
       TableName.ENDDATETIME as EndTIme,
       TableName.DURATION as Duration
FROM DatabaseName.TableName TableName
where To_Date(to_char(TableName.STARTDATETIME, 'DD-MON-YYYY')) >= To_Date('?DATE1::?','MM/DD/YYYY') AND
      To_Date(to_char(TableName.ENDDATETIME, 'DD-MON-YYYY')) <= To_Date('?DATE2::?','MM/DD/YYYY')
union all
SELECT NULL AS CREWACTIONFACTID, NULL AS CrewKey, NULL AS EventID,
       NULL AS ACTIONSEQUENCE, NULL AS Type, NULL AS StartTime, NULL AS EndTime,
       NULL AS Duration
from dual
ORDER BY (case when CrewActionFactId is NULL then 1 else 0 end),
         TableName.EVENTKEY, TableName.STARTDATETIME, TableName.ACTIONSEQUENCE 

Answer 3

An odd request to be sure, but yes it can be done by making a UNION against a row of literal blank values. To make sure the order-by is applied to the real query, enclose the whole thing in () and then union it against the blank row.

SELECT * FROM
(SELECT TableName.CREWACTIONFACTID
  ,TableName.CREWKEY as CrewKey
  ,TableName.EVENTKEY as EventID
  ,TableName.ACTIONSEQUENCE
  ,case TableName.ACTIONTYPE
        when 'DISPATCHED' then '2-Dispatched'
        when 'ASSIGNED' then '1-Assigned'
        when 'ENROUTE' then '3-Entoute'
        when 'ARRIVED' then '4-Arrived'
        else 'unknown'
        end  as Type
  ,TableName.STARTDATETIME as StartTime
  ,TableName.ENDDATETIME as EndTIme
  ,TableName.DURATION as Duration
FROM DatabaseName.TableName TableName
where 
To_Date(to_char(TableName.STARTDATETIME, 'DD-MON-YYYY')) >= To_Date('?DATE1::?','MM/DD/YYYY')
AND To_Date(to_char(TableName.ENDDATETIME, 'DD-MON-YYYY')) <= To_Date('?DATE2::?','MM/DD/YYYY')
ORDER BY TableName.EVENTKEY, TableName.STARTDATETIME,TableName.ACTIONSEQUENCE
)
UNION ALL
SELECT 
  '' AS CREWACTIONFACTID,
  '' AS CrewKey,
  '' AS EventID,
  '' AS ACTIONSEQUENCE,
  '' AS Type,
  '' AS StartTime,
  '' AS EndTime,
  '' AS Duration
FROM dual

Finally, depending on how you are presenting this result, I would look into other methods of spacing out the result. Appending blank rows to a query for presentation purposes flies in the face of separation of business and presentation logic.

Answer 4

The result will be desplayed in an HTML page.

So, use SQL to extract the data, not to format the output. Depending on the page structure and layout, there are a lot of solutions. Have a look here.