I have to develop a report that has to reconcile finance transactions. A first select selects a part of the records. After that I have to make a selection again on the table but without the records from the first selection.

I did use a 'not exists' for that. However the not exists acts very weird. Here is the select that I use:


|* Manual transactions
select tfgld106.*
where tfgld106.leac = "099500 "
and ((tfgld106.fyer = :fyer.f and tfgld106.fprd >= :fper.f)
or tfgld106.fyer > :fyer.f)
and ((tfgld106.fyer = :fyer.t and tfgld106.fprd <= :fper.t)
or tfgld106.fyer < :fyer.t)
and tfgld106.dim5 <> "RECONCILED"
and tfgld106.otyp <> "230"
and tfgld106.otyp <> "221"
and not exists (
select tfgld106sp.*
from tdsls401, cisli245, tfgld418, tfgld106 tfgld106sp
where tfgld106sp.leac = "099500 "
and tdsls401._index1 between { :orno.f } and { :orno.t }
and tdsls401.citg between :citg.f and :citg.t
and ((tfgld106sp.fyer = :fyer.f and tfgld106sp.fprd >= :fper.f)
or tfgld106sp.fyer > :fyer.f)
and ((tfgld106sp.fyer = :fyer.t and tfgld106sp.fprd <= :fper.t)
or tfgld106sp.fyer < :fyer.t)
and cisli245._index3 = { cisli.stat.posted, :curr.comp,
cisli.ortp.sales, tdsls401.orno, tdsls401.pono,
tdsls401.sqnb, tdsls401.shpm }
and tfgld418._index2 = { :curr.comp, tdsls401.cprj,
tctror.sli.sales, tdsls401.orno, tdsls401.pono }
and tfgld418.sint = tfgld.sint.posted
and tfgld418.frot = tcfin.frto.to
and tfgld106sp.otyp = tfgld418.ttyp
and tfgld106sp.odoc = tfgld418.docn
and tfgld106sp.olin = tfgld418.lino
and tfgld106sp.dim5 <> "RECONCILED"
and tdsls401.dqua > 0
and tfgld106sp.otyp <> "230"
and tfgld106sp.otyp <> "221"
)


If it don't include the where clause otype <> "230" and otyp <> "221" in both select parts it doesn't find anything!

Anyone has an idea what I am doing wrong?

Hi Han,

could it be that by inserting the where otyp <> "230" ... the inner select allways returns nothing, hence you get the result of:
select tfgld106.*
where tfgld106.leac = "099500 "
and ((tfgld106.fyer = :fyer.f and tfgld106.fprd >= :fper.f)
or tfgld106.fyer > :fyer.f)
and ((tfgld106.fyer = :fyer.t and tfgld106.fprd <= :fper.t)
or tfgld106.fyer < :fyer.t)
and tfgld106.dim5 <> "RECONCILED"
and tfgld106.otyp <> "230"
and tfgld106.otyp <> "221"
and True

and if you get it out, it allways finds something, that probably does not have anything to do with the outer select, hence you have the equivalent of

select tfgld106.*
where tfgld106.leac = "099500 "
and ((tfgld106.fyer = :fyer.f and tfgld106.fprd >= :fper.f)
or tfgld106.fyer > :fyer.f)
and ((tfgld106.fyer = :fyer.t and tfgld106.fprd <= :fper.t)
or tfgld106.fyer < :fyer.t)
and tfgld106.dim5 <> "RECONCILED"
and tfgld106.otyp <> "230"
and tfgld106.otyp <> "221"
and False

Maybe I'm overlooking something, but it looks 2 me like the link between the inner and the outher selects is missing.

Don't have a BAAN env. at hand, hence just a generic explaination of what I mean with that link:

select table.field1 :var1 , table.field2 :var2
from table
where <conditions>
and not exists (
select tablealias.*
from table tablealias
where <conditions>
and tablealias.field1 = :var1 and tablealias.field2 = :var2)

of course field1 and field2 are some unique index fields....

Hope this helps,

Cheers,
rodichen

Hi Hans,

I think I would split the query into two parts: your outer query unchanged, and then an inner query for each record found. This inner query then uses a selectempty to get the records you need....



|* Manual transactions
select tfgld106.*
where tfgld106.leac = "099500 "
and ((tfgld106.fyer = :fyer.f and tfgld106.fprd >= :fper.f)
or tfgld106.fyer > :fyer.f)
and ((tfgld106.fyer = :fyer.t and tfgld106.fprd <= :fper.t)
or tfgld106.fyer < :fyer.t)
and tfgld106.dim5 <> "RECONCILED"
and tfgld106.otyp <> "230"
and tfgld106.otyp <> "221"
selectdo


select tdsls401.*, cisli245.*, tfgld418.*
from tdsls401, cisli245, tfgld418
where tdsls401._index1 between { :orno.f } and { :orno.t }
and tdsls401.citg between :citg.f and :citg.t
and cisli245._index3 = {cisli.stat.posted, :curr.comp,
cisli.ortp.sales, tdsls401.orno, tdsls401.pono,
tdsls401.sqnb, tdsls401.shpm }
and tfgld418._index2 = { :curr.comp, tdsls401.cprj,
tctror.sli.sales, tdsls401.orno, tdsls401.pono }
and tfgld418.sint = tfgld.sint.posted
and tfgld418.frot = tcfin.frto.to
and tfgld418.ttyp = :tfgld106.otyp
and tfgld418.docn = :tfgld106.odoc
and tfgld418.lino = :tfgld106.olin
and tdsls401.dqua > 0
selectdo

| Do nothing...

selectempty

| These are the records you want....





endselect

endselect


I haven't tried this - firstly because selecting on tfgld106 takes FOREVER here, but mainly 'coz I'm too lazy...


HTH, R