Re: another can this be done with sql

[Chuck Morehead <cbmorehead@xxxxxxxxxx>]

Mike here is another option.

Let's assume you set up three files:
SOSFILE with field SOSTAT to contain values to exclude
ACSFILE with field ACCTSTAT to contain values to exclude
SOTFILE with field SOTYPE to contain values to include

select * from SOFILE,ACCTFILE where soact = acctact  and sostat not in
(select sostat from SOSFILE)  and acctstat not in (select acctstat from
ACSFILE) and sotype in (select sotype from SOTFILE)

Since these three new files should have very few records this should have
decent performance.  But the join query (in Bruce's response) would do
better if the files had a large number of records.  Anyway, this is just
another approach.

Chuck


 <snip>
> i have an existing query that looks like this
>
>
> select * from SOFILE,ACCTFILE where soact = acctact  and sostat not in
('Y',
> 'C')  and acctstat not in ('AC', 'PN') and sotype in('PEDLV', 'PDLV')
>
> this works fine.
>
> but i've been tasked with trying to make it more flexible.  ie status
codes
> change etc.  So i created a file that has the possible combinations of
> Sostat, acctstat, and sotype(maybe i should have 3 files)  ie Y,AC,PEDLV
> Y,PN,PEDLV
>
<snip>