| Author |
Very perplexing select Problem
|
|
|
| Calling all SQL experts!
How can one do a select for this:
I have a table with (for brevity) 2 columns. RecID and ParentID.
All absolute or generation 0 PARENT records will have a ParentID of 0
(zero) but their children will have their parentID pointed to an
existing parent's RecID (a parent of any generation).
I want to do a select to list out the parents with all their children
and grandchildren under them and then start listing the next parent and
all the children and grand children under them etc.
example
RECID ParentID
PARENT 1 0
child 2 1 child of p1
child 6 1 child of p1
child 4 6 child of child
child 5 4 child of child of child
PARENT 3 0 next parent of gen 0
child 7 3 child of p3 or RecID 3
etc.. I want to list it in that order using SQL select.
How may I solve this using SQL SELECT and perhaps HAVING?
!!!!IMPORTANT!!!!
Dont assume that RECIDs will be in any order. RECIDs are just assigned
by a sequence as they are entered. And children are not guaranteed to
have a recID immediately or sequentially next to their parents recID -
only that Child recIDs will be greater than the parent's recCIDs since
they can only be entered after their parents records are entered.
Any help from the SQL experts and overlords appreciated!!
| |
|
| try the following SQL
select level,decode(level,1
,'PARENT','CHILD'), rec_id, parent_id from
TABLE_REC start with parent_id = 0
connect by prior rec_id = parent_id
This will give hierarchical ordering of parent/children/grandchildren
etc etc (level will tell you what hierarchical level they are at) ..
Hope it helps
Regards,
Gopal Krishan
| |
|
| Is this SQL statement portable? I am sorry but I should have mentioned
that. Powers to be require that the SQL is portable to other RDBMS.
After the fiasco here 5 years ago with developers using SQL Server
specific features no one wants to repeat this. That app become
impossible to port to other backend databases if customers want your app
against another database.
Oracle is great and is our primary choice. But if the customer wants to
use your app against their own db that they pay for like DB2 or a free
one then you have to allow for that. I think even SAP allows you the
flexibility to use many databases.
Can this be done anyother way?
Kris wrote:
> try the following SQL
>
> select level,decode(level,1
,'PARENT','CHILD'), rec_id, parent_id from
> TABLE_REC start with parent_id = 0
> connect by prior rec_id = parent_id
>
> This will give hierarchical ordering of parent/children/grandchildren
> etc etc (level will tell you what hierarchical level they are at) ..
>
> Hope it helps
>
> Regards,
> Gopal Krishan
>
| |
| DA Morgan 2005-09-17, 3:23 am |
| BN wrote:
> Is this SQL statement portable? I am sorry but I should have mentioned
> that. Powers to be require that the SQL is portable to other RDBMS.
It is not and neither is just about any other SQL statement that will
perform decently. I'd suggest the "Powers that be" learn something
about the subject before pontificating.
Perhaps you could start them off with a copy of Tom Kyte's book "Expert
one-on-one Oracle."
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
| Serge Rielau 2005-09-17, 9:23 am |
| BN wrote:
> Is this SQL statement portable? I am sorry but I should have mentioned
> that. Powers to be require that the SQL is portable to other RDBMS.
>
> After the fiasco here 5 years ago with developers using SQL Server
> specific features no one wants to repeat this. That app become
> impossible to port to other backend databases if customers want your app
> against another database.
>
> Oracle is great and is our primary choice. But if the customer wants to
> use your app against their own db that they pay for like DB2 or a free
> one then you have to allow for that. I think even SAP allows you the
> flexibility to use many databases.
>
> Can this be done anyother way?
To do it another way you would need to code it "on-foot" which would
indeed be very slow.
Both DB2 for LUW and SQL Server 2005 support SQL Standard recursion.
There is however a mapping that allows to get from CONNECT BY to the SQL
Standard. So if you isolate this query in a function or procedure you
can limit the impact when porting.
I actually plan to describe the mapping soon at IBM developerworks DB2,
so when the time comes to port do DB2 you'll have instructions there.
Cheers
Serge
PS: Contrary to others I believe that a lot can be achieved sticking
with standard SQL. It's the more exotic SQL features where divergence
happens. Then the trick is encapsulation.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
| |
| William Robertson 2005-09-18, 8:23 pm |
| BN wrote:[color=darkred
]
> Is this SQL statement portable? I am sorry but I should have mentioned
> that. Powers to be require that the SQL is portable to other RDBMS.
>
> After the fiasco here 5 years ago with developers using SQL Server
> specific features no one wants to repeat this. That app become
> impossible to port to other backend databases if customers want your app
> against another database.
>
> Oracle is great and is our primary choice. But if the customer wants to
> use your app against their own db that they pay for like DB2 or a free
> one then you have to allow for that. I think even SAP allows you the
> flexibility to use many databases.
>
> Can this be done anyother way?
>
> Kris wrote:
Better not use joins then ;-)
http://oracle-wtf.blogspot.com/2005...-explained.html
| |
|
| BN wrote:
> Is this SQL statement portable? I am sorry but I should have mentioned
> that. Powers to be require that the SQL is portable to other RDBMS.
Oh bugger. That means the PTB (powers that be) have just thrown away
any use of PL/SQL or any other basic features of Oracle.
> Oracle is great and is our primary choice. But if the customer wants to
Doesn't look like it. The way the PTB want to use it, they might
as well be using a flat file...
> one then you have to allow for that. I think even SAP allows you the
> flexibility to use many databases.
"SAP" and "flexibility" should never be used in the same sentence...
> Can this be done anyother way?
Yes. Encapsulation. Use vanilla SQL for the compatible stuff,
use encapsulation for what isn't. Not a new concept either:
it's been around since, let me see, early 80s?
BTW, it's also what SAP does...
| |
| xhoster@gmail.com 2005-09-19, 1:24 pm |
| "Noons" <wizofoz2k@yahoo.com.au> wrote:
> BN wrote:
>
> Oh bugger. That means the PTB (powers that be) have just thrown away
> any use of PL/SQL or any other basic features of Oracle.
>
>
> Doesn't look like it. The way the PTB want to use it, they might
> as well be using a flat file...
This is one of the favorite refrains of the Oracle junkies, and of course
is patently untrue.
There are many reasons to use Oracle-specific features. But even
if you foolishly or wisely decide not to use them, what is left is still
far superior to a flat file. "...might as well be using MySQL" maybe I
could agree with, but definitely not a flat file.
Xho
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
|
|
|
|