((Don't have to use Access...can use Access's 'pass-through' query
type))
Connect-by clause?? Seriously lost.....any syntax please....
2 tables:
Group_Upload Site_History
------------ ------------
Group_Num Site_Key
Site_Key New_Site_Key
A user will upload a group of Site Keys in the Group_Upload table, and
specify the group using Group_Num. What I need help w/ is as
follows...
(probably using connect by clause??)
If the Tables looked like this:
Site_History | Group_Upload
---------------------- | ---------------------
Site_Key | New_Site_Key | Group_Num | Site_Key
1 | 2 | ABC44 | 1
2 | 3 | |
3 | 4 | |
User specifies Group_Num "ABC44" and the Site_Key in Group_Upload
table was "1", then it would return "4" (don't need 2 or 3 nodes, just
the final "Leaf"...
Any gurus help w/ this? Thanks (more than you could know!)
treoo0_at_yahoo.com (Treo) wrote in message news:<c631ca9c.0207171744.14a989a0_at_posting.google.com>...
> 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 Thu Jul 18 2002 - 08:09:20 CDT
