Home » SQL & PL/SQL » SQL & PL/SQL » analytical function help (oracle 9)
analytical function help [message #324818] Wed, 04 June 2008 03:54 Go to next message
dusoo
Messages: 41
Registered: March 2007
Member
Hi,

can someone please advice me,
how can i retrieve (for rows 17,18,19) previous not-null value in column COUNTER2 without using LAG function in ORACLE 9 ? So the value in counter2 in rows 17,18,19 will be 2094?

thanks a lot


STARTTIME M_ID RNUM COUNTER2
26. 5. 2008 10:30:00 551088 15 2128
26. 5. 2008 10:45:00 551088 16 2094
26. 5. 2008 11:00:00 551088 17
26. 5. 2008 11:15:00 551088 18
26. 5. 2008 11:30:00 551088 19
26. 5. 2008 11:45:00 551088 20 2086
26. 5. 2008 12:00:00 551088 21 2066
Re: analytical function help [message #324823 is a reply to message #324818] Wed, 04 June 2008 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use LAST_VALUE

Regards
Michel
Re: analytical function help [message #324828 is a reply to message #324818] Wed, 04 June 2008 04:26 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link see to if this is of any help to you.

http://www.orafaq.com/forum/m/316065/94420/#msg_316065

@Michael,

Unfortunately since OP is working on Oracle 9, last_value on its own will not be any of help because ignore nulls options is added only in Oracle 10g.

Regards

Raj
Re: analytical function help [message #324845 is a reply to message #324828] Wed, 04 June 2008 05:46 Go to previous messageGo to next message
dusoo
Messages: 41
Registered: March 2007
Member
LAST_VALUE is great, but using with Ignore Nulls in Ora 9 only Smile ...like u wrote.

Anyway, i came up with one solution using max.
It looks bad, but works as needed for now.

max(counter2) over (partition by m_id order by rnum rows nvl(rnum-prev_rn_counter2,1) preceding)
Re: analytical function help [message #324848 is a reply to message #324845] Wed, 04 June 2008 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure this works?
It would be fair to post the whole query and not a part including some expressions you didn't explain.
For instance, don't you use LAG to get prev_rn_counter2?

Regards
Michel
Re: analytical function help [message #324851 is a reply to message #324848] Wed, 04 June 2008 06:06 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Michael,

This will give you a better idea about the crux of the problem.

http://forums.oracle.com/forums/thread.jspa?threadID=663358&tstart=30

Regards

Raj
Re: analytical function help [message #324869 is a reply to message #324851] Wed, 04 June 2008 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why is he able to correctlty post in this forum and not here?
Why didn't he give us all information including what has already been tried and just let us waste our time?

I give up with this guy.

Regards
Michel
Re: analytical function help [message #324890 is a reply to message #324869] Wed, 04 June 2008 07:57 Go to previous messageGo to next message
dusoo
Messages: 41
Registered: March 2007
Member
Well, on the forum mentioned below, it's a different problem discussed there, where after a couple of inner selects lag's offset does not work in some cases. Most probably it's a oracle bug, as one guy replied Sad there. So there is no solution at all for that case ...

I just did not want to open new topic there.

That's why i moved in here and kindly asked, if there is a way to get a previous not-null value not using LAG in ORA9.

After S.Rajaram's suggestion to check one link, i have tried to use max() over (..rows RNUM - prev_rn preceding), which works.
The prev_rn is calculated using LAG in inner select where LAG is still working. In the last step LAG just has problem to use known offset value, because is interpreted for him as NULL...

thanks
Re: analytical function help [message #324891 is a reply to message #324890] Wed, 04 June 2008 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The prev_rn is calculated using LAG in inner select where LAG is still working

But you asked for:
Quote:
without using LAG function

in BOLD.

Regards
Michel
Re: analytical function help [message #324906 is a reply to message #324891] Wed, 04 June 2008 08:19 Go to previous messageGo to next message
dusoo
Messages: 41
Registered: March 2007
Member
Yes Smile

i dont have any problem with LAG function in my inner selects, where prev_rn is calculated for example. My problem with LAG is in the last select, where i needed to retrieve last not-null value.

That's why i asked here, if there is a way to get that value not using LAG
Re: analytical function help [message #324908 is a reply to message #324906] Wed, 04 June 2008 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you want an answer to a question you don't post.
Or rather you post a question to get an answer to a different one.
I don't think it is the most efficient way to get an answer to the actual question.

Regards
Michel
Re: analytical function help [message #324918 is a reply to message #324908] Wed, 04 June 2008 08:49 Go to previous messageGo to next message
dusoo
Messages: 41
Registered: March 2007
Member
Noo, not at all Smile

My question was

how can i retrieve (for rows 17,18,19) previous not-null value in column COUNTER2 without using LAG function in ORACLE 9 ? So the value in counter2 in rows 17,18,19 will be 2094?


And i got good answers, after which i found the solution.

I did not write why i dont want to use LAG function, because i did not want to confuse anyone.

Regards
D.
Re: analytical function help [message #324921 is a reply to message #324918] Wed, 04 June 2008 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And so you did it.

Regards
Michel
Re: analytical function help [message #324933 is a reply to message #324921] Wed, 04 June 2008 09:17 Go to previous message
dusoo
Messages: 41
Registered: March 2007
Member
sorry for confusion,
thanks for help
Previous Topic: ORA-27369: job of type EXECUTABLE failed with exit code: Unknown erro
Next Topic: ORDER BY dilemma
Goto Forum:
  


Current Time: Sat Dec 10 03:33:49 CST 2016

Total time taken to generate the page: 0.09836 seconds