troubleshooting Question

Need to Add 1 extra Join to SQl Statement

Avatar of pclarke7
pclarke7 asked on
12 Comments1 Solution76 ViewsLast Modified:
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 ?

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

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(sysdate) as "Due in days",
CASE WHEN to_date(1900000 + wadrqj,'YYYYDDD')-trunc(sysdate)   <=7 THEN 'Y' ELSE 'N' END  "Due in the next week",
CASE WHEN to_date(1900000 + wadrqj,'YYYYDDD')-trunc(sysdate)  <=14 THEN 'Y' ELSE 'N' END  "Due in the next fortnight",
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','8O2','4P6','SWR','8J1','1O9','2F6','2F8','2C1','8O9','2C3','XPC','2R1','2C2','LBI','2D1','2J0','CPS','2F4','2F7','2A4','X99','2R3','2F5') Then 'GAL' ELSE 'SHN' END "Site",
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

          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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros