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.

Nunciature

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

Daladier

I believe the db2 equivalent of the Oracle DUAL dummy table is SYSIBM.SYSDUMMY1 – Spotweld

Actually, YES! That is exactly what I was looking for (provided that it is valid SQL for the DBMS I am using). I was digging in documentation trying to find out how to do a static query, but my SQL is a bit spotty! Thanks! – Nunciature

should probably use UNION ALL instead of UNION. UNION checks to see if there are duplicate records that need to be eliminated which takes extra time. UNION ALL keeps all records, but in this case there should not be any duplicates. – Spotweld

@Dave I really don't need to do UNION ALL because the number of records in the DB that match the actually select I do is small enough that it wouldn't matter. – Nunciature

Answer 2

Try this:

SELECT -1 AS ID, 'ALL THINGs' AS NAMEOFTHING FROM SYSIBM.SYSDUMMY1
UNION
SELECT ID, NAMEOFTHING FROM LISTOFTHINGS 

In

Thanks! I think you posted the correct code first, but the above answer is more complete, so I marked it as accepted. – Nunciature

Answer 3

In SQL Server Management Studio v18, there is a slight difference:

SELECT -1 AS ID, 'ALL THINGs' AS NAMEOFTHING
UNION
SELECT ID, NAMEOFTHING FROM LISTOFTHINGS 

Inviolate