How to exclude multiple records from a table

[rajendran]

i am reading a config file and getting the exclusion list with space separator like
conv custreq payment etc.
i am using the below code to loop through each exclusion list.

do i = 1 to num-entries(tt-process.reason, " "):

v-reasonexc = entry(i,tt-process.reason, " ").


for each subscriptiontran no-lock
where trandate = 03/03/14
and trantype = "start"
and not reasoncode = v-reasonexc,
each subscription no-lock where
subscriptiontran.subscriptionid = subscription.subscriptionid
and subscription.productid = tt-process.product:

put stream sout unformatted subscriptiontran.subscriptionid ","
subscriptiontran.trandate ","
subscription.productid "," skip.


end.

i am not able to get required records excluding these particular reasoncodes.
Please help me to fix thsi.

 

[jdpjamesp]

Do you know the full list of possible reason codes? If so, create a temp-table containing only the reason codes you want. If you don't know the full list the query might well be slow, but somethin like:

for each subscriptiontran no-lock
  where trandate = 03/03/14
  and trantype = "start":
  if lookup(reasoncode ,v-reasonexc," ") gt 0 then 
    next. 
  for each subscription no-lock where
    subscriptiontran.subscriptionid = subscription.subscriptionid
    and subscription.productid = tt-process.product:

 

[Stefan]

No need for next to escape the loop, include it in the query.

for each subscriptiontran
   where trandate = 03/03/14
   and   trantype = "start"
   and   lookup( reasoncode, tt-process.reason, " " ) = 0
no-lock,
each subscription
   where subscriptiontran.subscriptionid = subscription.subscriptionid
   and   subscription.productid = tt-process.product
no-lock:

   put stream sout unformatted 
      subscriptiontran.subscriptionid "," 
      subscriptiontran.trandate ","
      subscription.productid "," 
   skip.

end.