Skip to Content
0
Former Member
Apr 10, 2014 at 10:04 PM

sql not returning rows needed in the cr

27 Views

What I am trying to do is this.

table OEINHDIH is history of orders. Orders can have many order type rows. so that order 12345 can have types INT, COR etc within it.

we need the orders which are INT but don't have a correction order against it (COR COE). But when i run this sql i get only 1 ihent#. Also this has to be run in Crystal report where the user will drive this according to dates from and to which is h.IHDOCD.

select h.IHDOCD, h.IHENT#, h.IHSFX#, h.IHINV#, a.ADINTA

from astdta.OEINHDIH h

join astdta.Adressad a

on h.ihent# = a.ADENT#

where h.IHORDT = 'INT'

AND h.IHVIAC not in ('PML','FCM')

AND ADSFX# = '000'

and h.ihent# in (

select cor.ihent#

from astdta.OEINHDIH cor

where cor.IHORDT not in ('COR','COE'))