I do the reporting using sql and then report it off using 'TABLE FILE' ...

SQL DB2
SELECT F1 FROM TABLE1
;

TABLE ON TABLE HOLD AS HT1
END
-RUN

TABLE FILE HT1
PRINT
F1
END

Is there some syntax to put multiple queries in the 'SQL DB2' section ? I want to collect the
results of one query into a db2 temporary table and then to run a second query on that table.

This message has been edited. Last edited by: Kerry,

Govind,

A very simple example from a very good SQL manual:

WITH TEMP1 AS
(SELECT MAX(NAME) AS MAX_NAME
,MAX(DEPT) AS MAX_DEPT
FROM STAFF
)
SELECT *
FROM TEMP1;

This manual can be found on the web at http://users.sdsc.edu/~jrowley/db2/SQL%20Cookbook%20V8....%202003%20update.pdf

It is by far the best SQL query manual I've found so far.

.... or use a subselect if your SQL is up to it -

SQL
  SELECT column1
       , SUM(column2) AS column2
    FROM (SELECT this_value AS column1
                 another_value AS column2
            FROM owner.database.table
           WHERE clause IS what-ever) S1
ORDER BY column1
GROUP BY column1
;
TABLE FILE SQLOUT
PRINT *
ON TABLE .........

T

Thx for the tips. I have a subquery that repeats about 10 times in my main
query. I did not want to subselect 10 times. I wanted to do it as a
temporary table. After looking at your "With" query, I got the idea. For
example now I can write ....

WITH TEMP1 AS (SELECT * FROM T0 WHERE ....)
SELECT * FROM TEMP1,T1 ....
UNION
SELECT * FROM TEMP1,T2 ....
UNION
SELECT * FROM TEMP1,T3 ....

This message has been edited. Last edited by: Govind Jujare,

Did I mention that manual is free?

I looked at the book just now. Packed with examples. Thx.

Even when using dwf's WITH idea, I get the following error:

Multiple-step OLE DB operation generated
: errors


Does this work the same way for others or have you been able to use it?

Note:

The author's home page has moved to http://mysite.verizon.net/Graeme_Birchall/id1.html

A Jan. 2009 revision of the Cookbook PDF is available there.

I assume you are on DB2 ? (Because the WITH syntax is specific to db2)

SQL Server - didn't realize it was DB2 specific - thought it does work when I run it through Query Analyzer - just not when I run it through WebFOCUS

You are right. SQL server 2005 online reference has the "WITH" syntax.