pclarke7
asked on
Need to add additional SQL sub query to existing SQL query
Hello,
the follow SQL statement works perfectly. However I now need to add some new logic which I am struggling to do.
Here is the current output:
I now need to add a new column t3.systrt as "Original planned Date". The logic that I need to add is shown highlighted below:
The above give me an error of missing right parenthesis. The 2nd sub query that I am trying to insert is looking to return a different record from the same file that the 1st sub query is processing. Is this possible and if so how should I structure the 2nd sub query?
regards
Pat
the follow SQL statement works perfectly. However I now need to add some new logic which I am struggling to do.
SELECT
"Wrk Ctr",
"Op Seq",
"Order #",
"Item #",
"Qty started",
"Cur Qty Open",
"W/O Create Date",
"Cur Requested Date",
"Planned Start Date",
"Actual Start Date (40)",
"Actual Start Time (40)",
"WO Seq",
"Schedule Variance",
"Schedule adhered to Y/N",
"Cat Code",
"Current Status",
"Planner #",
"Master Planning family",
"Family Name",
"Site",
"Bus Unit"
from (
SELECT
wlmcu as "Wrk Ctr",
wlopsq as "Op Seq",
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((wadrqj+ 1900000),'yyyyddd')) as "Cur Requested Date",
to_char(to_date((t2.syupmj+ 1900000),'yyyyddd')) as "Actual Start Date (40)",
to_char ( to_date ( LPAD(SYTDAY,6,0), 'HH24MISS'), 'HH:MI:SS AM') as "Actual Start Time (40)",
to_char(to_date((t2.systrt+ 1900000),'yyyyddd')) as "Planned Start Date",
t2.syupmj-systrt as "Schedule Variance",
CASE WHEN systrt=syupmj or syupmj=0 and TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYDDD'))-1900000<= wastrt THEN 'Y' ELSE 'N' END as "Schedule adhered to Y/N",
wawr10 as "Cat Code",
waresc as "WO Seq", TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYDDD'))-1900000 ,
wasrst as "Current Status",
ibanpl ||'- '|| abalph as "Planner #",
ibprp4 || '-'|| drdl01 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",
row_number() over(partition by wldoco order by wlopsq desc) new_rn
FROM (SELECT t1.*, t2.*
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t2.syupmj,t2.sytday) rn
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t2
ON t1.wadoco = t2.sydoco and t2.sysrst='40') t2
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 >='40' and wasrst<'90' and wammcu like '%4IM%' order by syupmj,sytday
)
where new_rn=1Here is the current output:
I now need to add a new column t3.systrt as "Original planned Date". The logic that I need to add is shown highlighted below:
SELECT
"Wrk Ctr",
"Op Seq",
"Order #",
"Item #",
"Qty started",
"Cur Qty Open",
"W/O Create Date",
"Cur Requested Date",
"Planned Start Date",
"Actual Start Date (40)",
"Actual Start Time (40)",
"WO Seq",
"Schedule Variance",
"Schedule adhered to Y/N",
"Cat Code",
"Current Status",
"Planner #",
"Master Planning family",
"Family Name",
"Site",
"Bus Unit"
from (
SELECT
wlmcu as "Wrk Ctr",
wlopsq as "Op Seq",
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((wadrqj+ 1900000),'yyyyddd')) as "Cur Requested Date",
to_char(to_date((t2.syupmj+ 1900000),'yyyyddd')) as "Actual Start Date (40)",
to_char ( to_date ( LPAD(SYTDAY,6,0), 'HH24MISS'), 'HH:MI:SS AM') as "Actual Start Time (40)",
to_char(to_date((t2.systrt+ 1900000),'yyyyddd')) as "Planned Start Date",
t2.syupmj-systrt as "Schedule Variance",
CASE WHEN systrt=syupmj or syupmj=0 and TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYDDD'))-1900000<= wastrt THEN 'Y' ELSE 'N' END as "Schedule adhered to Y/N",
wawr10 as "Cat Code",
waresc as "WO Seq", TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYDDD'))-1900000 ,
wasrst as "Current Status",
ibanpl ||'- '|| abalph as "Planner #",
ibprp4 || '-'|| drdl01 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",
row_number() over(partition by wldoco order by wlopsq desc) new_rn
FROM (SELECT t1.*, t2.*
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t2.syupmj,t2.sytday) rn
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t2
ON t1.wadoco = t2.sydoco and t2.sysrst='40') t2
***NEW LOGIC START***
FROM (SELECT t1.*, t3.*
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t3.syupmj,t3.sytday) rn2
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t3
ON t1.wadoco = t3.sydoco and t3.sysrst='20') t3
***NEW LOGIC END***
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 >='40' and wasrst<'90' and wammcu like '%4IM%' order by syupmj,sytday
)
where new_rn=1
***NEW LOGIC START***
and rn2=1
***NEW LOGIC END***The above give me an error of missing right parenthesis. The 2nd sub query that I am trying to insert is looking to return a different record from the same file that the 1st sub query is processing. Is this possible and if so how should I structure the 2nd sub query?
regards
Pat
ASKER
Thanks for your comments. I have changed the Query as shown below but now I am getting an error "ORA-00918 column ambiguously defined". I have ensured that every reference to t2 and t3 fields are prefixed by t2. or t3. but I'm still getting error. Any idea what it is referring to ?
regards
Pat
regards
Pat
SELECT
"Wrk Ctr",
"Op Seq",
"Order #",
"Item #",
"Qty started",
"Cur Qty Open",
"W/O Create Date",
"Cur Requested Date",
"Planned Start Date",
"Actual Start Date (40)",
"Actual Start Time (40)",
"WO Seq",
"Schedule Variance",
"Schedule adhered to Y/N",
"Cat Code",
"Current Status",
"Planner #",
"Master Planning family",
"Family Name",
"Site",
"Bus Unit"
from (
SELECT
wlmcu as "Wrk Ctr",
wlopsq as "Op Seq",
wadoco as "Order #",
walitm as "Item #",
t2.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((wadrqj+ 1900000),'yyyyddd')) as "Cur Requested Date",
to_char(to_date((t2.syupmj+ 1900000),'yyyyddd')) as "Actual Start Date (40)",
to_char ( to_date ( LPAD(T2.SYTDAY,6,0), 'HH24MISS'), 'HH:MI:SS AM') as "Actual Start Time (40)",
to_char(to_date((t2.systrt+ 1900000),'yyyyddd')) as "Planned Start Date",
t2.syupmj-t2.systrt as "Schedule Variance",
CASE WHEN t2.systrt=t2.syupmj or t2.syupmj=0 and TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYDDD'))-1900000<= wastrt THEN 'Y' ELSE 'N' END as "Schedule adhered to Y/N",
wawr10 as "Cat Code",
waresc as "WO Seq", TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYDDD'))-1900000 ,
wasrst as "Current Status",
ibanpl ||'- '|| abalph as "Planner #",
ibprp4 || '-'|| drdl01 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",
row_number() over(partition by wldoco order by wlopsq desc) new_rn
FROM (SELECT t1.*, t2.*
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t2.syupmj,t2.sytday) rn
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t2
ON t1.wadoco = t2.sydoco and t2.sysrst='40') t2
LEFT OUTER JOIN (SELECT t1.*, t3.*
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t3.syupmj,t3.sytday) rn2
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t3
ON t1.wadoco = t3.sydoco and t3.sysrst='20') t3 ON t2.wadoco = t3.wadoco
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 >='40' and wasrst<'90' and wammcu like '%4IM%' and wadoco=64337180 order by t2.syupmj,t2.sytday
)
where new_rn=1 
First: I would only select the columns you need and get rid of the ".*".
For the ambiguous columns, when the WHERE clause. For example does wasrst exist in both of the inline views?
I would seriously look at combining the new subquery with the one already there and pull back the two row_number's in the same query.
For the ambiguous columns, when the WHERE clause. For example does wasrst exist in both of the inline views?
I would seriously look at combining the new subquery with the one already there and pull back the two row_number's in the same query.
I'm also thinking you can merge the new subquery into the original and get both row_numbers in the same query or better, remove the subqueries all together and do normal joins.
ASKER
File F4801Z1 is the file where we have 2 views and all of the fields in this file begin with SY.
Changes T2.* and T3.* to relevant field names but still getting same error ORA-00918 column ambiguously defined
Changes T2.* and T3.* to relevant field names but still getting same error ORA-00918 column ambiguously defined
SELECT
"Wrk Ctr",
"Op Seq",
"Order #",
"Item #",
"Qty started",
"Cur Qty Open",
"W/O Create Date",
"Cur Requested Date",
"Planned Start Date",
"Actual Start Date (40)",
"Actual Start Time (40)",
"WO Seq",
"Schedule Variance",
"Schedule adhered to Y/N",
"Cat Code",
"Current Status",
"Planner #",
"Master Planning family",
"Family Name",
"Site",
"Bus Unit"
from (
SELECT
wlmcu as "Wrk Ctr",
wlopsq as "Op Seq",
wadoco as "Order #",
walitm as "Item #",
t2.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((wadrqj+ 1900000),'yyyyddd')) as "Cur Requested Date",
to_char(to_date((t2.syupmj+ 1900000),'yyyyddd')) as "Actual Start Date (40)",
to_char ( to_date ( LPAD(T2.SYTDAY,6,0), 'HH24MISS'), 'HH:MI:SS AM') as "Actual Start Time (40)",
to_char(to_date((t2.systrt+ 1900000),'yyyyddd')) as "Planned Start Date",
t2.syupmj-t2.systrt as "Schedule Variance",
CASE WHEN t2.systrt=t2.syupmj or t2.syupmj=0 and TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYDDD'))-1900000<= wastrt THEN 'Y' ELSE 'N' END as "Schedule adhered to Y/N",
wawr10 as "Cat Code",
waresc as "WO Seq", TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYDDD'))-1900000 ,
wasrst as "Current Status",
ibanpl ||'- '|| abalph as "Planner #",
ibprp4 || '-'|| drdl01 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",
row_number() over(partition by wldoco order by wlopsq desc) new_rn
FROM (SELECT t1.*, t2.syupmj,t2.sytday,t2.sysrst,t2.sydoco
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t2.syupmj,t2.sytday) rn
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t2
ON t1.wadoco = t2.sydoco and t2.sysrst='40') t2
LEFT OUTER JOIN (SELECT t1.*, t3.syupmj,t3.sytday,t3.sysrst,t3.sydoco
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t3.syupmj,t3.sytday) rn2
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t3
ON t1.wadoco = t3.sydoco and t3.sysrst='20') t3 ON t2.wadoco = t3.wadoco
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 >='40' and wasrst<'90' and wammcu like '%4IM%' and wadoco=64337180 order by t2.syupmj,t2.sytday
)
where new_rn=1 and rn2=1
You still have t1.* in subqueries t2 and t3. They have the same names
>>Changes T2.* and T3.* to relevant field names but still getting same error ORA-00918 column ambiguously defined
Did you also look in the WHERE clause as I suggested?
See if and of the columns here exist in both subqueries:
WHERE rn = 1 and wasrst >='40' and wasrst<'90' and wammcu like '%4IM%' and wadoco=64337180 order by t2.syupmj,t2.sytday
Did you also look in the WHERE clause as I suggested?
See if and of the columns here exist in both subqueries:
WHERE rn = 1 and wasrst >='40' and wasrst<'90' and wammcu like '%4IM%' and wadoco=64337180 order by t2.syupmj,t2.sytday
ASKER
Now Changed t1* to t1 & t4 but getting error ORA-00904 "T4"."WADOCO" : invalid identifier
SELECT
"Wrk Ctr",
"Op Seq",
"Order #",
"Item #",
"Qty started",
"Cur Qty Open",
"W/O Create Date",
"Cur Requested Date",
"Planned Start Date",
"Actual Start Date (40)",
"Actual Start Time (40)",
"WO Seq",
"Schedule Variance",
"Schedule adhered to Y/N",
"Cat Code",
"Current Status",
"Planner #",
"Master Planning family",
"Family Name",
"Site",
"Bus Unit"
from (
SELECT
wlmcu as "Wrk Ctr",
wlopsq as "Op Seq",
t1.wadoco as "Order #",
t1.walitm as "Item #",
t2.syuorg/100 as "Qty started",
t1.wauorg/100 as "Cur Qty Open",
to_char(to_date((t1.watrdj+ 1900000),'yyyyddd')) as "W/O Create Date",
to_char(to_date((t1.wadrqj+ 1900000),'yyyyddd')) as "Cur Requested Date",
to_char(to_date((t2.syupmj+ 1900000),'yyyyddd')) as "Actual Start Date (40)",
to_char ( to_date ( LPAD(T2.SYTDAY,6,0), 'HH24MISS'), 'HH:MI:SS AM') as "Actual Start Time (40)",
to_char(to_date((t2.systrt+ 1900000),'yyyyddd')) as "Planned Start Date",
t2.syupmj-t2.systrt as "Schedule Variance",
CASE WHEN t2.systrt=t2.syupmj or t2.syupmj=0 and TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYDDD'))-1900000<= t1.wastrt THEN 'Y' ELSE 'N' END as "Schedule adhered to Y/N",
t1.wawr10 as "Cat Code",
t1.waresc as "WO Seq", TO_NUMBER(TO_CHAR(CURRENT_DATE, 'YYYYDDD'))-1900000 ,
t1.wasrst as "Current Status",
ibanpl ||'- '|| abalph as "Planner #",
ibprp4 || '-'|| drdl01 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",
t1.wammcu as "Bus Unit",
row_number() over(partition by wldoco order by wlopsq desc) new_rn
FROM (SELECT t1.wadoco,t1.walitm,t1.wauorg,t1.watrdj,t1.wastrt,t1.wawr10,t1.waresc,t1.wasrst,t1.wammcu, t2.syupmj,t2.sytday,t2.sysrst,t2.sydoco
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t2.syupmj,t2.sytday) rn
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t2
ON t1.wadoco = t2.sydoco and t2.sysrst='40') t2
LEFT OUTER JOIN (SELECT t4.wadoco,t4.walitm,t4.wauorg,t4.watrdj,t4.wastrt,t4.wawr10,t4.waresc,t4.wasrst,t4.wammcu, t3.syupmj,t3.sytday,t3.sysrst,t3.sydoco
,ROW_NUMBER() OVER (PARTITION BY t4.wadoco ORDER BY t3.syupmj,t3.sytday) rn2
FROM JDESTAGE.F4801_wh t4
LEFT JOIN JDESTAGE.F4801Z1_wh t3
ON t4.wadoco = t3.sydoco and t3.sysrst='20') t3 ON t3.sydoco = t4.wadoco
LEFT OUTER JOIN JDESTAGE.F4102_wh ON iblitm=t1.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=t1.wadoco
WHERE rn = 1 and t1.wasrst >='40' and t1.wasrst<'90' and t1.wammcu like '%4IM%' and t1.wadoco=64337180 order by t2.syupmj,t2.sytday
)
where new_rn=1 and rn2=1
t4 is defined in subquery, but you refer to in main query.
I think instead of
t3 ON t3.sydoco = t4.wadoco
should be
t3 ON t3.sydoco = t1.wadoco
I think instead of
t3 ON t3.sydoco = t4.wadoco
should be
t3 ON t3.sydoco = t1.wadoco
ASKER
swapping t4.wadodo for t1.wadoco just results in the same error on the t1 field ORA-00904 "T1"."WADOCO"
sorry. I mean you should join subquery t2 and subquery t3. Which key fields should be used for join there?
ASKER
Sorry, I don't fully understand what you mean by joining sub query t2 & t3 ? Both sub queries will return values from different records on F4801Z1 file.
You have a scope issue. You cannot reference an alias from a subquery in the outer query.
To simplify the example:
select t1.col1 from (select 'Hello' col1 from dual t1);
ERROR at line 1:
ORA-00904: "T1"."COL1": invalid identifier
You alias t1 in the sub query. You cannot access that alias outside that query.
To simplify the example:
select t1.col1 from (select 'Hello' col1 from dual t1);
ERROR at line 1:
ORA-00904: "T1"."COL1": invalid identifier
You alias t1 in the sub query. You cannot access that alias outside that query.
You are trying to join two subqueries:
(SELECT t1.wadoco,t1.walitm,t1.wau
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t2.syupmj,t2.sytday) rn
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t2
ON t1.wadoco = t2.sydoco and t2.sysrst='40') t2
LEFT OUTER JOIN
(SELECT t4.wadoco,t4.walitm,t4.wau
,ROW_NUMBER() OVER (PARTITION BY t4.wadoco ORDER BY t3.syupmj,t3.sytday) rn2
FROM JDESTAGE.F4801_wh t4
LEFT JOIN JDESTAGE.F4801Z1_wh t3
ON t4.wadoco = t3.sydoco and t3.sysrst='20') t3
using key values from these subqueries:
ON t3.keyvalue = t4.keyvalue
substitute correct name of key fields instead of keyvalue above.
(SELECT t1.wadoco,t1.walitm,t1.wau
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t2.syupmj,t2.sytday) rn
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t2
ON t1.wadoco = t2.sydoco and t2.sysrst='40') t2
LEFT OUTER JOIN
(SELECT t4.wadoco,t4.walitm,t4.wau
,ROW_NUMBER() OVER (PARTITION BY t4.wadoco ORDER BY t3.syupmj,t3.sytday) rn2
FROM JDESTAGE.F4801_wh t4
LEFT JOIN JDESTAGE.F4801Z1_wh t3
ON t4.wadoco = t3.sydoco and t3.sysrst='20') t3
using key values from these subqueries:
ON t3.keyvalue = t4.keyvalue
substitute correct name of key fields instead of keyvalue above.
ASKER
My intention is not to join the 2 sub queries. My intention is to have the two sub queries to run off the main query. Subquery 1 returns a number of key fields and sub-query 2 returns additional field. If subquery 1 failed to return any records then sub query 2 can still return values
It won't work this way. subquery t2 is main. Data from all other queries will appear only if t2 returns rows. Otherwise you have empty recordset.
Additional field from subquery t3 could be selected only with join to records returned by subquery t2.
You could make union with a query similar to original one, but based on subquery t3.
Additional field from subquery t3 could be selected only with join to records returned by subquery t2.
You could make union with a query similar to original one, but based on subquery t3.
ASKER
I think I need to go back to the basics. Below I have a simple example of two files F4801_wh & F4801Z1_wh which are joined by wadoco & sydoco. Can you suggest the best way to format the query so that I get an output like Example A below?
regards
Pat
F4801_wh
wadoco wasrst wauorg
64337180 89 24
F4801Z1_wh
sydoco sysrst systrt syupmj
64337180 20 119018 119001
64337180 20 119019 119002
64337180 20 119021 119003
64337180 30 119022 119005
64337180 40 119023 119025
64337180 40 119023 119026
64337180 40 119023 119027
64337180 40 119023 119028
64337180 90 119024 119030
The “Planned Start date (20)” is systrt from the 1st instance of sysrst equal to ‘20’ (sequenced by syupmj (Julian date))
The “Actual start date (40)” is syupmj from the 1st instance of sysrst equal to ‘40’ (sequenced by syupmj (Julian date))
SELECT
wadoco as "Order #",
wauorg as "Cur Open Qty",
systrt as "Planned Start Date (20)",
syupmj as "Actual Start Date (40)"
from JDESTAGE.F4801_wh, JDESTAGE.F4801Z1_wh
where wasrst>’40’ and wasrst<’90’ and wadoco=64337180 and
sydoco=wadoco
When I run the above query I will get the following: (Example A)
Order # Cur Open Qty Planned Start Date(20) Actual Start Date (40)
6433718 24 119018 119001
6433718 24 119019 119002
6433718 24 119021 119003
6433718 24 119022 119004
6433718 24 119023 119025
6433718 24 119023 119026
6433718 24 119023 119027
6433718 24 119023 119028
6433718 24 119024 119030
What I require is (Example B)
Order # Cur Open Qty Planned Start Date(20) Actual Start Date (40)
6433718 24 119018 119025
This will require two joins to F4801Z1_wh file returning two separate values. The 1st join should return the systrt value of the 1st ‘20’ record and the 2nd join should return the syupmj value of the 1st ‘40’ record. Syupmj determines the sequence of values on F4801Z1_wh
regards
Pat
F4801_wh
wadoco wasrst wauorg
64337180 89 24
F4801Z1_wh
sydoco sysrst systrt syupmj
64337180 20 119018 119001
64337180 20 119019 119002
64337180 20 119021 119003
64337180 30 119022 119005
64337180 40 119023 119025
64337180 40 119023 119026
64337180 40 119023 119027
64337180 40 119023 119028
64337180 90 119024 119030
The “Planned Start date (20)” is systrt from the 1st instance of sysrst equal to ‘20’ (sequenced by syupmj (Julian date))
The “Actual start date (40)” is syupmj from the 1st instance of sysrst equal to ‘40’ (sequenced by syupmj (Julian date))
SELECT
wadoco as "Order #",
wauorg as "Cur Open Qty",
systrt as "Planned Start Date (20)",
syupmj as "Actual Start Date (40)"
from JDESTAGE.F4801_wh, JDESTAGE.F4801Z1_wh
where wasrst>’40’ and wasrst<’90’ and wadoco=64337180 and
sydoco=wadoco
When I run the above query I will get the following: (Example A)
Order # Cur Open Qty Planned Start Date(20) Actual Start Date (40)
6433718 24 119018 119001
6433718 24 119019 119002
6433718 24 119021 119003
6433718 24 119022 119004
6433718 24 119023 119025
6433718 24 119023 119026
6433718 24 119023 119027
6433718 24 119023 119028
6433718 24 119024 119030
What I require is (Example B)
Order # Cur Open Qty Planned Start Date(20) Actual Start Date (40)
6433718 24 119018 119025
This will require two joins to F4801Z1_wh file returning two separate values. The 1st join should return the systrt value of the 1st ‘20’ record and the 2nd join should return the syupmj value of the 1st ‘40’ record. Syupmj determines the sequence of values on F4801Z1_wh
try this:
SELECT
wadoco as "Order #",
wauorg as "Cur Open Qty",
min(details_table1.systrt) as "Planned Start Date (20)",
min(details_table2.syupmj) as "Actual Start Date (40)"
from JDESTAGE.F4801_wh main_table
left outer join JDESTAGE.F4801Z1_wh details_table1 on main_table.wadoco = details_table1.sydoco and details_table1.sysrst = '20'
left outer join JDESTAGE.F4801Z1_wh details_table2 on main_table.wadoco = details_table2.sydoco and details_table2.sysrst = '40'
where wasrst>’40’ and wasrst<’90’ and wadoco=64337180
group by wadoco, wauorg
No need to hit the table more than once.
Given the sample data in the last post, see if this helps:
Given the sample data in the last post, see if this helps:
SELECT
wadoco as "Order #",
wauorg as "Cur Open Qty",
min(case when sysrst=20 then systrt end) as "Planned Start Date (20)",
min(case when sysrst=40 then syupmj end) as "Actual Start Date (40)"
from myF4801_wh join myF4801Z1_wh on sydoco=wadoco
where wasrst>'40' and wasrst<'90' and wadoco=64337180
group by wadoco, wauorg
/ASKER
Hi Slightwv,
No, Min does not work. It is just a co-insidence that the dates to be retrieved are the lowest dates. The logic for the 2 subqueries are:
1st Subquery
Retrieve systrt from F4801Z1_wh where sydoco=wadoco and sysrst='20' and the syupmj is the earliest entry in file for status '20'
2nd Subquery
Retrieve syupmj from F4801Z1_wh where sydoco=wadoco and sysrst='40' and the syupmj is the earliest entry in file for status '40'
regards
Pat
No, Min does not work. It is just a co-insidence that the dates to be retrieved are the lowest dates. The logic for the 2 subqueries are:
1st Subquery
Retrieve systrt from F4801Z1_wh where sydoco=wadoco and sysrst='20' and the syupmj is the earliest entry in file for status '20'
2nd Subquery
Retrieve syupmj from F4801Z1_wh where sydoco=wadoco and sysrst='40' and the syupmj is the earliest entry in file for status '40'
regards
Pat
I haven't been posting to this thread because I was confused by it. It makes a little more sense now, but I'm still going to stay out of it because it looks like it is going the right direction. However, I will ask the question that everyone else needs the answer to.
Can you explain how to get:
Can you explain how to get:
earliest entry in file for status '20'To everyone else, that is going to mean MIN. If it isn't MIN, then you need to explain how you get there.
Yes, please post more sample data and expected results.
If we have questions on how you get the result, we'll ask.
If we have questions on how you get the result, we'll ask.
ASKER
Take the slightly altered example below. The earliest entry for status '20' or '40' will always be determined by SYUPMJ which represents the date updated to file. When looking for the earlient status '20' I want to return the "planned start date" associated with the earliest entry not the minimum start date. In this instance the Planned start date will be 119020 (julian) (or 20th Jan 2019)
When looking for the earlient status '40' I want to return the Date updated (SYUPMJ) associated with the earliest entry so this value could be specified as a MIN for status 40. The calculations for '20' and '40' are different.
F4801Z1_wh
sydoco sysrst systrt syupmj
64337180 20 119020 119001
64337180 20 119019 119002
64337180 20 119021 119003
64337180 30 119022 119005
64337180 40 119023 119025
64337180 40 119023 119026
64337180 40 119023 119027
64337180 40 119023 119028
64337180 90 119024 119030
When looking for the earlient status '40' I want to return the Date updated (SYUPMJ) associated with the earliest entry so this value could be specified as a MIN for status 40. The calculations for '20' and '40' are different.
F4801Z1_wh
sydoco sysrst systrt syupmj
64337180 20 119020 119001
64337180 20 119019 119002
64337180 20 119021 119003
64337180 30 119022 119005
64337180 40 119023 119025
64337180 40 119023 119026
64337180 40 119023 119027
64337180 40 119023 119028
64337180 90 119024 119030
I think it can be cleaner but until we come up with a better way, try this:
select
wadoco as "Order #",
wauorg as "Cur Open Qty",
min(case when rn=1 and sysrst=20 then systrt end) as "Planned Start Date (20)",
min(case when rn=1 and sysrst=40 then syupmj end) as "Actual Start Date (40)"
from (
select
wadoco,
wauorg,
systrt,
syupmj,
sysrst,
ROW_NUMBER() OVER (PARTITION BY wadoco,sysrst ORDER BY syupmj,systrt) rn
from F4801_wh join F4801Z1_wh on sydoco=wadoco
where wasrst>'40' and wasrst<'90' and wadoco=64337180
)
group by wadoco, wauorg
/ASKER
Sorry, been away for the past week. I'm still having problems with the subquery and I think that the example I gave was too different from the actual requirement to work. I have made up a different example which is closer to the actual but removes much of the noise
Here is the current SQL
Here is the output
Current output
Wrk Started Current Actual Actual Start
Centre Order# Qty Qty Start Dt(40) Time(40)
CPGA 63999279 24 24 20-MAR-19 212128
I need to modify the above query to include another sub query over F4801Z1_wh selecting the Planned start date from the earliest status '20' record. Whereas Actual Start dt(40) is taken from F4801Z1_wh as the 1st date that the order moved to status ‘40’, Planned Start dt(20) is the SYSTRT date value when the order first moved to status ‘20’. Both date come from the same file The required output will be:
Here is the Required output
Wrk Started Current Planned Actual Actual Start
Centre Order# Qty Qty Start Dt(20) Start Dt(40) Time(40)
CPGA 63999279 24 24 20-JUN-18 20-MAR-19 212128
Files involved in SQL statement:
F4801Z1_wh
Order Status Plan Start Date updated Time Updated Order Qty
SYDOCO SYSRST SYSTRT SYUPMJ SYTDAY SYUORG
63999279 10 17-JUN-18 11-MAR-18 101127 24
63999279 20 20-JUN-18 21-MAR-18 105521 24
63999279 20 21-JUN-18 21-MAR-18 105954 24
63999279 20 28-APR-18 22-APR-18 71243 24
63999279 20 03-DEC-18 25-APR-18 54043 24
63999279 30 19-MAR-19 19-MAR-19 205229 24
63999279 40 19-MAR-19 20-MAR-19 212128 24
63999279 40 19-MAR-19 22-MAR-19 193834 24
63999279 40 19-MAR-19 23-MAR-19 175715 24
63999279 40 19-MAR-19 24-MAR-19 80028 24
F4801_wh
Order Status Order Qty
WAOCO WASRST WAUORG
63999279 40 24
F3112_wh
Order Op Seq Cost centre
WLDOCO WLOPSQ WLMCU
63999279 10 CPGA
I know that I nheed to insert something like the following into the existing query but I cannot determine where it should be inserted. Every attempt to do this results is SQL format error
Here is the current SQL
SELECT
"Wrk Centre",
"Order #",
"Started Qty",
"Current Qty",
"Actual Start Dt(40)",
"Actual Start Time(40)"
from (
SELECT
wlmcu as "Wrk Ctr",
wadoco as "Order #",
syuorg as "Qty Started",
wauorg as "Cur Qty Open",
to_char(to_date((syupmj+ 1900000),'yyyyddd')) as "Actual Start Dt(40)",
SYTDAY as "Actual Start Time(40)",
row_number() over(partition by wldoco order by wlopsq desc) new_rn
FROM (SELECT t1.*, t2.*
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t2.syupmj,t2.sytday) rn
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t2
ON t1.wadoco = t2.sydoco and sysrst='40')
LEFT OUTER JOIN JDESTAGE.F3112_wh on wldoco=wadoco
WHERE rn = 1 and wasrst >='40' and wasrst<'90' and wadoco=63999279 order by syupmj,sytday
)
where new_rn=1Here is the output
Current output
Wrk Started Current Actual Actual Start
Centre Order# Qty Qty Start Dt(40) Time(40)
CPGA 63999279 24 24 20-MAR-19 212128
I need to modify the above query to include another sub query over F4801Z1_wh selecting the Planned start date from the earliest status '20' record. Whereas Actual Start dt(40) is taken from F4801Z1_wh as the 1st date that the order moved to status ‘40’, Planned Start dt(20) is the SYSTRT date value when the order first moved to status ‘20’. Both date come from the same file The required output will be:
Here is the Required output
Wrk Started Current Planned Actual Actual Start
Centre Order# Qty Qty Start Dt(20) Start Dt(40) Time(40)
CPGA 63999279 24 24 20-JUN-18 20-MAR-19 212128
Files involved in SQL statement:
F4801Z1_wh
Order Status Plan Start Date updated Time Updated Order Qty
SYDOCO SYSRST SYSTRT SYUPMJ SYTDAY SYUORG
63999279 10 17-JUN-18 11-MAR-18 101127 24
63999279 20 20-JUN-18 21-MAR-18 105521 24
63999279 20 21-JUN-18 21-MAR-18 105954 24
63999279 20 28-APR-18 22-APR-18 71243 24
63999279 20 03-DEC-18 25-APR-18 54043 24
63999279 30 19-MAR-19 19-MAR-19 205229 24
63999279 40 19-MAR-19 20-MAR-19 212128 24
63999279 40 19-MAR-19 22-MAR-19 193834 24
63999279 40 19-MAR-19 23-MAR-19 175715 24
63999279 40 19-MAR-19 24-MAR-19 80028 24
F4801_wh
Order Status Order Qty
WAOCO WASRST WAUORG
63999279 40 24
F3112_wh
Order Op Seq Cost centre
WLDOCO WLOPSQ WLMCU
63999279 10 CPGA
I know that I nheed to insert something like the following into the existing query but I cannot determine where it should be inserted. Every attempt to do this results is SQL format error
row_number() over(partition by wldoco order by wlopsq desc) new_rn
FROM (SELECT t1.*, t2.*
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t2.syupmj,t2.sytday) rn
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t2
ON t1.wadoco = t2.sydoco and sysrst='20')
As before, I need actual sample data and expected results from that data.
You posted output of a query. For example SYUPMJ is a julian date but you posted 11-MAR-18. You show SYSTRT in the output but that isn't part of the query you posted.
I need to set up a test case to run your query to generate the results you show and tweak it to get the results you want.
With the latest post, I cannot do this.
That said, if I make dates you posted actual date columns and add your new data, I get the results you say you want using my last query.
Here is my complete test case (I changed the table names by adding 'my' to them):
My output:
You posted output of a query. For example SYUPMJ is a julian date but you posted 11-MAR-18. You show SYSTRT in the output but that isn't part of the query you posted.
I need to set up a test case to run your query to generate the results you show and tweak it to get the results you want.
With the latest post, I cannot do this.
That said, if I make dates you posted actual date columns and add your new data, I get the results you say you want using my last query.
Here is my complete test case (I changed the table names by adding 'my' to them):
--drop table myF4801_wh purge;
create table myF4801_wh(wadoco number, wasrst number, wauorg number);
insert into myF4801_wh values(63999279,40,24);
--drop table myF4801Z1_wh purge;
create table myF4801Z1_wh(SYDOCO number, SYSRST number, SYSTRT date, SYUPMJ date, SYTDAY number, SYUORG number);
insert into myF4801Z1_wh values(63999279,10,to_date('17-JUN-18','DD-MON-YY'),to_date('11-MAR-18','DD-MON-YY'),101127,24);
insert into myF4801Z1_wh values(63999279,20,to_date('20-JUN-18','DD-MON-YY'),to_date('21-MAR-18','DD-MON-YY'),105521,24);
insert into myF4801Z1_wh values(63999279,20,to_date('21-JUN-18','DD-MON-YY'),to_date('21-MAR-18','DD-MON-YY'),105954,24);
insert into myF4801Z1_wh values(63999279,20,to_date('28-APR-18','DD-MON-YY'),to_date('22-APR-18','DD-MON-YY'),71243,24);
insert into myF4801Z1_wh values(63999279,20,to_date('03-DEC-18','DD-MON-YY'),to_date('25-APR-18','DD-MON-YY'),54043,24);
insert into myF4801Z1_wh values(63999279,30,to_date('19-MAR-19','DD-MON-YY'),to_date('19-MAR-19','DD-MON-YY'),205229,24);
insert into myF4801Z1_wh values(63999279,40,to_date('19-MAR-19','DD-MON-YY'),to_date('20-MAR-19','DD-MON-YY'),212128,24);
insert into myF4801Z1_wh values(63999279,40,to_date('19-MAR-19','DD-MON-YY'),to_date('22-MAR-19','DD-MON-YY'),193834,24);
insert into myF4801Z1_wh values(63999279,40,to_date('19-MAR-19','DD-MON-YY'),to_date('23-MAR-19','DD-MON-YY'),175715,24);
insert into myF4801Z1_wh values(63999279,40,to_date('19-MAR-19','DD-MON-YY'),to_date('24-MAR-19','DD-MON-YY'),80028,24);
commit;
select
wadoco as "Order #",
wauorg as "Cur Open Qty",
min(case when rn=1 and sysrst=20 then systrt end) as "Planned Start Date (20)",
min(case when rn=1 and sysrst=40 then syupmj end) as "Actual Start Date (40)"
from (
select
wadoco,
wauorg,
systrt,
syupmj,
sysrst,
ROW_NUMBER() OVER (PARTITION BY wadoco,sysrst ORDER BY syupmj,systrt) rn
from myF4801_wh join myF4801Z1_wh on sydoco=wadoco
where wasrst>='40' and wasrst<'90' and wadoco=63999279
)
group by wadoco, wauorg
/My output:
Order # Cur Open Qty Planned S Actual St
---------- ------------ --------- ---------
63999279 24 20-JUN-18 20-MAR-19ASKER
Hi Slightwv,
I changed the example to use standard date to try and remove much of the noise (eg converting dates from julian etc..). The problem with your solution is that you are using Min and Group to get the desired results whereas I want to be able to create two sub queries which return date from different records of the same file. Whereas Min and Group may work for certain circumstances ,it will not always be the case.
I believe that it should be possible to create a 2nd sub query to return the Planned Start date of the 1st status '20' record in F4801Z1_wh. I am trying to understand how to implement the 2nd sub query whilst retaining all of the existing logic (OUTER JOINs etc..)
regards
Pat
I changed the example to use standard date to try and remove much of the noise (eg converting dates from julian etc..). The problem with your solution is that you are using Min and Group to get the desired results whereas I want to be able to create two sub queries which return date from different records of the same file. Whereas Min and Group may work for certain circumstances ,it will not always be the case.
I believe that it should be possible to create a 2nd sub query to return the Planned Start date of the 1st status '20' record in F4801Z1_wh. I am trying to understand how to implement the 2nd sub query whilst retaining all of the existing logic (OUTER JOINs etc..)
regards
Pat
Why create two queries against the same table when I've not seen a reason to do so?
Are you assuming it may not work or do you have data showing it will not work? If you can show me where it doesn't work then I can try and fix the SQL.
If you still want tow queries, I believe a post somewhere above addresses it. Joins shouldn't change once you get the correct aliases on the correct tables/values but I'm not going to re-read the entire thread and attempt to make what appears to be working SQL into inefficient SQL just because.
Are you assuming it may not work or do you have data showing it will not work? If you can show me where it doesn't work then I can try and fix the SQL.
If you still want tow queries, I believe a post somewhere above addresses it. Joins shouldn't change once you get the correct aliases on the correct tables/values but I'm not going to re-read the entire thread and attempt to make what appears to be working SQL into inefficient SQL just because.
ASKER
Hi Slightwv,
I have reverted to you initial solution using Min, However I am getting an ORA-00936 missing expression when I expand to include all the required tables
regards
Pat
select
wlmcu as "Wrk Ctr",
wlopsq as "Op Seq",
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",
min(case when rn=1 and sysrst='20' then systrt end) as "Orig Plan Start Date (20)",
to_char(to_date((wastrt+ 1900000),'yyyyddd')) as "Cur Plan Start Date",
min(case when rn=1 and sysrst='40' then syupmj end) as "Actual Start Date (40)",
CASE WHEN to_date(1900000 + wadrqj,'YYYYDDD')-trunc(sy
wawr10 as "Cat Code",
waresc as "WO Seq",
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 || ' - '|| drdl01 as "Master Planning family",
drdl01 as "Family Name",
CASE WHEN ibprp4 in('2F9','8O8','2G3','8J7'
wammcu as "Bus Unit",
from (
select
wlmcu,
wlopsq,
wadoco,
walitm,
syuorg,
wauorg,
watrdj,
sydrqj,
wadrqj,
wastrt,
wawr10,
waresc,
ibanpl,
abalph,
ibprp4,
drdl01,
wammcu,
ROW_NUMBER() OVER (PARTITION BY wadoco,sysrst ORDER BY syupmj,systrt) rn
from JDESTAGE.F4801_wh join JDESTAGE.F4801Z1_wh on sydoco=wadoco
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 wasrst>'40' and wasrst<='99' and wadoco=64337180
)
group by
wlmcu ,
wlopsq,
wadoco,
walitm,
syuorg,
wauorg,
watrdj,
sydrqj,
wadrqj,
wastrt,
wawr10,
waresc,
ibanpl,
abalph,
ibprp4,
drdl01,
wammcu
I have reverted to you initial solution using Min, However I am getting an ORA-00936 missing expression when I expand to include all the required tables
regards
Pat
select
wlmcu as "Wrk Ctr",
wlopsq as "Op Seq",
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",
min(case when rn=1 and sysrst='20' then systrt end) as "Orig Plan Start Date (20)",
to_char(to_date((wastrt+ 1900000),'yyyyddd')) as "Cur Plan Start Date",
min(case when rn=1 and sysrst='40' then syupmj end) as "Actual Start Date (40)",
CASE WHEN to_date(1900000 + wadrqj,'YYYYDDD')-trunc(sy
wawr10 as "Cat Code",
waresc as "WO Seq",
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 || ' - '|| drdl01 as "Master Planning family",
drdl01 as "Family Name",
CASE WHEN ibprp4 in('2F9','8O8','2G3','8J7'
wammcu as "Bus Unit",
from (
select
wlmcu,
wlopsq,
wadoco,
walitm,
syuorg,
wauorg,
watrdj,
sydrqj,
wadrqj,
wastrt,
wawr10,
waresc,
ibanpl,
abalph,
ibprp4,
drdl01,
wammcu,
ROW_NUMBER() OVER (PARTITION BY wadoco,sysrst ORDER BY syupmj,systrt) rn
from JDESTAGE.F4801_wh join JDESTAGE.F4801Z1_wh on sydoco=wadoco
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 wasrst>'40' and wasrst<='99' and wadoco=64337180
)
group by
wlmcu ,
wlopsq,
wadoco,
walitm,
syuorg,
wauorg,
watrdj,
sydrqj,
wadrqj,
wastrt,
wawr10,
waresc,
ibanpl,
abalph,
ibprp4,
drdl01,
wammcu
Missing expression is here:
wammcu as "Bus Unit",
from (
You have a comma before the FROM so it thinks there is another column.
wammcu as "Bus Unit",
from (
You have a comma before the FROM so it thinks there is another column.
ASKER
Made the change but also forced to add wasrst, sysrst, systrt syupmj to the Group by clause to get query working. I can now run the query successfully however it now reports multiple lines per order number whereas it should only have 1 summary line per order number. It should report only one line which should be for the Max OpSeq line which is 9400 and this one line should include the Orig Plan Start Date(20) of 26-Jun-19 and Actual Start Date(40) of 05-Mar-19. (combination of lines 46 & 120 on sample output)
Sample output:
Current Query
select
wlmcu as "Wrk Ctr",
wlopsq as "Op Seq",
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",
min(case when rn=1 and sysrst='20' then systrt end) as "Orig Plan Start Date (20)",
to_char(to_date((wastrt+ 1900000),'yyyyddd')) as "Cur Plan Start Date",
min(case when rn=1 and sysrst='40' then syupmj end) as "Actual Start Date (40)",
CASE WHEN to_date(1900000 + wadrqj,'YYYYDDD')-trunc(sy
wawr10 as "Cat Code",
waresc as "WO Seq",
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 || ' - '|| drdl01 as "Master Planning family",
drdl01 as "Family Name",
CASE WHEN ibprp4 in('2F9','8O8','2G3','8J7'
wammcu as "Bus Unit"
from (
select
wlmcu,
wlopsq,
wadoco,
walitm,
syuorg,
wauorg,
watrdj,
sydrqj,
wadrqj,
wastrt,
systrt,
syupmj,
wawr10,
waresc,
ibanpl,
abalph,
ibprp4,
drdl01,
wammcu,
wasrst,
sysrst,
ROW_NUMBER() OVER (PARTITION BY wadoco,sysrst ORDER BY syupmj,systrt) rn
from JDESTAGE.F4801_wh join JDESTAGE.F4801Z1_wh on sydoco=wadoco
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 wasrst>'40' and wasrst<='99' and wadoco=64337180
)
group by
wlmcu ,
wlopsq,
wadoco,
walitm,
syuorg,
wauorg,
watrdj,
sydrqj,
wadrqj,
wastrt,
systrt,
syupmj,
wawr10,
waresc,
ibanpl,
abalph,
ibprp4,
drdl01,
wammcu,
wasrst,
sysrst
Sample output:
Wrk Ctr Op Seq Order # Item # Qty started Cur Qty Open W/O Create Date Orig Requested Date (OTIF) Cur Requested Date Orig Plan Start Date (20) Cur Plan Start Date Actual Start Date (40) Due in the next week Cat Code WO Seq Schedule adhered to Y/N Past Due Y/N Due in days Due in the next week Due in the next fortnight Current Status Planner # Master Planning family Family Name Site Bus Unit
CPGA 12500 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 90000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 4000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
W800GA 93000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 1000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 32000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 90000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 13000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 2000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 90000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 85000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 13000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 5000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 90000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 13000 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12400 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 90000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 5000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 1000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 13000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 2000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 94000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 1000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 85000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 4000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12400 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
W800GA 93000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 1000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 13000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 5000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 13000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 2000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 5000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 32000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
W800GA 93000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 32000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 4000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 94000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 05-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 5000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12400 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 13000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 32000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 90000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 85000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 5000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 2000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92500 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 4000 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 1000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 94000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 94000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 85000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 5000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 4000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 32000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 85000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 94000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 2000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 85000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
W800GA 93000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12400 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 1000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92000 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 1000 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12400 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 13000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 5000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12400 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 32000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 85000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 13000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 32000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
W800GA 93000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 2000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 32000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 4000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 13000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 2000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 94000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 4000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
W800GA 93000 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12500 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 5000 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
W800GA 93000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 1000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 4000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 94000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12500 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12400 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 90000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 85000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
W800GA 93000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12400 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
W800GA 93000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 1000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 4000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
W800GA 93000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 90000 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 94000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 26-JUN-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92500 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 2000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 4000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 2000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92500 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 90000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 5000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 1000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12400 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 32000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 1000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12400 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 85000 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 2000 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 85000 64337180 42532007502 24 24 26-FEB-19 11-JUL-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 13000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92500 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 12400 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 2000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 90000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
W800GA 93000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 94000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 4000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
TIBIAGA 5000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 92000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 90000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 85000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 94000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 32000 64337180 42532007502 24 24 26-FEB-19 05-APR-19 05-APR-19 18-MAR-19 Y 0 Y N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 94000 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IM
CPGA 32000 64337180 42532007502 0 24 26-FEB-19 05-APR-19 18-MAR-19 Y 0 N N 2 Y Y 99 261396 - DUNLEAVY, MICHELLE 4P6 - ALTA 5320 TIB NP STM 5 DEG ALTA 5320 TIB NP STM 5 DEG GAL 4IMCurrent Query
select
wlmcu as "Wrk Ctr",
wlopsq as "Op Seq",
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",
min(case when rn=1 and sysrst='20' then systrt end) as "Orig Plan Start Date (20)",
to_char(to_date((wastrt+ 1900000),'yyyyddd')) as "Cur Plan Start Date",
min(case when rn=1 and sysrst='40' then syupmj end) as "Actual Start Date (40)",
CASE WHEN to_date(1900000 + wadrqj,'YYYYDDD')-trunc(sy
wawr10 as "Cat Code",
waresc as "WO Seq",
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 || ' - '|| drdl01 as "Master Planning family",
drdl01 as "Family Name",
CASE WHEN ibprp4 in('2F9','8O8','2G3','8J7'
wammcu as "Bus Unit"
from (
select
wlmcu,
wlopsq,
wadoco,
walitm,
syuorg,
wauorg,
watrdj,
sydrqj,
wadrqj,
wastrt,
systrt,
syupmj,
wawr10,
waresc,
ibanpl,
abalph,
ibprp4,
drdl01,
wammcu,
wasrst,
sysrst,
ROW_NUMBER() OVER (PARTITION BY wadoco,sysrst ORDER BY syupmj,systrt) rn
from JDESTAGE.F4801_wh join JDESTAGE.F4801Z1_wh on sydoco=wadoco
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 wasrst>'40' and wasrst<='99' and wadoco=64337180
)
group by
wlmcu ,
wlopsq,
wadoco,
walitm,
syuorg,
wauorg,
watrdj,
sydrqj,
wadrqj,
wastrt,
systrt,
syupmj,
wawr10,
waresc,
ibanpl,
abalph,
ibprp4,
drdl01,
wammcu,
wasrst,
sysrst
Do you still need help with this? I apologize but I've been busy lately.
I have a little time if you still need help.
As always: Please post sample data and expected results so I can run the query you posted to see where it is failing.
I have a little time if you still need help.
As always: Please post sample data and expected results so I can run the query you posted to see where it is failing.
ASKER
Hi Slightwv,
you can see from my last post that I am getting output, but it is at the wrong level. For each work order I should be getting 1 line which includes the key values v but instead I am getting multiple lines rather than just 1 line. I believe that this is due to the group clause.
regards
Pat
you can see from my last post that I am getting output, but it is at the wrong level. For each work order I should be getting 1 line which includes the key values v but instead I am getting multiple lines rather than just 1 line. I believe that this is due to the group clause.
regards
Pat
Probably your grouping. For order# 12500, I see two different watrdj values and that is in your group by. If you only want 1 row, which watrdj is "valid"?
ASKER
12500 is not an order number. It is an 'Op Seq'. THere is just 1 order number in the example which is 64337180
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Slightwv,
finally got it working correctly by adjusting the Group by fields. I realy appreciate your help with this
regards
Pat
finally got it working correctly by adjusting the Group by fields. I realy appreciate your help with this
regards
Pat
ASKER
thanks to everyone for being patient and finally helping me to resolve the issue
I think it should be like the following:
***NEW LOGIC START***
LEFT OUTER JOIN (SELECT t1.*, t3.*
,ROW_NUMBER() OVER (PARTITION BY t1.wadoco ORDER BY t3.syupmj,t3.sytday) rn2
FROM JDESTAGE.F4801_wh t1
LEFT JOIN JDESTAGE.F4801Z1_wh t3
ON t1.wadoco = t3.sydoco and t3.sysrst='20') t3 ON t2.wadoco = t3.wadoco
***NEW LOGIC END***
t2 an t3 may return different record due to difference in t2.sysrst='40' and t3.sysrst='20'