>OK, using subqueries is something I'm little new at, and I cant figure out the following:
>
>SELECT handles.account,handles.handle,handles.progtype, ;
> handles.stype,styperef.ptype, HANDLES.REF_NUM ;
> from handles,styperef ;
> WHERE STYPEREF.STYPE==HANDLES.STYPE AND ;
> styperef.ref_num=handles.ref_num AND ;
> STYPEREF.PTYPE="A" AND PROGTYPE"A" ;
> AND NOT INLIST(SUBSTR(handle,1,2),"SP","DD","PN","P4") ;
> AND NOT INLIST(SUBSTR(handle,1,3),"TRD") ;
> AND NOT INLIST(handles.stype,"LR") ;
> INTO CURSOR alpha_x &&Returns 7809 records and is correct
>
>SELECT * FROM ALPHA_X WHERE NOT EXISTS( ;
> SELECT * FROM ALPHA_X WHERE stype="WL " AND progtype="NP") ;
> INTO CURSOR ALPHATMP &&Returns Empty Result
>
>If I run the subquery alone I get 5155 records with stype="WL " AND progtype="NP" Therefore my ALPHATMP cursor should have 2654 records, not zero. What did I do wrong this time? Note - this code currently running in FPW 2.6, couldnt test under 5.0a until next week because Monthend processing is running.

Roxy,

I think you need to add aliases to that second query and add a condition, like this:


SELECT * FROM ALPHA_X A WHERE NOT EXISTS( ;
SELECT * FROM ALPHA_X B WHERE A.Somefield = B.Somefield AND B.stype="WL " AND B.progtype="NP") ;
INTO CURSOR ALPHATMP

I think that ought to do it.

How come no new picture of you from DevCon (with hat, of course)?

George