Results 1 to 2 of 2

Thread: Statement

  1. #1
    Join Date
    Nov 2002
    Posts
    2

    Unanswered: Statement

    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 SqlServer.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Something like this (I did not test it but this method is work for MSSQL)

    SELECT q.PART, q.SUBPART, SUM(q.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
    ) as q
    GROUP BY q.PART, q.SUBPART
    ORDER BY q.PART, q.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
  •