求高手优化这条sql语句
语句一和语句二都能很快显示结果,但当一和二合并时,却半天没有结果
语句一:
select we.wip_entity_name,
msi.inventory_item_id,
msi.segment1,
we.organization_id,
msi.description,
wdj.START_QUANTITY,
wdj.QUANTITY_completed,
wdj.QUANTITY_SCRAPPED,
wdj.creation_date,
max(WMT.TRANSACTION_DATE) last_date,
wdj.COMPLETION_SUBINVENTORY,
wdj.DATE_COMPLETED,
TO_OPERATION_SEQ_NUM,
wdj.wip_entity_id,
decode(substr(wdj.class_code, 1, 2),
'FX',
msi.attribute1,
msi.WIP_SUPPLY_SUBINVENTORY) WIP_SUPPLY_SUBINVENTORY
from wip.wip_move_transactions wmt,
wip.WIP_DISCRETE_JOBS wdj,
wip.wip_entities we,
inv.mtl_system_items msi
where
wmt.organization_id+0=4
and wdj.wip_entity_id = wmt.wip_entity_id+0
and wdj.COMPLETION_SUBINVENTORY = 'J030半成品'
and wdj.organization_id = wmt.organization_id
and we.wip_entity_id= wmt.wip_entity_id+0
and WMT.TRANSACTION_DATE>= to_date('&p_fm_date','YYMMDD')
and WMT.TRANSACTION_DATE < to_date('&p_to_date','YYMMDD') + 1
and TO_OPERATION_SEQ_NUM =
(select max(OPERATION_SEQ_NUM)
from APPS.WIP_OPERATIONS wo
where wo.wip_entity_id = wmt.wip_entity_id
and wo.organization_id = wmt.organization_id)
and WMT.to_INTRAOPERATION_STEP_TYPE+0= 3
and msi.organization_id= we.organization_id
and msi.inventory_item_id= we.primary_item_id
AND WE.WIP_ENTITY_NAME || '' LIKE '&P_1'
and ((wdj.class_code not like 'FX%' and
msi.WIP_SUPPLY_SUBINVENTORY || '' like '&P_SUB_CODE' || '%') or
(wdj.class_code like 'FX%' and
msi.attribute1 || '' like '&P_SUB_CODE' || '%'))
group by wip_entity_name,
msi.inventory_item_id,
msi.segment1,
we.organization_id,
msi.description,
wdj.START_QUANTITY,
wdj.QUANTITY_completed,
wdj.QUANTITY_SCRAPPED,
wdj.COMPLETION_SUBINVENTORY,
wdj.DATE_COMPLETED,
TO_OPERATION_SEQ_NUM,
wdj.wip_entity_id,
wdj.creation_date,
decode(substr(wdj.class_code, 1, 2),
'FX',
msi.attribute1,
msi.WIP_SUPPLY_SUBINVENTORY)
语句二:
select distinct mmt.transaction_reference
from inv.mtl_material_transactions mmt
where
mmt.organization_id=4
and substr(mmt.transaction_reference,1,2) in('JM','JW')
and mmt.transaction_date >= to_date('&p_fm_date', 'YYMMDD')
and mmt.transaction_date <to_date('&p_to_date', 'YYMMDD') + 1
合并后:
select we.wip_entity_name,
msi.inventory_item_id,
msi.segment1,
we.organization_i