Home » SQL & PL/SQL » SQL & PL/SQL » Get Next ID
Get Next ID [message #217295] Thu, 01 February 2007 09:31 Go to next message
toastmax
Messages: 16
Registered: November 2006
Junior Member
I found this excellent code to find out the next record in a dataset, unfortunatly I having a little trouble getting it going.

This is the statement :

select 
sh.student, 
sh.hist_id, 
shn.next_id
from  
stud_hist sh, 
(SELECT
a.STUD_ID,
CASE WHEN SIGN(a.HIST_ID - sh.hist_id) > 0 THEN min(a.HIST_ID) 
WHEN SIGN(a.HIST_ID - sh.hist_id) > 0 THEN max(a.HIST_ID) END NEXT_ID
FROM STUD_HIST a
WHERE a.STUD_ID = sh.student
AND a.HIST_ID <> sh.hist_id
and SIGN(a.HIST_ID - sh.hist_id) > 0
GROUP BY a.STUD_ID, SIGN(a.HIST_ID - sh.hist_id)
ORDER BY SIGN(a.HIST_ID - sh.hist_id)) shn
where
sh.stud_id = shn.stud_id
and sh.stud_id = 70968
and sh.hist_id = 128504


But I am getting a ERROR at line 16:
ORA-00904: "SH"."HIST_ID": invalid identifier error, can anyone see what stupid mistake I have made?

Many thanks

Re: Get Next ID [message #217296 is a reply to message #217295] Thu, 01 February 2007 09:35 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I take it you have created the STUD_HIST table and it has the correct columns ?
Re: Get Next ID [message #217300 is a reply to message #217296] Thu, 01 February 2007 10:25 Go to previous messageGo to next message
toastmax
Messages: 16
Registered: November 2006
Junior Member
Yup

Heres whats in the table :

LAST_UPDT
UPDATED_BY
BASE_ID
END_DATE
HIST_AMEND
HIST_TIME
HIST_USER
REG_BASE
RESIDENCE
RJOIN
RLEAVE
START_DATE
STUD_ID
HIST_ID
D_PER_WEEK
REG2_BASE
REGTYPE
BOARDER
GUEST
Re: Get Next ID [message #217303 is a reply to message #217295] Thu, 01 February 2007 10:34 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The mistake is here:

ORDER BY SIGN(a.HIST_ID - sh.hist_id)) shn

It references table "sh", but that is not part of inline view and you can't link it back to other "sh" because it's not a subquery.

Without really knowing what you're trying to do, I can't comment further. It might work if you changed it to an inline select.
Previous Topic: difference between timestamp
Next Topic: IN function
Goto Forum:
  


Current Time: Tue Dec 06 02:25:33 CST 2016

Total time taken to generate the page: 0.25056 seconds