Adding a static value to the results of an SQL query

Question

I'm wondering if there is a way to accomplish this with an SQL query.

I have a table, lets call it "LISTOFTHINGS" that has two fields of interest "ID" and "NAMEOFTHING"

What I want to do is construct a query such that what gets returned is the results of this query:

SELECT ID, NAMEOFTHING FROM LISTOFTHINGS ORDER BY NAMEOFTHING 

and adds a row before the first row of the above query that has " -1, 'ALL THINGs' " as the values.

So if the table has the following three entries:

1, 'THING 1' 3, 'THING 3' 2, 'THING 2' 

Then the result that I want looks like this:

-1, 'ALL THINGS' 1, 'THING 1' 2, 'THING 2' 3, 'THING 3' 

I know that I can do the query and create the list with code, but inside the VB6 program where I am using this, I have a 3rd party app (which I don't have the code for) that takes the query to populate an ACTIVEX table control with the results. I don't have the hooks to go in to add the static value.

I also know that I could just put a record in the table for " -1, 'ALL THINGS' " but the problem is, if I do that, I will need to change a lot of places in the program to ignore that record when doing processing.

The 'ALL THINGS' value is sort of a pseudo record that handles a special case for one part of the program.

Answer 1

Could you do a union in the query?

SELECT -1 AS ID , 'ALL THINGS' AS NAMEOFTHING FROM DUAL /*'FROM DUAL' is an Oracle thing,                                                        not sure if you need to do                                                         something like that in DB2*/ UNION  SELECT ID, NAMEOFTHING FROM LISTOFTHINGS ORDER BY NAMEOFTHING 

---

Apparently, this is how it should be done for DB2

SELECT -1 AS ID , 'ALL THINGS' AS NAMEOFTHING FROM SYSIBM.SYSDUMMY1 UNION  SELECT ID, NAMEOFTHING FROM LISTOFTHINGS ORDER BY NAMEOFTHING