RE: SQL query

From: Huascar Espinoza <hespinoza_at_superele.gov.bo>
Date: Wed, 19 May 2004 10:00:58 -0400
Message-ID: <B2DC72F71B99474B8CCA0C3611AAC2A81E44CE@correo.super.gov.bo>

Thank you! But I have problems to SUM(X-LAG(X,1)): "ORA-30483: windows = functions are not allowed here". How can I sum or compare (after WHERE) = fields with analytical function LAG or LEAD?

Thanks!

Hu=E1scar

-----Mensaje original-----
De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] = En nombre de Stephane Faroult
Enviado el: Martes 18 de Mayo de 2004 16:27 Para: oracle-l_at_freelists.org
Asunto: Re: SQL query

Huascar Espinoza wrote:

>=20

> How can I get the following statistics in singles SQL queries:

>=20

> Table has a sequencing number field (NRO_CORREL) and a date field =3D
> (DATE_REF). Example (ORDER BY DATE_REF):

>=20


> NRO_CORREL                      DATE_REF

> =

=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D= 3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D
> =3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D
> 1 date1
> 2 date2
> 3 date3
> 5 date4
> 6 date5
> 4 date6
> 8 date7
> 9 date8

>=20

> I need to find:

>=20

> 1) Qty. and list of regs. with lacked NRO_CORREL in the sequence
> 2) Qty. and list of regs. With disordered NRO_CORREL

>=20

> Thank you!

>=20

> Hu=3DE1scar
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

Huascar,

   Have a look at the lag() and lead() analytical functions. Order by date in the window attribute of the function, and compare NRO_CORREL to lag(NRO_CORREL).

--=20
Regards,

Stephane Faroult
Oriole Software

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed May 19 2004 - 08:57:12 CDT