I am trying to re-write the following SQL statement to eliminate the "where" part of the statement, as we need to use the value in single-cell table. However, I am having trouble getting the exact results, and I think the problem is with my joins.
Original SQL:
1611 Row(s) affected
Modified SQL:
Original SQL:
Code:
select oi.iep_offer_nbr,p.old_item_nbr,p.item_status_cd
from iep_offer_invstmnt oi,iep_prod p
where oi.iep_offer_nbr = 3200024
and oi.old_item_nbr = p.old_item_nbr
and p.ITEM_STATUS_CD NOT IN ( 'O','S','A' )
Modified SQL:
Code:
SELECT DISTINCT
ADMIN.IEP_PARALLEL_PRCSNG.IEP_OFFER_NBR,
ADMIN.IEP_PROD.OLD_ITEM_NBR,
ADMIN.IEP_PROD.ITEM_STATUS_CD
FROM
ADMIN.IEP_PARALLEL_PRCSNG INNER JOIN ADMIN.IEP_OFFER_INVSTMNT ON (ADMIN.IEP_OFFER_INVSTMNT.IEP_OFFER_NBR=ADMIN.IEP_PARALLEL_PRCSNG.IEP_OFFER_NBR)
WHERE
ADMIN.IEP_PROD.ITEM_STATUS_CD NOT IN ( 'O','S','A' )