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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] = En nombre de Stephane Faroult Enviado el: Martes 18 de Mayo de 2004 16:27 Para: oracle-l@xxxxxxxxxxxxx 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@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------