Re: Ordered subquery

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 28 Mar 2007 16:36:03 -0700
Message-ID: <1175124963.888960.292150@y80g2000hsf.googlegroups.com>

On Mar 28, 4:52 am, xas..._at_gmail.com wrote:
> Hi all!
>
> I am trying to run this query and I am getting an error I can't
> understand:
>
> select
> t1.PER_COD,
> t1.DATASITUACIO as DATA_ALTA,
> (select t2.DATASITUACIO
> from tmpINTERVALS as t2
> where
> t2.SITUACIO = 'B'
> and t1.PER_COD = t2.PER_COD
> and t2.DATASITUACIO > t1.DATASITUACIO
> and t2.ROWNUM = 1
> order by t2.DATASITUACIO) as DATA_BAIXA
> from
> tmpINTERVALS as t1
> where
> t1.SITUACIO = 'A'
> and
> t1.DATA_BAIXA is null or t1.DATA_BAIXA >= sysdate - 365
>
> The error is "missing right parenthesis".
>
> Any idea?
>
> Thanks a lot,
>
> Xavi

There very likely is a better way to accomplish your task. Regarding the question of why you are receiving the error message, a little test on Oracle 10.2.0.3:
SQL> SELECT
  2 'MONDAY'
  3 FROM
  4 DUAL AS T1;
  DUAL AS T1
       *
ERROR at line 4:
ORA-00933: SQL command not properly ended

SQL> SELECT
  2 'MONDAY'
  3 FROM
  4 DUAL T1; 'MONDA

---

MONDAY SQL> SELECT
  2 'MONDAY',
  3 (SELECT
  4 'TUESDAY'
  5 FROM
  6 DUAL AS T1)
  7 FROM
  8 DUAL T1;
    DUAL AS T1)
         *
ERROR at line 6:
ORA-00907: missing right parenthesis

SQL> SELECT
  2 'MONDAY',
  3 (SELECT
  4 'TUESDAY'
  5 FROM
  6 DUAL T1)
  7 FROM
  8 DUAL T1; 'MONDA (SELECT
------ -------
MONDAY TUESDAY If you look carefully at the SQL statements that do not work, and compare to those that do, you likely will find the problem quickly.

I was able to pick out LAG/LEAD from Carlos' post - that looks to be a better approach.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Mar 28 2007 - 18:36:03 CDT