Menu
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.
220
Static values can be inserted into a resultset returned from a SELECT query as another column. Simply use the static value as a column to select, and the query will return a column where the name of the column is the static value, and every row in that column will return that same static value.
You can add static value when you use INSERT INTO SELECT MySQL query. Write the value directly in the select statement or you can add with the help of variable which initializes the value. SET @yourVariableName − = yourstaticValue; INSERT INTO yourSecondTableName(yourColumnName1,yourColumnName2,....
The SQL INSERT INTO SELECT Statement The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
