Hello ,
One suggesstion i need in SQL

i have the following fields on form
Transaction Type From To
Document No. From To
Doc. Date From To
Supplier From To

Trans. type nc1 nc2 nc3
not to be considered

for this i wrote the following query

QUERY 1
select tfgld106.*, tccom020.*
from tfgld106, tccom020
where tfgld106._index1 inrange {:ttyp.f, :ninv.f}
and {:ttyp.t, :ninv.t}
and tfgld106.dcdt between :date.f and :date.t
and tccom020._index1 = {tfgld106.suno}
and tfgld106.otyp <> :ttyp.nc1
and tfgld106.otyp <> :ttyp.nc2
and tfgld106.otyp <> :ttyp.nc3
order by tfgld106.leac

selectdo
endselect


QUERY 2
select tfgld106.*, tccom020.*
from tfgld106, tccom020
where tfgld106._index1 inrange {:ttyp.f, :ninv.f}
and {:ttyp.t, :ninv.t}
and tfgld106.dcdt between :date.f and :date.t
and tccom020._index1 = {tfgld106.suno}
and ( tfgld106.otyp <> :ttyp.nc1
or tfgld106.otyp <> :ttyp.nc2
or tfgld106.otyp <> :ttyp.nc3 )
order by tfgld106.leac

selectdo
endselect


Which one is the correct one , i feel that first one is the correct one. Am i right ?
please note that if the user specified in the nc1 or nc2 or nc3 those transaction types should not be considered i mean they should be skipped. I know this can also be done using CONTINUE keyword, but i would like to know which one in the above part is correct.


Regards

Learner

The first would be correct. Since you are ANDing all three nc checks together, each must be true. Thus, otyp cannot be either of the three values. In the second query, your checks end up being worthless because they are OR'd together. What it is saying is otyp isn't nc1 OR otyp isn't nc2 OR otyp isn't nc3. Well, nothing will ever be all three at once unless nc1=nc2=nc3, so the statement in parentheses will ALWAYS be true (except for the otyp=nc1=nc2=nc3 case).

Sometimes it helps to make truth tables and lay out all of the possibilities.

I hope this helps.

Hi,

Thanks. That solved my doubt.