| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> BIG Outer join problem
Hi All,
You will hate me after looking at the query below. But, if you look at the query, there is a section (actually in 2 places) where I compare using a hard coded year and period (2005 & 3).
I want to join that to a table with one row that contains the peoper year and period. that way, I do not have to keep changing it each month.
I get all sorts of ORA-01799 when attempting this. Can anyone help me plow through this monster below?
Thanks,
Arthur
CREATE OR REPLACE FORCE VIEW CSIOWNER.MPC_SORD_MRP_EXPEDITE
(CUST_ID, ORD_STATUS, ORD_NBR, LN_NBR, PRODUCT_LN,
TOT_ITM_AMT, SHP_NBR, SHP_EXCP, RQST_SHP_DT, CUR_PRM_SHP_DT,
USER_FLD_3, OR_SOONER, PRT_NBR, PROJECT_ID, QTY_OUTS,
QTY_ON_HAND, REL_ORD_NBR, REL_SUB_ORD_NBR, PART_NBR, PLANNED_QTY,
ALLOC_QTY, PLANNED_DATE, ORDER_TYPE, MRP_ORD_NBR, SUB_ORD_NBR,
QTY_ON_ORD, PLAN_RECV_DATE, ORD_STAT, EXCP_CODE, TEXT,
OPS_TOTAL, OPS_TO_REPORT, OPS_CLOSED, AT_WCTR, AT_RATS)
AS
select sorm.cust_id, sorm.ord_status, sorm.ord_nbr, sord.ln_nbr,
prod.product_ln,
round(sord.tot_itm_amt,0) as tot_itm_amt, sord.shp_nbr, sord.shp_excp,
sord.rqst_shp_dt, sord.cur_prm_shp_dt, sord.user_fld_3,
decode(sign(sord.user_fld_3-300),1, sord.rqst_shp_dt,
sord.rqst_shp_dt-sord.user_fld_3) as or_sooner,
sord.prt_nbr, sord.project_id, sord.qty_reqd - sord.tot_qty_shp as
qty_outs,
qty_on_hand, oords.ord_nbr as rel_ord_nbr, oords.sub_ord_nbr as
rel_sub_ord_nbr,
sqty.part_nbr, sqty.planned_qty, sqty.alloc_qty, sqty.planned_date,
decode(sqty.record_type,'WP',sqty.ord_type,sqty.record_type) as
order_type,
oord.ord_nbr as mrp_ord_nbr, oord.sub_ord_nbr, oord.qty_on_ord,
oord.plan_recv_date, oord.ord_stat, oord.excp_code, msgs.text,
ops_total, ops_to_report, ops_closed, at_wctr, at_rats
from prod, msgs, oord, sqty, sord, sorm,
(select part_nbr,
control_id,
sum(qty_on_hand) as qty_on_hand
from invd,
(select distinct prt_nbr, project_id
from sord, sorm
where sorm.system_type in
('PROD','PROTO','AFTRMRKT','OVRHL') and sorm.ord_status not like '%L'
and sord.ord_nbr = sorm.ord_nbr
and sord.org_id = sorm.org_id
and sord.rec_cd = 'DT'
and sord.ord_itm_status <> 'CL'
and sord.project_id <> ' '
and sord.prt_nbr <> ' '
and ( decode(sign(sord.user_fld_3-300),1,
sord.rqst_shp_dt, sord.rqst_shp_dt-sord.user_fld_3)
< (sysdate + 60)
or decode(sign(sord.user_fld_3-300),1,
sord.cur_prm_shp_dt, sord.cur_prm_shp_dt-sord.user_fld_3)
< (sysdate + 60))) sord
where invd.mrp_avail_flag = ' '
and sord.prt_nbr = invd.part_nbr
and sord.project_id = invd.control_id
group by part_nbr, control_id ) invd,
(select part_nbr,
control_id,
oord.ord_nbr,
oord.sub_ord_nbr
from oord,
(select distinct prt_nbr, project_id
from sord, sorm
where sorm.system_type in
('PROD','PROTO','AFTRMRKT','OVRHL') and sorm.ord_status not like '%L'
and sord.ord_nbr = sorm.ord_nbr
and sord.org_id = sorm.org_id
and sord.rec_cd = 'DT'
and sord.ord_itm_status <> 'CL'
and sord.project_id <> ' '
and sord.prt_nbr <> ' '
and ( decode(sign(sord.user_fld_3-300),1,
sord.rqst_shp_dt, sord.rqst_shp_dt-sord.user_fld_3)
< (sysdate + 60)
or decode(sign(sord.user_fld_3-300),1,
sord.cur_prm_shp_dt, sord.cur_prm_shp_dt-sord.user_fld_3)
< (sysdate + 60))) sord
where oord.ord_stat <> 'CL'
and oord.plan_recv_date < (sysdate + 60)
and oord.qty_on_ord > oord.qty_recvd
and sord.prt_nbr = oord.part_nbr
and sord.project_id = oord.control_id
UNION ALL
select oord.part_nbr,
oord.control_id,
oord2.ord_nbr,
oord2.sub_ord_nbr
from oord,
rqmt,
part,
oord oord2,
(select distinct prt_nbr, project_id
from sord, sorm
where sorm.system_type in
('PROD','PROTO','AFTRMRKT','OVRHL') and sorm.ord_status not like '%L'
and sord.ord_nbr = sorm.ord_nbr
and sord.org_id = sorm.org_id and sorm.org_id =
'001'
and sord.rec_cd = 'DT'
and sord.ord_itm_status <> 'CL'
and sord.project_id <> ' '
and sord.prt_nbr <> ' '
and ( decode(sign(sord.user_fld_3-300),1,
sord.rqst_shp_dt, sord.rqst_shp_dt-sord.user_fld_3)
< (sysdate + 60)
or decode(sign(sord.user_fld_3-300),1,
sord.cur_prm_shp_dt, sord.cur_prm_shp_dt-sord.user_fld_3)
< (sysdate + 60))) sord
where oord.ord_stat <> 'CL'
and oord.plan_recv_date < (sysdate + 60)
and oord.qty_on_ord > oord.qty_recvd
and sord.prt_nbr = oord.part_nbr
and sord.project_id = oord.control_id
and rqmt.ord_nbr = oord.ord_nbr
and rqmt.sub_ord_nbr = oord.sub_ord_nbr
and part.part_nbr = rqmt.comp_part_nbr
and part.project_ctrl_code <> 'N'
and oord2.part_nbr = part.part_nbr
and oord2.control_id = oord.control_id
) oords,
(select sopn.ord_nbr, sopn.sub_ord_nbr,
count(distinct sopn.major_seq_nbr) as ops_total,
sum(decode(sopn.recd_code,'PO',1,0)) as ops_to_report,
sum(decode(sopn.recd_code ||
substr(sopn.po_operation_status,1,1),'POC',1,0))
as ops_closed,
substr(min(decode(sopn.recd_code,
'PO',decode(substr(sopn.po_operation_status,1,1),
'C',TO_CHAR(NULL),
sopn.major_seq_nbr ||
sopn.alternate_seq_nbr
|| sopn.minor_seq_nbr ||
sopn.po_work_center_id
)
)
)
,8,5) as at_wctr,
substr(min(decode(sopn.recd_code,
'PO',decode(substr(sopn.po_operation_status,1,1),
'C',TO_CHAR(NULL),
sopn.major_seq_nbr ||
sopn.alternate_seq_nbr
|| sopn.minor_seq_nbr ||
sopn.row_added_ts
)
)
)
,8,20) as at_rats
from sopn, oord
where oord.ord_stat in ('RR', 'OP')
and sopn.ord_nbr = oord.ord_nbr
and sopn.sub_ord_nbr = oord.sub_ord_nbr
and sopn.recd_code <> 'PE'
group by sopn.ord_nbr, sopn.sub_ord_nbr
) sopn
and sord.ord_nbr = sorm.ord_nbr and sord.org_id = sorm.org_id and sord.rec_cd = 'DT' and sord.project_id <> ' ' and sord.prt_nbr <> ' ' and sord.prt_nbr <> 'NRE' and sord.ord_itm_status <> 'CL' and sord.qty_reqd > sord.tot_qty_shp
< to_date('20041224','YYYYMMDD')
or decode(sign(sord.user_fld_3-300),1,*/ sord.cur_prm_shp_dt --,
sord.cur_prm_shp_dt-sord.user_fld_3)
< (sysdate + 60))
--)
and sord.product_id = prod.product_id
and invd.part_nbr (+) = sord.prt_nbr
and invd.control_id (+) = sord.project_id
and oords.part_nbr (+) = sord.prt_nbr
and oords.control_id (+) = sord.project_id
and sqty.fiscal_year (+) = 2005
and sqty.fiscal_period (+) = 3
and nvl(sqty.record_type,'WP') in ('WP', 'PO', 'PR')
and sqty.alloc_ord_nbr (+) = oords.ord_nbr
and sqty.alloc_sub_ord_nbr (+) = oords.sub_ord_nbr
and oord.ord_nbr (+) = sqty.ord_nbr
and oord.sub_ord_nbr (+) = sqty.sub_ord_nbr
and nvl(oord.ord_stat,'XX') <> 'CL'
and msgs.msgs_number (+) = oord.excp_code
and sopn.ord_nbr (+) = sqty.ord_nbr
and sopn.sub_ord_nbr (+) = sqty.sub_ord_nbr
union all
qty_outs, to_number (null) as qty_on_hand, 'Soft' as rel_ord_nbr, 'All' as rel_sub_ord_nbr,
(select sopn.ord_nbr, sopn.sub_ord_nbr,
count(distinct sopn.major_seq_nbr) as ops_total,
sum(decode(sopn.recd_code,'PO',1,0)) as ops_to_report,
sum(decode(sopn.recd_code ||
substr(sopn.po_operation_status,1,1),'POC',1,0))
as ops_closed,
substr(min(decode(sopn.recd_code,
'PO',decode(substr(sopn.po_operation_status,1,1),
'C',TO_CHAR(NULL),
sopn.major_seq_nbr ||
sopn.alternate_seq_nbr
|| sopn.minor_seq_nbr ||
sopn.po_work_center_id
)
)
)
,8,5) as at_wctr,
substr(min(decode(sopn.recd_code,
'PO',decode(substr(sopn.po_operation_status,1,1),
'C',TO_CHAR(NULL),
sopn.major_seq_nbr ||
sopn.alternate_seq_nbr
|| sopn.minor_seq_nbr ||
sopn.row_added_ts
)
)
)
,8,20) as at_rats
from sopn, oord, art
where oord.ord_stat in ('RR', 'OP')
and sopn.ord_nbr = oord.ord_nbr
and sopn.sub_ord_nbr = oord.sub_ord_nbr
and sopn.recd_code <> 'PE'
group by sopn.ord_nbr, sopn.sub_ord_nbr
) sopn
and sord.ord_nbr = sorm.ord_nbr and sord.org_id = sorm.org_id and sord.rec_cd = 'DT' and sord.project_id = ' ' and sord.prt_nbr <> ' ' and sord.prt_nbr <> 'NRE' and sord.ord_itm_status <> 'CL' and sord.qty_reqd > sord.tot_qty_shp
< (sysdate + 60)
< (sysdate + 60))
and sord.product_id = prod.product_id
and sqty.fiscal_year (+) = 2005
and sqty.fiscal_period (+) = art.sasc_fiscal_period
and nvl(sqty.record_type,'WP') in ('WP', 'PO', 'PR')
and sqty.alloc_ord_nbr (+) = substr(sord.ord_nbr,1,6)
and sqty.alloc_sub_ord_nbr (+) = to_char(sord.ln_nbr,'FM000')
and oord.ord_nbr (+) = sqty.ord_nbr
and oord.sub_ord_nbr (+) = sqty.sub_ord_nbr
and nvl(oord.qty_recvd,0) < nvl(oord.qty_on_ord,1)
and nvl(oord.ord_stat,'XX') <> 'CL'
and msgs.msgs_number (+) = oord.excp_code
and sopn.ord_nbr (+) = sqty.ord_nbr
and sopn.sub_ord_nbr (+) = sqty.sub_ord_nbr
WITH READ ONLY;
Received on Mon Mar 07 2005 - 10:55:58 CST
 |
 |