I having the following query using WITH clause.

with tbl as ( select 'A' as COL1 From SYSIBM.SYSDUMMY1 ) select * from tbl

The above query works fine but when i want to use is as sub table i am not able to do this in DB2, but works on Oracle & SQL Server.


select * from (with tbl as ( select 'A' as COL1 From SYSIBM.SYSDUMMY1 ) select * from tbl) a

Any ideas to make the above SQL work? I know it does not make sense to use is as sub-stable, but from one of our C++ application, the SELECT has to be the first word.

Best Regards
Edwin

The sample is too simple.
So, I couldn't understand why you used common-table-expression.

Anyhow, try this...
select * from ( select * from (select 'A' as COL1 From SYSIBM.SYSDUMMY1 ) tbl) a

Thanks for your reply.

I have just simplified my requirement for posting. Actually we need to use WITH, as CTE table is used multiple times in the query.

DB2 does not support embedding cte's in sub selects, but you always rewrite this as:

select * from (with tbl as ( select 'A' as COL1 From SYSIBM.SYSDUMMY1 ) select * from tbl) a

with tbl as ( select 'A' as COL1 From SYSIBM.SYSDUMMY1 ), a as (select * from tbl)
select * from a;

Now I see that you have to start with select. The only solution I can think of is to wrap the cte in a table function:

create function f ( ... )
returns table ( ... )
return
with ... select ...

and then select from that function in your program:

select * from table(f(...));

/Lennart

Thanks Lennart !!

The function approach would be solution in DB2 for this issue.

In SQL Server & DB2 we can using functions to return table and is pretty simple unlike in Oracle where we need to use REF CURSOR which adds additional complication.

Our application needs to support all the 3 database mentioned, So i was trying to keep the SQLs as common as possible.

Thanks all for your valuable suggestions.

Not true, Oracle supports table functions as well. You can create a function that you use like this: Check out "pipelined functions" in the manual.

You don't need table functions for this purpose: creating a VIEW would be easier, and more transparent to the application:

Yeah True, Since i have many parameters to be passed, Use of functions would be ideal IMHO.