[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
RE: [CF-Database] Next & previous records (Oracle 9.2)
Please remove me from this mailing list
-----Original Message-----
From: Russ Michaels (Snake) [mailto:snake.lists@snakepit.net]
Sent: Tuesday, 19, October, 2004 06:05 AM
To: 'database programming'
Subject: RE: [CF-Database] Next & previous records (Oracle 9.2)
>- see footer for list info
Why bother with the outer join. Surely 2 sub-queries to get the next and
last is enough, if there is no next and last, the sub-query will return
nothing.
Or if it was me, I would just query ALL the ID's and ORDER values and store
them in a structure, and use these for reference instead of querying the
database every time.
Russ
> -----Original Message-----
> From: database-bounces@list.cfdeveloper.co.uk
> [mailto:database-bounces@list.cfdeveloper.co.uk] On Behalf Of
> Aidan Whitehall
> Sent: 18 October 2004 17:21
> To: database@list.cfdeveloper.co.uk
> Subject: [CF-Database] Next & previous records (Oracle 9.2)
>
> >- see footer for list info
> I'd bet money this is simple, but can't see the light. We
> have a table of records along the lines of:
>
> id varchar(10) PK
> section varchar(10) PK
> order numeric
> title varchar(255)
>
> with values such as
>
> 'a', 'a', 1, "Some page title"
> 'b', 'a', 2, "Another page title"
> 'c', 'a', 4, "Guess..."
>
> For any given record, I need to be able to retrieve all
> columns, plus the id and title of the record before and after
> it, based on it's 'order' value. 'order' is an integer that's
> not necessarily contiguous; hence the 1,2,4 in the example.
>
> Before we upgraded from Oracle 9.0.1 to 9.2 this weekend, I
> was doing a select that then outer joined two queries, both
> of which had subselects to get the id of the record before
> and after it. The outer join catered for those times when the
> id specified was the first or last record in the section and
> there was consequently no previous or next record. The record
> set retrieved was:
>
> id, section, order, title, previous_id, previous_title,
> next_id, next_title
>
> After the upgrade, that SQL started throwing the error:
>
> ORA-01799: a column may not be outer-joined to a subquery
>
> We've altered the query to one that is sub-optimal (four
> additional sub-queries in the initial select block) but I
> can't help thinking that the Celko-devotees out there (I'm
> hoping there's a few of you) know how to deal with this sort
> of problem in their sleep.
>
> Any pointers?
>
>
> --
> Aidan Whitehall aidanwhitehall@fairbanks.co.uk
> Macromedia ColdFusion Developer
> Fairbanks Environmental +44 (0)1695 51775
>
> ______________________________________________________________
> __________
> This e-mail has been scanned for all viruses by Star. The
> service is powered by MessageLabs. For more information on a
> proactive anti-virus service working around the clock, around
> the globe, visit:
> http://www.star.net.uk
> ______________________________________________________________
> __________
> _______________________________________________
> For info on all lists and to join/leave go to
> http://list.cfdeveloper.co.uk/mailman/listinfo
> --
>
> CFDeveloper is hosted by CFMXhosting.co.uk and
> managed/supported by Russ Michaels Thanks also to the
> following for their support:- * www.fusetalk.com *
> www.gradwell.com * www.proworkflow.com * www.helmguru.com *
> www.APYCOM.com * _______________________________________________
>
> These lists are syncronised with the CFDeveloper forum at
> http://forum.cfdeveloper.co.uk/
> Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
>
_______________________________________________
For info on all lists and to join/leave go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper is hosted by CFMXhosting.co.uk and managed/supported by Russ
Michaels
Thanks also to the following for their support:- * www.fusetalk.com *
www.gradwell.com * www.proworkflow.com * www.helmguru.com * www.APYCOM.com *
_______________________________________________
These lists are syncronised with the CFDeveloper forum at
http://forum.cfdeveloper.co.uk/
Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/