Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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 addressReceived on Wed Feb 13 2002 - 16:05:03 CST
![]() |
![]() |