Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Posts
    7

    Red face Unanswered: Hierarchical Query

    Hello guys,
    on the SQL reference, appendix M: Recursion example there is this query:

    ---------------------------------------------------------------
    WITH RPL (PART, SUBPART, QUANTITY) AS
    ( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
    FROM PARTLIST ROOT
    WHERE ROOT.PART = '01'
    UNION ALL
    SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
    FROM RPL PARENT, PARTLIST CHILD
    WHERE PARENT.SUBPART = CHILD.PART
    )
    SELECT DISTINCT PART, SUBPART, QUANTITY
    FROM RPL
    ORDER BY PART, SUBPART, QUANTITY;
    -----------------------------------------------------------

    I properly created the table PARTLIST, but when I run this query from the command center I get this error:

    -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.
    SQL0104N An unexpected token "(" was found following "". Expected tokens
    may
    include: "IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING> ". SQLSTATE=42601
    -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.--.-.-.-.-..

    Is this sintax correct or do I have to include it in a stored procedure?
    Thanks a lot

    Matteo

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Hierarchical Query

    I'm not sure what you are trying to do, but let me point out the obvious ..


    UNION ALL
    SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
    FROM RPL PARENT, PARTLIST CHILD
    WHERE PARENT.SUBPART = CHILD.PART

    In this portion you select from RPL (which is the temp table you are creating .. So this will not work ....

    But you can do

    with temp1(i,j) as
    (select a,b from tab1),
    temp2(x,y) as
    (select i,j from temp1)
    select * from temp2

    ie, in your 'WITH' part you can refer a temp table already defined ....
    But you cannot do

    But you can do

    with temp1(i,j) as
    (select a,b from temp2),
    temp2(x,y) as
    (select i,j from tab1)
    select * from temp2

    HTH

    Sathyaram




    Originally posted by matteogd
    Hello guys,
    on the SQL reference, appendix M: Recursion example there is this query:

    ---------------------------------------------------------------
    WITH RPL (PART, SUBPART, QUANTITY) AS
    ( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
    FROM PARTLIST ROOT
    WHERE ROOT.PART = '01'
    UNION ALL
    SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
    FROM RPL PARENT, PARTLIST CHILD
    WHERE PARENT.SUBPART = CHILD.PART
    )
    SELECT DISTINCT PART, SUBPART, QUANTITY
    FROM RPL
    ORDER BY PART, SUBPART, QUANTITY;
    -----------------------------------------------------------

    I properly created the table PARTLIST, but when I run this query from the command center I get this error:

    -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.
    SQL0104N An unexpected token "(" was found following "". Expected tokens
    may
    include: "IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING> ". SQLSTATE=42601
    -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.--.-.-.-.-..

    Is this sintax correct or do I have to include it in a stored procedure?
    Thanks a lot

    Matteo

  3. #3
    Join Date
    Apr 2003
    Posts
    7

    Re: Hierarchical Query

    Thank you Sathyaram,
    I have found that sintax on the SQL reference:

    http://www-3.ibm.com/cgi-bin/db2www/...s0db2s0537.htm

    I believe that the purpose of nesting the table definition is to obtain a recursion so as to retrieve the whole hierarchy of records.

    By the way, I have just tried your sintax:

    WITH TEMP1 (A,B) AS
    (SELECT A,B FROM TAB1)
    SELECT A, B FROM TEMP1;

    And it does not work, the error returned is the same.
    I do not know whether this 'WITH' sintax is not allowed in the version I'm running (version 7) or I have to insert it in a stored procedure declaration.
    Have you done it in version 7?

    Thanks

    Matteo



    Originally posted by sathyaram_s
    I'm not sure what you are trying to do, but let me point out the obvious ..


    UNION ALL
    SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
    FROM RPL PARENT, PARTLIST CHILD
    WHERE PARENT.SUBPART = CHILD.PART

    In this portion you select from RPL (which is the temp table you are creating .. So this will not work ....

    But you can do

    with temp1(i,j) as
    (select a,b from tab1),
    temp2(x,y) as
    (select i,j from temp1)
    select * from temp2

    ie, in your 'WITH' part you can refer a temp table already defined ....
    But you cannot do

    But you can do

    with temp1(i,j) as
    (select a,b from temp2),
    temp2(x,y) as
    (select i,j from tab1)
    select * from temp2

    HTH

    Sathyaram

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    The syntax looks fine to me and no, you don't have to run it in a stored procedure.

    Can you expand on how you are actually running the query? Have you tried running it in the command centre?

  5. #5
    Join Date
    Apr 2003
    Posts
    7
    Thank you Damian,
    yes I have run it both in the Command Center and on Quest central
    but the error returned is "..SQL0104N An unexpected token..."

    Have you run a query with the 'WITH' option in version 7 for OS390?

    Thanks

    Matteo


    Originally posted by Damian Ibbotson
    The syntax looks fine to me and no, you don't have to run it in a stored procedure.

    Can you expand on how you are actually running the query? Have you tried running it in the command centre?

  6. #6
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by matteogd
    Thank you Damian,
    yes I have run it both in the Command Center and on Quest central
    but the error returned is "..SQL0104N An unexpected token..."

    Have you run a query with the 'WITH' option in version 7 for OS390?

    Thanks

    Matteo
    I'm using UDB version7. I have no problems using the WITH clause whether for recursive processing or otherwise.

  7. #7
    Join Date
    Apr 2003
    Posts
    7

    Angry

    Hi Damian,
    I have compared the SQL Reference for DB2 OS/390 with the version for Unix-Linux-Win2K, and it seems that it is possible to specify an alias for a common table expression with the 'WITH' syntax only in the version for Unix-Windows. OS/390 Sucks!!
    Thanks anyway

    Matteo

    Originally posted by Damian Ibbotson
    I'm using UDB version7. I have no problems using the WITH clause whether for recursive processing or otherwise.

Posting Permissions

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