Results 1 to 2 of 2

Thread: Statement WITH

  1. #1
    Join Date
    Nov 2002
    Posts
    2

    Unanswered: Statement WITH

    This Statement Works in DB2:

    WITH TEMP(PART, SUBPART, QUANTITY) AS
    (
    SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
    FROM PARTLIST ROOT
    WHERE ROOT.PART = '01'
    UNION ALL
    SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY
    FROM TEMP PARENT, PARTLIST CHILD
    WHERE PARENT.SUBPART = CHILD.PART
    )
    SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used"
    FROM TEMP
    GROUP BY PART, SUBPART
    ORDER BY PART, SUBPART;

    How can I obtain the same result using Oracle.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Statement WITH

    Originally posted by cfaria
    This Statement Works in DB2:

    WITH TEMP(PART, SUBPART, QUANTITY) AS
    (
    SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
    FROM PARTLIST ROOT
    WHERE ROOT.PART = '01'
    UNION ALL
    SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY
    FROM TEMP PARENT, PARTLIST CHILD
    WHERE PARENT.SUBPART = CHILD.PART
    )
    SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used"
    FROM TEMP
    GROUP BY PART, SUBPART
    ORDER BY PART, SUBPART;

    How can I obtain the same result using Oracle.
    It works in Oracle too, but only in the most recent versions (9i). Before that you would have to use an in-line view:

    SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used"
    FROM
    (
    SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
    FROM PARTLIST ROOT
    WHERE ROOT.PART = '01'
    UNION ALL
    SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY
    FROM TEMP PARENT, PARTLIST CHILD
    WHERE PARENT.SUBPART = CHILD.PART
    ) TEMP
    GROUP BY PART, SUBPART
    ORDER BY PART, SUBPART;

Posting Permissions

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