UNION and adding specific value to query results 1

[bwschiek]

Hi:

Very new to DB2, so please be gentle!

I am doing a proof of concept using Microsoft SQL Server Reporting Services as the reporting engine for our DB2 databases.

While I am able to connect using iSeries Access for Windows and run reports just fine, I am unable to get the following to work and I think that it is just a DB2 SQL thing:

1. I can create a report that prompts the user to select a value to filter the result set with just fine. For example, I can create a report to list the sales offices based on a parameter of US State. I can run the report, select "New Hampshire" and the report returns only the New Hampshire offices. Great.

2. What I want to do is also offer the report user the option of listing all of the sales offices in the database. The following code is shown as an example provided by Microsoft:

SELECT 0 AS DepartmentID, 'All' AS Name
UNION
SELECT DepartmentID, Name
FROM Department
ORDER BY Name

When I try to use this code (modified for our databases, of course), I receive an error message from the ODBC driver that states "SQL0104 - Token was not valid. Valid tokens:, FROM INTO."

What am I doing wrong?

Thanks in advance.

 

[PruSQLer]

There's not FROM statement after your first SELECT. Are you just trying to insert a row with the values '0' and 'All' into the other table?

 

[bwschiek]

Yes, I am trying to include the values of '0' and 'All' in the query's result set in addition to the information returned from the other SELECT statement.

What am I doing wrong?

Thanks in advance.

Bruce.

 

[MarcLodge]

SELECT 0 AS DepartmentID, 'All' AS Name
from sysibm.sysdummy1
UNION
SELECT DepartmentID, Name
FROM Department
ORDER BY Name

Will do the trick, but the order by clause is going to relate to the whole results table.

If you are trying to get the

0 All

line to come out first and if you have a department named 'Admin' (for instance), this will not happen. You might be better off dealing with this in your reporting package, or alternatively add an extra column to both sides of the union giving a first sort by, eg:

SELECT 0 as SortID, 0 AS DepartmentID, 'All' AS Name
from sysibm.sysdummy1
UNION
SELECT 1 as SortID, DepartmentID, Name
FROM Department
ORDER BY SortID, Name

Hope this is not too confusing, and helps.

Marc

 

[bwschiek]

Thanks Marc! That did the trick!

Bruce.