SQL Help

I'm somewhat new to Oracle and SQL so if this is something stupid, I apologize. I've tried reseaching this but the only manuals I have available are the O'Reilly and Oracle Press SQL and PL/SQL manuals.

The following query works in a SQL*Plus window, but will not compile in a procedure when using it to define a cursor. Here is what I have tried:

I have tried a simple select to verify that the procedure will compile and it does with a simple select. So it is not the procedure itself.

The first select part works if I put in a simple date to replace the second select statement.

The second select statement works when I supply a hard coded id to run it standalone so both selects work.

Database is 8i on HPUX 11.

I get the following error on the line containing the second select when compiling in either TOAD or PL/SQL Developer. It also will not compile in SQL*Plus

PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + mod not null others <an identifier>

Here is the query: I tried to format it a bit to make it readable.

SELECT
   cpsd_id cpsd_id

FROM
  cpsd_site_details cpsd,
  cpste_sites cpste,
  clcon_contracts clcon

WHERE cpsd.cpsd_site_id = cpste_id

      AND clcon.clcon_site_id = cpste_id
      AND clcon_condition LIKE 'Active%'
      AND clcon_status LIKE 'Installed%'
      AND clcon_contract_type = 'GP'
      AND cpsd_effective_date <= TO_CHAR(LAST_DAY(

          (SELECT MIN(clcon1.clcon_term_start_date)
           FROM   cpste_sites cpste1,
                        clcon_contracts clcon1
        WHERE  clcon1.clcon_site_id = cpste1.cpste_id
             AND  cpste1.cpste_site_key = cpste.cpste_site_key
	AND  clcon1.clcon_contract_type = 'GP') 
	 ),'DD-MON-YYYY') 

I don't mind doing my homework to figure this out, but am out of resources available to me and the other developers in this group are out of ideas as well. If someone has a recommendation of a good book that gets into this level of detail, I would appreciate it. Of course, a solution to this problem would also be appreciated.

Thanks in advance.
Mark Received on Wed Feb 13 2002 - 15:23:55 CST