| HELp IMP [message #8155] |
Wed, 30 July 2003 14:53  |
pg
Messages: 12
Registered: May 2002
|
Junior Member |
|
|
hi gurus, need one help ..
i have data in a table in following format
Date amount
====== =======
APR-02 100
MAY-02 200
JAN-03 400
I Want to fetch data like this
date 1 amount1 date2 amount2
MAy-02 200 APR-02 100
JAN-03 400 MAY-02 200
I.E DATE1 AND AMOUNT 1 IS ROW +1 AND DATE2 AND AMOUNT2 IS ACTUAL ROW
CAN ANY ONE PLEASE SUGGEST A SINGLE SIMPLE QUERY FOR THE SAME !!!
pL HELP
|
|
|
|
| Re: Accessing data in next row [message #8156 is a reply to message #8155] |
Wed, 30 July 2003 17:35  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
"HELp IMP" is not a very helpful subject. You will need the analytic functions available in 8.1.6 and up:
sql>select * from test order by some_date;
SOME_DATE AMOUNT
--------------------- ---------
04/01/2002 12:00:00am 100
05/01/2002 12:00:00am 200
01/01/2003 12:00:00am 400
3 rows selected.
sql>select *
2 from (select to_char(lead(some_date) over (order by some_date), 'MON-RR') date1,
3 lead(amount) over (order by some_date) amount1,
4 to_char(some_date, 'MON-RR') date2,
5 amount amount2
6 from test)
7 where date1 is not null;
DATE1 AMOUNT1 DATE2 AMOUNT2
------ --------- ------ ---------
MAY-02 200 APR-02 100
JAN-03 400 MAY-02 200
2 rows selected. |
|
|
|
| Re: Accessing data in next row [message #8157 is a reply to message #8156] |
Wed, 30 July 2003 18:22 |
pg
Messages: 12
Registered: May 2002
|
Junior Member |
|
|
Thanks Todd,
But i have to use the query in a tool so cant use analytic functions like Lead ,Over
is ther any other simpler way of doing it !!
thanks i really appriciate ur help
|
|
|
|
| Re: Accessing data in next row [message #8159 is a reply to message #8157] |
Wed, 30 July 2003 18:51 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Why can't you use the query in your tool? The tool doesn't process the query - Oracle does. I don't understand.
There is no other way of efficiently doing this type of query.
|
|
|
|
| Re: Accessing data in next row [message #8163 is a reply to message #8159] |
Wed, 30 July 2003 20:03 |
pg
Messages: 12
Registered: May 2002
|
Junior Member |
|
|
Todd
the problem is i have to implement it using a third party reporting tool BRIO and it does not have these advace features to support.. its a GUI thing...
so need a simpler stuff
|
|
|
|
| Re: Accessing data in next row [message #8172 is a reply to message #8156] |
Thu, 31 July 2003 06:35 |
Jeanne
Messages: 26
Registered: June 2003
|
Junior Member |
|
|
Hi, Todd,
I just want to learn the analytic functions so I test it. But I got the result not like you got. The result is:
DATE1 AMOUNT1 DATE2 AMOUNT2
APR-03 100 JAN-03 400
MAY-03 200 APR-03 100
I do not know why. Thanks very much.
Jeanne
|
|
|
|
|
|
| Re: Accessing data in next row [message #8176 is a reply to message #8174] |
Thu, 31 July 2003 13:21 |
Jeanne
Messages: 26
Registered: June 2003
|
Junior Member |
|
|
Thank you very much, Todd.
Here is my data in a test table:
SOME_DATE AMOUNT
04/01/2003 00:00:00 100
05/01/2003 00:00:00 200
01/01/2003 00:00:00 400
And my SQL is:
select *
from (select
to_char(lead(some_date)
over (order by some_date), 'MON-RR') date1,
lead(amount) over (order by some_date) amount1,
to_char(some_date, 'MON-RR') date2,
amount amount2
from testtab)
where date1 is not null;
My result is:
DATE1 AMOUNT1 DATE2 AMOUNT2
APR-03 100 JAN-03 400
MAY-03 200 APR-03 100
It seems extract like what you did. But I got different result. What's wrong I did?
Thanks a lot.
Jeanne
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
