New to SQL Analytics and need help

From: Scott Johnson <stj_spam_at_yahoo.com>
Date: Thu, 23 Sep 2004 09:21:51 -0400
Message-ID: <4152cb86$0$26126$bb8e7a08_at_news.usenetcompany.com>

I'm fairly new to sql analytics and need help with an relatively simple problem.

Given table:

ID      Operation Date      Old_Loc     New_Loc
100     9/1/04  12:34       B1
100     9/1/04  12:45       B2
100     9/1/04  13:30


I want to move the location down by one record so the result will be
ID      Operation Date      Old_Loc     New_Loc
100     9/1/04  12:34       B1
100     9/1/04  12:45       B2               B1
100     9/1/04  13:30                          B2

I'm using:

update tab_name
set new_loc = (select lead(old_loc) over (partition by id order by operation_dt) from tab_name);

but I'm getting Ora 1427 - Single-row subquery returns more than one row.

Like I said, I'm new to sql analytics so I may be completely off base.

Can anyone offer some insight?

Thanks,
Scott Received on Thu Sep 23 2004 - 15:21:51 CEST