Re: SQL Help

On Wed, 13 Feb 2002 15:23:55 -0600, Mark Rathbun <mark_at_NOSPAMpickerel-lake.net> wrote:

>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

Blank lines are not allowed in SQL statements. If you really want them put

--
in them for a single-line comment

also  it definitely must be  (reformatted the query to my style)
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 <= 
-- the previous to_char will diable any index on effective_date
               (select LAST_DAY(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'
               );


Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address