Select over a subquery having WITH clause

Question

I'm not familiar to DB2 and in Oracle we can do the following

select * from 
(WITH A AS (SELECT 'test' FROM DUAL)
 SELECT *
  FROM A )

I have a requirement to so similar to this in DB2, but it wont allow me. My requirement is not exactly same as above, but i simplified.

There is another issue, I'm trying to do a XML output on a results got using my below query. But the issue is when I call the XMLFOREST function, it restricts me to have only a column as input (in the example below example, A*B it does not allow).

SELECT XMLELEMENT (
      NAME "DATA",
      XMLAGG (XMLELEMENT (NAME "DJ_STOCK", (XMLFOREST (A, B, A * B)))))
FROM (SELECT *
      FROM (SELECT RTRIM (RTRIM (6.0000), '.') A,
                   RTRIM (RTRIM (6.0000), '.') B
              FROM SYSIBM.SYSDUMMY1))

I could do A*B in the inner query and call it as C and then call from the xmlforest function, but my actual scenario does not allow me to do that due to performance. Any workaround?

I'm using DB2 9.5 version

Answer 1

The whole purpose of the common table expression (what you call the WITH clause) is to avoid subqueries, mostly for readability. Subsequently, the CTE is used in the outermost SELECT:

WITH A (B) AS (SELECT 'test' FROM DUAL)
select * from 
(SELECT *
  FROM A )

Note that you must provide names for calculated columns (B for the literal value 'test' in the example above).