Greetings. I'm trying to do something to a table, contained within an
Oracle dB, which looks exactly like Daniel's table below:
parent child
1 2
2 3
3 4
What I need to do: I need to find parent1 (a user record) and return
only child4. If parent 1 doesn't exist in the table, I'll spit that
one back out (example: Parent 45 isn't in the above table, so I want
to spit it back out to the user.
Problem: I have to do this in Access 97. I've tried creating a loop,
and assigning variables, but to no avail.
Looks like this: 2 tables
User_Table Oracle_Table
---------- ------------
Parent_Num Old_Parent_Num | New_Parent_Num
Do until rs.EOF
If User_Table.Parent_Num = Oracle_Table.Old_Parent_Num
return Oracle_Table.New_Parent_Num
If New_Parent_Num (stored in a 3rd table??) =
Oracle_Table.Old_Parent_Num
return Oracle_Table.New_Parent_Num
Else
return User_Table.Parent_Num
rs.movenext
Loop
Does this make any sense? Is there a query I can write which will
eliminate the need to loop through the table? (I can't get the loop to
work...it only spits out the first child)
I think I can run an "sql-passthrough" query in Access, if I can
utilize the oracle functionality below.
Super-Lost! Any help would be Great!
Thanks,
Treo
Daniel <dlUNSPAMrubin_at_yahoo.com> wrote in message news:<3D34B3BF.73B38FF9_at_yahoo.com>...
> Thanks for your suggestion! One question in response below--you say you can put reasonable
> number (20 for example) of descendants into one line using sum/decode/group by tricks,
> e.g., making lines into columns. I'm not familiar with this. Do you have an example of this
> or pointer to where I can see how to do this?
>
> Thanks,
> Daniel
>
> (To reply to me directly, please remove the UNSPAM part from my email)
>
> Alex Filonov wrote:
>
> > Daniel <dlUNSPAMrubin_at_yahoo.com> wrote in message news:<3D33755B.105CC0CD_at_yahoo.com>...
> > > I'm trying to write a query on oracle 8.1.7 that will give me
> > > grandparent-child assocations. For example, if this is my table:
> > >
> > > parent child
> > > 1 2
> > > 2 3
> > > 3 4
> > >
> > > I want a query that shows all descendant of "parent". Such a query
> > > should produce:
> > >
> > > parent child
> > > 1 2
> > > 2 3
> > > 3 4
> > > 1 3
> > > 1 4
> > > 2 4
> > >
> > > I've tried using oracle hierarchical queries (START and CONNECT BY), but
> > > they don't give grandparent-child tuples. For example, here's what I get
> > > with this query:
> > >
> > > SELECT * FROM mytable
> > > START WITH parent_id = 1
> > > CONNECT BY PRIOR child = parent;
> > >
> > > PARENT CHILD
> > > ---------- ----------
> > > 1 2
> > > 2 3
> > > 3 4
> >
> > To get grandparent-chils tuples:
> >
> > select p.parent grandparent, c.child grandchild
> > from
> > ( SELECT * FROM mytable
> > START WITH parent_id = 1
> > CONNECT BY PRIOR child = parent) p,
> > (SELECT * FROM mytable
> > START WITH parent_id = 1
> > CONNECT BY PRIOR child = parent) c
> > where p.child = c.parent
> >
> > > But this doesn't give all descendants. What query do I need to write
> > > to get all
> > > descendants?
> >
> > It DOES give you all descendats. Maybe you want them all in one line, but it's
> > a different question. You can put reasonable number (20 for example) of
> > descendants into one line using sum/decode/group by tricks. Ever tried to turn
> > report, making lines into columns?
> >
> > > thanks
> > >
> > > (To reply to me directly, please remove the unspam string from my email)
Received on Wed Jul 17 2002 - 20:44:43 CDT
