Results 1 to 9 of 9

Thread: With Clause

  1. #1
    Join Date
    Oct 2004
    Posts
    60

    Unanswered: With Clause

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  3. #3
    Join Date
    Oct 2004
    Posts
    60
    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.

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    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;

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    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

  6. #6
    Join Date
    Oct 2004
    Posts
    60
    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.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by edwin_fredrick View Post
    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.
    Not true, Oracle supports table functions as well. You can create a function that you use like this:
    Code:
    select * from table(my_function());
    Check out "pipelined functions" in the manual.

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by edwin_fredrick View Post
    The function approach would be solution in DB2 for this issue.

    Our application needs to support all the 3 database mentioned, So i was trying to keep the SQLs as common as possible.
    You don't need table functions for this purpose: creating a VIEW would be easier, and more transparent to the application:
    Code:
    create view xxx as
      with tbl as ( select 'A' as COL1 From SYSIBM.SYSDUMMY1 ) select * from tbl
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Oct 2004
    Posts
    60
    Yeah True, Since i have many parameters to be passed, Use of functions would be ideal IMHO.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •