Insert an empty string into the result after ORDER BY

Question

I have SQL Query, I want to add insert an empty string into the result, so it is easy to see the result. I want to insert it after ORDER BY. I don’t know if this can 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 like Michael and Gordon, just insert an empty string using union all , but you need to have it before 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 cannot use the case that Gordon had directly in order by , because this is not a selected value - you will get ORA-07185. (Note that the column names in order by are the aliases that you assigned to select , not the ones indicated in the table, and you do not include the table name / alias, and you do not need to use the null alias in the combined part, but you can do this for clarity).

But it depends on the fact that null sorted after any real values, which may not always be (not sure, but may be affected by the NLS parameters), and it is not known whether the real eventkey will never be null . Therefore, it is probably safer to enter a dummy column in both parts of the query and use it for 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; 

Date processing is odd, especially to_date(to_char(...)) . It looks like you're just trying to lose some of the time, 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 the use of any index, so it is better to leave it alone and get the variable part in the correct state for comparison:

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

+ 1 adds a day, so id DATE2 was 07/12/2012 , filter < 2012-07-13 00:00:00 , which matches <= 2012-07-12 23:59:59 .

Answer 2

An odd query, but yes, this can be done by doing UNION against a string of literal empty values. To make sure that the order applies to the real query, attach all this to () , and then concatenate it with an empty string.

 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 present this result, I would consider other methods for splitting the result. Adding blank lines to a query for presentation purposes flies in the face of the separation of business logic and presentation.

Answer 3

Your question is rather complicated. SQL only guarantees the ordering of results through order. This does not guarantee what will happen later. So you must enter an empty string and then add the following data:

 <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 usually works. But this is not guaranteed.

In addition, you must decide whether you want spaces or NULL. I assume the first id is a number, so I set it to NULL.

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

Here's what the full query will look like (with all fields set to NULL, you can change it to empty if you want):

 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 4

The result will be deleted on the HTML page.

So, use SQL to extract the data, not to format the output. There are many solutions depending on the page structure and layout. Take a look here .