| 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 -> Re: BIG Outer join problem
amerar_at_iwc.net wrote:
>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
> 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.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
> and (/* decode(sign(sord.user_fld_3-300),1, sord.rqst_shp_dt,
>/*sord.rqst_shp_dt-sord.user_fld_3) DJW 10/05/04
> < 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
>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,
> to_number (null) as qty_on_hand, 'Soft' as rel_ord_nbr, 'All' 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 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
> 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.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
> 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))
> 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;
What part of 'You cannot do it that way' did you not understand?
Cause: expression(+) relop (subquery) is not allowed.
Action: Either remove the (+) or make a view out of the subquery. In V6 and before, the (+) was just ignored in this case.
Did you try the soultion provided in the error message? Received on Mon Mar 07 2005 - 11:40:31 CST
 |
 |