asked on
Need to Add 1 extra Join to SQl Statement
The following SQL statement gives me exactly what I want except I need just the Highest Sequence from File F31122 (Bolded values) So I need to read just the record with the highest WTOPSQ (9400) and its description PrimaryH. Rather than 17 records I would like to see 1 record as follows. Any idea how to add this extra join to complete this ?
regards
Pat
Desired result
94000 PrimaryH 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
SELECT
wlopsq as "Sequence",
wlmcu as "Wrk Ctr",
wadoco as "Order #",
walitm as "Item #",
syuorg/100 as "Qty started",
wauorg/100 as "Cur Qty Open",
to_char(to_date((watrdj+ 1900000),'yyyyddd')) as "W/O Create Date",
to_char(to_date((sydrqj+ 1900000),'yyyyddd')) as "Orig Requested Date (OTIF)",
to_char(to_date((wadrqj+ 1900000),'yyyyddd')) as "Cur Requested Date",
to_char(to_date((systrt+ 1900000),'yyyyddd')) as "Orig Plan Start Date (30)",
to_char(to_date((wastrt+ 1900000),'yyyyddd')) as "Cur Plan Start Date",
wawr10 as "Cat Code",
CASE WHEN systrt=wastrt THEN 'Y' ELSE 'N' END as "Schedule adhered to Y/N",
CASE WHEN sydrqj <wadrqj THEN 'Y' ELSE 'N' END "Past Due Y/N",
to_date(1900000 + wadrqj,'YYYYDDD')-trunc(sy
CASE WHEN to_date(1900000 + wadrqj,'YYYYDDD')-trunc(sy
CASE WHEN to_date(1900000 + wadrqj,'YYYYDDD')-trunc(sy
wasrst as "Current Status",
ibanpl ||'- '|| abalph as "Planner #",
ibprp4 as "Master Planning family",
drdl01 as "Family Name",
CASE WHEN ibprp4 in('2F9','8O8','2G3','8J7'
wammcu as "Bus Unit",
syupmj as "Transacted Date",
sytday as "Transacted Time"
FROM (SELECT t1.*, t2.*
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t2.syupmj) rn
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t2
ON t1.wadoco = t2.sydoco and sysrst='30') t3
LEFT OUTER JOIN JDESTAGE.F4102_wh ON iblitm=walitm and Ibmcu like '%4IM%'
LEFT OUTER JOIN JDESTAGE.F0005_wh ON drsy='41' and drrt='P4' and trim(drky)=trim(ibprp4)
LEFT OUTER JOIN JDESTAGE.F0101_wh ON aban8=ibanpl
LEFT OUTER JOIN JDESTAGE.F3112_wh on wldoco=wadoco
WHERE rn = 1 and wasrst <'90' and wammcu like '%4IM%' and wadoco=63963405;
Sequence Wrk Ctr Order # Item # Qty started Cur Qty Open W/O Create Date Orig Requested Date (OTIF) Cur Requested Date Orig Plan Start Date (30)
4000 Primary1 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
4500 Primary2 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
5500 Primary3 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
7000 Primary4 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
8500 Primary5 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
9100 Primary6 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
9500 Primary7 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
10000 Primary8 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
11000 Primary9 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
12400 PrimaryA 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
12500 PrimaryB 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
32000 PrimaryC 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
90000 PrimaryD 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
92000 PrimaryE 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
92500 PrimaryF 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
93000 PrimaryG 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
94000 PrimaryH 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
regards
Pat
Desired result
94000 PrimaryH 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
SELECT
wlopsq as "Sequence",
wlmcu as "Wrk Ctr",
wadoco as "Order #",
walitm as "Item #",
syuorg/100 as "Qty started",
wauorg/100 as "Cur Qty Open",
to_char(to_date((watrdj+ 1900000),'yyyyddd')) as "W/O Create Date",
to_char(to_date((sydrqj+ 1900000),'yyyyddd')) as "Orig Requested Date (OTIF)",
to_char(to_date((wadrqj+ 1900000),'yyyyddd')) as "Cur Requested Date",
to_char(to_date((systrt+ 1900000),'yyyyddd')) as "Orig Plan Start Date (30)",
to_char(to_date((wastrt+ 1900000),'yyyyddd')) as "Cur Plan Start Date",
wawr10 as "Cat Code",
CASE WHEN systrt=wastrt THEN 'Y' ELSE 'N' END as "Schedule adhered to Y/N",
CASE WHEN sydrqj <wadrqj THEN 'Y' ELSE 'N' END "Past Due Y/N",
to_date(1900000 + wadrqj,'YYYYDDD')-trunc(sy
CASE WHEN to_date(1900000 + wadrqj,'YYYYDDD')-trunc(sy
CASE WHEN to_date(1900000 + wadrqj,'YYYYDDD')-trunc(sy
wasrst as "Current Status",
ibanpl ||'- '|| abalph as "Planner #",
ibprp4 as "Master Planning family",
drdl01 as "Family Name",
CASE WHEN ibprp4 in('2F9','8O8','2G3','8J7'
wammcu as "Bus Unit",
syupmj as "Transacted Date",
sytday as "Transacted Time"
FROM (SELECT t1.*, t2.*
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t2.syupmj) rn
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t2
ON t1.wadoco = t2.sydoco and sysrst='30') t3
LEFT OUTER JOIN JDESTAGE.F4102_wh ON iblitm=walitm and Ibmcu like '%4IM%'
LEFT OUTER JOIN JDESTAGE.F0005_wh ON drsy='41' and drrt='P4' and trim(drky)=trim(ibprp4)
LEFT OUTER JOIN JDESTAGE.F0101_wh ON aban8=ibanpl
LEFT OUTER JOIN JDESTAGE.F3112_wh on wldoco=wadoco
WHERE rn = 1 and wasrst <'90' and wammcu like '%4IM%' and wadoco=63963405;
Sequence Wrk Ctr Order # Item # Qty started Cur Qty Open W/O Create Date Orig Requested Date (OTIF) Cur Requested Date Orig Plan Start Date (30)
4000 Primary1 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
4500 Primary2 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
5500 Primary3 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
7000 Primary4 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
8500 Primary5 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
9100 Primary6 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
9500 Primary7 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
10000 Primary8 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
11000 Primary9 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
12400 PrimaryA 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
12500 PrimaryB 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
32000 PrimaryC 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
90000 PrimaryD 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
92000 PrimaryE 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
92500 PrimaryF 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
93000 PrimaryG 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
94000 PrimaryH 63963405 4.25E+10 24 24 14-Feb-18 18-Feb-19 18-Feb-19 5-Feb-19 5-Feb-19 S10 Y N 4 Y Y 40 273087- KENNEDY
SQL
Last Comment
pclarke7