Re: sql ?

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

What about an exception join on the columns with the values? The LIKE
predicate is tougher.

SELECT count(*) from UPRM U1 join QAPNOSTP00 Q1 on U1.upstp=q1.prmstp
exception join qapnostp00 q2 on .... etc.

Some variant of the above. EXCEPTION JOIN takes care of the NOT IN, JOIN
takes care of the IN.

Alternatively, if this is in RPG, build the statement and execute dynamically.

At 01:25 PM 3/28/02 -0500, you wrote:
>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
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@midrange.com
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>or email: MIDRANGE-L-request@midrange.com
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.