Re: HELP - Order by problem

From: Keith McKendry <mckendry_at_execpc.com>
Date: 1998/08/19
Message-ID: <35DAEF86.3A11799@execpc.com>#1/1

First a some observations:

1. You can only return one row in the sub-query (and you do with rownum <2), thus any order by in you sub-query is pointless.
2. Order by is processed after all rows are returned. I suspect that you want the min(person_id) and the corresponding role type and your statement will not return that.

Now for an answer:

Oracle (sql*plus) is expecting the ')' before the 'order by' since the parser recognizes that the sub-query can only return one row.

Now a solution:
> SQL> update conv_observation_tables cot set ( person_id,role_type ) =
> 2 ( select person_id, role_type
> 3 from spi_survey_surveyors s1
> 4 where s1.spi_project_dc_id = cot.spi_project_dc_id
> 5 and person_id = (select min(person_id) from
 spi_survey_surveyors s2
> 6 where s2.spi_project_dc_id =
 cot.spi_project_dc_id))
> 7 where observation_line_id = 4180;

Keith McKendry
Whittman-Hart, Inc.

EXE wrote:

> > I'm using Oracle server 7.3.3.0.0 Why am I getting the
> > following error in sql/plus
> >
> > SQL> update conv_observation_tables cot set ( person_id,role_type )
 =
> > 2 ( select person_id, role_type
> > 3 from spi_survey_surveyors s1
> > 4 where s1.spi_project_dc_id = cot.spi_project_dc_id
> > 5 and rownum < 2
> > 6 order by 1, 2 )
> > 7 where observation_line_id = 4180;
> > order by 1, 2 )
> > *
> > ERROR at line 6:
> > ORA-00907: missing right parenthesis

>

> Why is your second order by clause after the semicolon? And what is
> the
> asterisk doing after that? I am not going to try to guess what's wrong
> as
> it appears that you incorrectly copied the SQL.

>

> Regards,

>
> Dan
Received on Wed Aug 19 1998 - 00:00:00 CDT