February 16, 2006 at 7:07 am
Hi all,
I have been working on this report for a while and I am lost. I am getting #'s correctly but I am getting too many part numbers. I was wondering, with a union all query if i can put the where clause on the outside of the union all statement like the order by clause? And if not does anyone have a suggestion on what I should do? The first select statement is my problem because I can not put the where clause of the other two select statements. As soon as I join all or a combination of tables I get duplicate rows of data.
Here is the syntax....
SELECT *
FROM(
SELECT
PART.PART_NBR AS PART_NBR
,PART.PART_DESC AS PART_DESC
,PART.PART_TYPE AS PART_TYPE
,NULL AS ORD_STAT
,NULL AS ORD_TYPE
,NULL AS RQMT_CODE
,INVD.QTY_ON_HAND AS QTY_ON_HAND
,TO_NUMBER(NULL) AS DEMAND_OVERDUE
,TO_NUMBER(NULL) AS DEMAND_0TO20DAYS
,TO_NUMBER(NULL) AS DEMAND_21TO40DAYS
,TO_NUMBER(NULL) AS DEMAND_41TO60DAYS
,TO_NUMBER(NULL) AS DEMAND_61TO90DAYS
,TO_NUMBER(NULL) AS DEMAND_91TO180DAYS
,TO_NUMBER(NULL) AS DEMAND_BEYOND
,TO_NUMBER(NULL) AS ORDERS_OVERDUE
,TO_NUMBER(NULL) AS ORDERS_0TO20DAYS
,TO_NUMBER(NULL) AS ORDERS_21TO40DAYS
,TO_NUMBER(NULL) AS ORDERS_41TO60DAYS
,TO_NUMBER(NULL) AS ORDERS_61TO90DAYS
,TO_NUMBER(NULL) AS ORDERS_91TO180DAYS
,TO_NUMBER(NULL) AS ORDERS_BEYOND
,TO_DATE(NULL) AS PLAN_RECV_DATE
,TO_DATE(NULL) AS DATE_REQD
,TO_NUMBER(NULL) AS QTY_REQD
,TO_NUMBER(NULL) AS OTY_ISSUED
,TO_NUMBER(NULL) AS DEMAND_DUE
,NULL AS ORD_NBR
,NULL AS LINE_NBR
,NULL AS STATUS
,CASE WHEN
INVD.LOCATION_KEY LIKE '%SCRAP%' OR INVD.LOCATION_KEY LIKE '%DROP%'
THEN 0
ELSE INVD.QTY_ON_HAND
END AS AVAIL_INVENTORY
,CASE WHEN
INVD.LOCATION_KEY LIKE '%LINE%'
THEN INVD.QTY_ON_HAND
ELSE 0
END AS LINE_INVENTORY
,CASE WHEN
INVD.LOCATION_KEY LIKE '%SCRAP%'
THEN INVD.QTY_ON_HAND
ELSE 0
END AS SCRAP_INVENTORY
,CASE WHEN
RPAD(TO_CHAR(INVD.LOCATION_KEY),2) = 'RD' OR RPAD(TO_CHAR(INVD.LOCATION_KEY),2) = 'RI'
THEN INVD.QTY_ON_HAND
ELSE 0
END AS RECV_INVENTORY
,CASE WHEN
INVD.LOCATION_KEY LIKE '%DROP%'
THEN INVD.QTY_ON_HAND
ELSE 0
END AS DROP_INVENTORY
,TO_NUMBER(NULL) AS QTY_ON_ORD
,TO_NUMBER(NULL) AS QTY_RECVD
,NULL AS HORIZON
FROM
PART
,INVD
WHERE
PART.PART_NBR = INVD.PART_NBR (+) AND
PART.PART_TYPE = 'P'
UNION ALL
SELECT
PART.PART_NBR AS PART_NBR
,PART.PART_DESC AS PART_DESC
,PART.PART_TYPE AS PART_TYPE
,NULL AS ORD_STAT
,NULL AS ORD_TYPE
,RQMT.RQMT_CODE AS RQMT_CODE
,TO_NUMBER(NULL) AS QTY_ON_HAND
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD < SYSDATE
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_OVERDUE
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD >= SYSDATE AND RQMT.DATE_REQD <= (SYSDATE + 20)
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_0TO20DAYS
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD >= (SYSDATE + 21) AND RQMT.DATE_REQD <= (SYSDATE + 40)
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_21TO40DAYS
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD >= (SYSDATE + 41) AND RQMT.DATE_REQD <= (SYSDATE + 60)
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_41TO60DAYS
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD >= (SYSDATE + 61) AND RQMT.DATE_REQD <= (SYSDATE + 90)
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_61TO90DAYS
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD >= (SYSDATE + 91) AND RQMT.DATE_REQD <= (SYSDATE + 180)
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_91TO180DAYS
,TO_NUMBER(CASE WHEN
RQMT.DATE_REQD >= (SYSDATE + 181)
THEN (RQMT.QTY_REQD - RQMT.QTY_ISSUED)
ELSE 0
END) AS DEMAND_BEYOND
,TO_NUMBER(NULL) AS ORDERS_OVERDUE
,TO_NUMBER(NULL) AS ORDERS_0TO20DAYS
,TO_NUMBER(NULL) AS ORDERS_21TO40DAYS
,TO_NUMBER(NULL) AS ORDERS_41TO60DAYS
,TO_NUMBER(NULL) AS ORDERS_61TO90DAYS
,TO_NUMBER(NULL) AS ORDERS_91TO180DAYS
,TO_NUMBER(NULL) AS ORDERS_BEYOND
,TO_DATE(NULL) AS PLAN_RECV_DATE
,RQMT.DATE_REQD AS DATE_REQD
,RQMT.QTY_REQD AS QTY_REQD
,RQMT.QTY_ISSUED AS OTY_ISSUED
,(RQMT.QTY_REQD - RQMT.QTY_ISSUED) AS DEMAND_DUE
,NULL AS ORD_NBR
,NULL AS LINE_NBR
,NULL AS STATUS
,TO_NUMBER(NULL) AS AVAIL_INVENTORY
,TO_NUMBER(NULL) AS LINE_INVENTORY
,TO_NUMBER(NULL) AS SCRAP_INVENTORY
,TO_NUMBER(NULL) AS RECV_INVENTORY
,TO_NUMBER(NULL) AS DROP_INVENTORY
,TO_NUMBER(NULL) AS QTY_ON_ORD
,TO_NUMBER(NULL) AS QTY_RECVD
,NULL AS HORIZON
FROM
PART
,RQMT
WHERE
PART.PART_NBR = RQMT.COMP_PART_NBR AND
PART.PART_TYPE = 'P' AND
(RQMT.RQMT_CODE <> 'CL' AND
RQMT.RQMT_CODE <> 'IS')
UNION ALL
SELECT
PART.PART_NBR AS PART_NBR
,PART.PART_DESC AS PART_DESC
,PART.PART_TYPE AS PART_TYPE
,OORD.ORD_STAT AS ORD_STAT
,OORD.ORD_TYPE AS ORD_TYPE
,NULL AS RQMT_CODE
,TO_NUMBER(NULL) AS QTY_ON_HAND
,TO_NUMBER(NULL) AS DEMAND_OVERDUE
,TO_NUMBER(NULL) AS DEMAND_0TO20DAYS
,TO_NUMBER(NULL) AS DEMAND_21TO40DAYS
,TO_NUMBER(NULL) AS DEMAND_41TO60DAYS
,TO_NUMBER(NULL) AS DEMAND_61TO90DAYS
,TO_NUMBER(NULL) AS DEMAND_91TO180DAYS
,TO_NUMBER(NULL) AS DEMAND_BEYOND
,CASE WHEN
OORD.PLAN_RECV_DATE < SYSDATE
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0 END AS ORDERS_OVERDUE
,CASE WHEN
OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE <= (SYSDATE + 20)
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0 END AS ORDERS_0TO20DAYS
,CASE WHEN
OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 21) AND
OORD.PLAN_RECV_DATE <= (SYSDATE + 40)
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0 END AS ORDERS_21TO40DAYS
,CASE WHEN
OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 41) AND
OORD.PLAN_RECV_DATE <= (SYSDATE + 60)
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0
END AS ORDERS_41TO60DAYS
,CASE WHEN
OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 61) AND
OORD.PLAN_RECV_DATE <= (SYSDATE + 90)
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0
END AS ORDERS_61TO90DAYS
,CASE WHEN
OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 91) AND
OORD.PLAN_RECV_DATE <= (SYSDATE + 180)
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0
END AS ORDERS_91TO180DAYS
,CASE WHEN
OORD.PLAN_RECV_DATE >= SYSDATE AND OORD.PLAN_RECV_DATE >= (SYSDATE + 181)
THEN (OORD.QTY_ON_ORD - OORD.QTY_RECVD)
ELSE 0
END AS ORDERS_BEYOND
,OORD.PLAN_RECV_DATE AS PLAN_RECV_DATE
,TO_DATE(NULL) AS DATE_REQD
,TO_NUMBER(NULL) AS QTY_REQD
,TO_NUMBER(NULL) AS OTY_ISSUED
,TO_NUMBER(NULL) AS DEMAND_DUE
,OORD.ORD_NBR AS ORD_NBR
,OORD.SUB_ORD_NBR AS LINE_NBR
,OORD.ORD_STAT AS STATUS
,TO_NUMBER(NULL) AS AVAIL_INVENTORY
,TO_NUMBER(NULL) AS LINE_INVENTORY
,TO_NUMBER(NULL) AS SCRAP_INVENTORY
,TO_NUMBER(NULL) AS RECV_INVENTORY
,TO_NUMBER(NULL) AS DROP_INVENTORY
,OORD.QTY_ON_ORD AS QTY_ON_ORD
,OORD.QTY_RECVD AS QTY_RECVD
,CASE WHEN PLAN_RECV_DATE < SYSDATE THEN 'OVERDUE'
WHEN PLAN_RECV_DATE >= SYSDATE AND PLAN_RECV_DATE <= (SYSDATE + 20) THEN '0 To 20 Days'
WHEN PLAN_RECV_DATE >= (SYSDATE + 21) AND PLAN_RECV_DATE <= (SYSDATE + 40) THEN '21 To 40 Days'
WHEN PLAN_RECV_DATE >= (SYSDATE + 41) AND PLAN_RECV_DATE <= (SYSDATE + 60) THEN '41 To 60 Days'
WHEN PLAN_RECV_DATE >= (SYSDATE + 61) AND PLAN_RECV_DATE <= (SYSDATE + 90) THEN '61 To 90 Days'
WHEN PLAN_RECV_DATE >= (SYSDATE + 91) AND PLAN_RECV_DATE <= (SYSDATE + 180) THEN '91 To 180 Days'
ELSE 'BEYOND' END AS HORIZON
FROM
PART
,OORD
WHERE
PART.PART_NBR = OORD.PART_NBR (+) AND
PART.PART_TYPE = 'P' AND
OORD.ORD_TYPE = 'PO' AND
(OORD.ORD_STAT <> '--' AND
OORD.ORD_STAT <> 'OR' AND
OORD.ORD_STAT <> 'FP' AND
OORD.ORD_STAT <> 'PL' AND
OORD.ORD_STAT <> 'CL' )
)
ORDER BY 1 ,25,28,29
Any suggestions will be appreciated.
Thanks in advance!!!!
Kerrie
February 16, 2006 at 4:41 pm
OK, i didn't read through all of the code, but... it was long.
With what you'retrtying to do, are you getting a result set like this ->
Part123 22 23 99 ...
Part123 0 11 01 ...
...
ie you're getting multiple lines per part number but the figures per column are correct? If so, is there a reason why you could't make your outer query an aggregation one, so rather than using select * you'll have to explicity identify each field and use a SUM on it, then group by Part# (and whatever else isn't aggregated)?
Steve.
February 17, 2006 at 5:49 am
Thanks for replying!!!!!!
I finally figured it out last night. I had to group by one of the select statements (INVD Table) and do complex where statements on the INVD and OORD selects'. For example...
FROM
INVD
WHERE
INVD.PART_NBR IN (SELECT
PART.PART_NBR AS PART_NBR
FROM
PART
,RQMT
WHERE
PART.PART_NBR = RQMT.COMP_PART_NBR AND
PART.PART_TYPE = 'P' AND
(RQMT.RQMT_CODE <> 'CL' AND
RQMT.RQMT_CODE <> 'IS'))
I only wanted the information coming out of the RQMT table (requirements).
Thanks for your help, I do appreciate it.
Kerrie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply