× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I have an existing sql statement that looks like this.

SELECT count(*) FROM UPRM WHERE UPSTP IN(0,9999) and UPSST NOT LIKE
 '#%' AND UPTYP NOT IN ('HG', 'PC', 'PE', 'PF', 'PH', 'PI','PO',
 'PR', 'PS', 'PX') AND UPSTS NOT IN('CD', 'RT', 'SS')

I have been asked to take all the selection criteria and put it in a control
file so that if the selection values change I don't have to change the
query.

SO I created a file to hold these values giving room for growth

Now I need to convert this SQL statement to use the new file.  I was hoping
that it would as simple as replacing the values with field names but that
didn't work.  So I've resorted to this, but I still seem to be missing
something.
I am hoping that there is an easier way to achieve this conversion.

SELECT * FROM UPRM WHERE EXISTS(SELECT * FROM QAPNOSTP00
 WHERE(UPSTP = PRMSTP1 OR UPSTP = PRMSTP2 OR UPSTP = PRMSTP3 OR
UPSTP = PRMSTP4 OR UPSTP = PRMSTP5) AND (SUBSTR(UPSST,1,1) <>
PRMSST1 and SUBSTR(UPSST,1,1) <> PRMSST2) AND (UPTYP <> PRMTYP1 and
UPTYP <> PRMTYP2 and UPTYP <> PRMTYP3 and UPTYP <> PRMTYP4 and
UPTYP <> PRMTYP5 and UPTYP <> PRMTYP6 and UPTYP <> PRMTYP7 and
UPTYP <> PRMTYP7 and UPTYP <> PRMTYP8 and UPTYP <> PRMTYP9 and
UPTYP <> PRMTYP10) and (upsts <> PRMSTS1 and upsts <> prmsts2 and
upsts <> prmsts3 and upsts <> prmsts4 and upsts <> prmsts5 and
upsts <> prmsts6 and upsts <> prmsts7 and upsts <> prmsts8 and
upsts <> prmsts9 and upsts <> prmsts10))

the fields that start with PRM are from QAPNOSTOP00

any ideas how to achieve this conversion in a more simplified manner.

Thanks

mike


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.