How to add static values to SQL QUERY Result set in ORACLE

Question

I have a SQL Query that works perfectly. I am using Oracle 11g. The current SQL Query lists upcoming flights for the next 6 months and then list all past flights afterwards. So my query lists all flights for 2018 first and then all old flights after dating back early as 1998. I need to add two static values to this query. The two static values I want to add are "UPCOMING FLIGHTS" and "PAST FLIGHTS" to distinguish which flights are new and old. How can I achieve this? I need to incorporate this added code with the current SQL query that I have below. I have not been able to do this.

Current SQL Query:

SELECT FLIGHT_NMBR, SCHEDULED_LAUNCH_DATE
  FROM FLIGHTS
  WHERE DATA_VERSION_NAME = 'WORKING' 
  AND sequence_nmbr >= 0
   ORDER BY (CASE WHEN to_date(scheduled_launch_date, 'DD-MON-YY') 
     BETWEEN add_months(trunc(sysdate, 'MON'), 0) 
     AND add_months(trunc(sysdate, 'MON'), 6) THEN 1 ELSE 2 END),
       (CASE WHEN to_date(scheduled_launch_date, 'DD-MON-YY') 
     BETWEEN add_months(trunc(sysdate, 'MON'), 0) 
     AND add_months(trunc(sysdate, 'MON'), 6) THEN SCHEDULED_LAUNCH_DATE END),
     sequence_nmbr;

Results of current SQL query above:

FLIGHT_NMBR  SCHEDULED
------------ ---------
SpX-14       26-JAN-18
69P          09-FEB-18
SpX-DM1      09-MAR-18
54S          13-MAR-18
OA-9         14-MAR-18
55S          29-APR-18
SpX-15       06-JUN-18
SpX-DM2      22-JUN-18
70P          27-JUN-18
1A/R         20-NOV-98
2A           04-DEC-98

How can I achieve the desired result set below:

FLIGHT_NMBR      SCHEDULED
------------     ---------
UPCOMING FLIHGTS-------------------------------STATIC VALUE I WANT TO ADD
SpX-14           26-JAN-18
69P              09-FEB-18
SpX-DM1          09-MAR-18
54S              13-MAR-18
OA-9             14-MAR-18
55S              29-APR-18
SpX-15           06-JUN-18
SpX-DM2          22-JUN-18
70P              27-JUN-18
PAST FLIGHTS-----------------------------------STATIC VALUE I WANT TO ADD
1A/R             20-NOV-98
2A               04-DEC-98

Want my dropdown to look like this:

If `scheduled_launch_date` is a `DATE` column, what is `to_date(scheduled_launch_date, 'DD-MON-YY')` meant to do? — William Robertson, Jan 22 '18 at 19:10
As I already told you [here](https://stackoverflow.com/questions/48233239/how-to-have-static-value-as-first-value-in-list-using-javascript-oracle-apex/48260475#48260475), Select2 plug-in is the thing which does exactly what you ask in 2 or 3 last questions. — Dmitriy, Jan 22 '18 at 21:05
We've discussed it here: https://stackoverflow.com/questions/48233239/how-to-have-static-value-as-first-value-in-list-using-javascript-oracle-apex, didn't we? — Littlefoot, Jan 22 '18 at 22:06

score 1 · Accepted Answer · answered Jan 23 '18 at 03:38

1

you can do it this way,

SELECT 'UPCOMING FLIHGTS' text, NULL sched
  FROM dual
UNION ALL
<your select query for upcoming flights>
UNION ALL
SELECT 'PAST FLIGHTS' text, NULL sched
  FROM dual
UNION ALL
<your select query for past flights>

answered Jan 23 '18 at 03:38

eifla001

1,137
8
8

score 0 · Answer 2 · answered Jan 22 '18 at 19:08

What you want to do should be done at the application level. A SQL query returns a result set, which is a table -- consisting of rows with identical columns. Your new rows are not in the same format.

You can simplify the logic and include the flag on each row;

SELECT which, FLIGHT_NMBR, SCHEDULED_LAUNCH_DATE
FROM (SELECT F.*,
             (CASE WHEN to_date(scheduled_launch_date, 'DD-MON-YY') BETWEEN add_months(trunc(sysdate, 'MON'), 0) AND add_months(trunc(sysdate, 'MON'), 6)
                   THEN 'UPCOMING' ELSE 'PAST'
              END) as which
      FROM FLIGHTS F
     ) F
WHERE DATA_VERSION_NAME = 'WORKING' AND sequence_nmbr >= 0
ORDER BY (CASE WHEN which = 'UPCOMING' THEN 1 ELSE 2 END),
         (CASE WHEN which = 'UPCOMING' THEN SCHEDULED_LAUNCH_DATE END),
        sequence_nmbr;

So there is no way to add "UPCOMING FLIGHTS" to the top of result set and "PAST FLIGHTS" above the old ones? Your answer creates a brand new column. This will not work for me because I am using this in a select list. I can only display flight numbers. — Chris M, Jan 22 '18 at 19:21
@ChrisM . . . SQL result sets do not have such lines interspersed in them. — Gordon Linoff, Jan 23 '18 at 02:03

How to add static values to SQL QUERY Result set in ORACLE

2 Answers2