Re-writing SQL statement (1 Viewer)

[laxster]

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:

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'  )

1611 Row(s) affected

Modified SQL:

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'  )

 

[mdlueck]

I am trying to re-write the following SQL statement to eliminate the "where" part of the statement


Modified SQL:

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)
[COLOR=Red]WHERE
  ADMIN.IEP_PROD.ITEM_STATUS_CD  NOT IN  ( 'O','S','A'  )[/COLOR]

rrrrr?????

 

[laxster]

rrrrr?????

Sorry, I should have clarified that - the IEP_OFFER_NBR should not appear in the where statements any longer, as we made a table that will contain the value we want for that for the purpose of automating many of the audits we do.

 

[mdlueck]

However, I am having trouble getting the exact results, and I think the problem is with my joins.

Modified SQL:

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)

Well this new JOIN reads that only records will be considered that have matching values in both the ADMIN.IEP_OFFER_INVSTMNT and ADMIN.IEP_PARALLEL_PRCSNG tables.

Is that how your data really is?

LEFT JOIN would allow the right table to not have the matching record and the left table record would still be selected.

etc... lots of room for creativity. Just depends on what you are wanting to ask the DB. You have not made that clear enough yet.