Need help with complex subquery

I've built the following SQL statement (using Visual Basic to access an Oracle 7.3.3 database) which retrieves the data I want:

SELECT PSF_ASSY, PSF_FIND_NBR, PSF_PART, PSF_SHF_KEY, PSF_QPA FROM "sec_psf" WHERE (PSF_COMMENTS LIKE 'SC=EC%') AND ((PSF_START <= ? AND (PSF_DATE_STOP >= ? OR PSF_DATE_STOP IS NULL)) OR PSF_START IS NULL AND PSF_DATE_STOP IS NULL)) Now, to complicate things, I need to have the query create a new column "IS_ASSY" that will be set to "Y" for each record where PSF_PART is found in the PSF_ASSY column anywhere in this table (not PSF_PART=PSF_ASSY for the current record). If PSF_PART is not found in the PSF_ASSY column, the new column should be Null.

It seems like this should be possible using some kind of subquery in the SELECT statement, but I'm new to SQL and Oracle and need some help.

Also, if you have any suggestions on how I can improve the performance of the above SQL query I'd appreciate it (it's slow now and will get worse if I add a subquery).

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Dec 02 1998 - 09:51:12 CST