OK code experts....

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
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1Hi,
"UNION ALL" will include all records ie even duplicate records. Try putting
"Union"
It will eliminate duplicate records.
Regards
Amarnath
"Ksorrell via SQLMonster.com" wrote:
> 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
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1
>|||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
Amarnath wrote:
>Hi,
>"UNION ALL" will include all records ie even duplicate records. Try putting
>"Union"
>It will eliminate duplicate records.
>Regards
>Amarnath
>> Hi all,
>[quoted text clipped - 253 lines]
>> Kerrie
--
Message posted via http://www.sqlmonster.com