can access do Hierarchical queries

  • Thread starter Thread starter Guest
  • Start date Start date
Hi
What is access equivalent code for hierarchical query if oracle's is

SELECT
*
FROM
Connection
START WITH
Connection.STATION1 = :STATION1 AND
Connection.STATION2 = :STATION2 AND
Connection.TYPE = :TYPE AND
Connection.ID_NUMBER = :ID_NUMBER
CONNECT BY
Connection.STATION1 = prior TRIBUTARY_STATION1 AND
Connection.STATION2 = prior TRIBUTARY_STATION2 AND
Connection.TYPE = prior TRIBUTARY_TYPE AND
Connection.ID_NUMBER = prior TRIBUTARY_NUMBER


regards
chris
 
Chris said:
What is access equivalent code for hierarchical query if oracle's <<snips>>
Click to expand...

You don't speak SHAPE and I don't speak Oracle. How about stating your
problem in a common language e.g. English :)
 
Hi

How would one extract hierarchal information from an table if the table
contains the following fields
Lower_object_id
Higher_object_id

eg data within the table
Lower_object_id Higher_object_id
1 2
2 3
3 4
a b
b c

the hierachical query would have extracted all the numeric recordsets if the
param that was passed was 1

Could you please provide us with an example

chris
 
Chris said:
How would one extract hierarchal information from an table if the table
contains the following fields
Lower_object_id
Higher_object_id

eg data within the table
Lower_object_id Higher_object_id
1 2
2 3
3 4
a b
b c

the hierachical query would have extracted all the numeric recordsets if the
param that was passed was 1
Click to expand...

Sorry I don't see your data as hierarchical :(
Could you please provide us with an example
Click to expand...

This example uses Northwind:

SHAPE {SELECT CustomerID, ContactName FROM Customers}
APPEND ({SELECT OrderID, OrderDate, CustomerID FROM Orders} AS
chapOrders
RELATE customerID TO customerID)

It's hierarchical because there is a one to many relationship between
Customers and Orders, so that each record in the Customers recordset
will return a child recordset of Orders for that customer.
 
Hi

This is an Hierarchical table as it has an involuted relationship on itself.
I am not interested in joining two tables but tree walking within the same
table ie. a foreign key on a table is the primary key on the same table.
Therefore one row within that table as a refernce to another row in that table

Regards
Chris
 
Chris said:
This is an Hierarchical table as it has an involuted relationship on itself.
I am not interested in joining two tables but tree walking within the same
table ie. a foreign key on a table is the primary key on the same table.
Therefore one row within that table as a refernce to another row in that table
Click to expand...

I'm not sure SHAPE is what you are looking for because it is not
'recursive'. For example using your data in Access

Sub test1()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
With con
.ConnectionString = _
"Provider=MSDataShape;Data " & _
CurrentProject.Connection.ConnectionString
.Open
.Execute _
"CREATE TABLE [Connection] " & _
"(Lower_object_id CHAR(1) NOT NULL PRIMARY KEY, " & _
"Higher_object_id CHAR(1) " & _
" REFERENCES [Connection] (Lower_object_id) " & _
" ON DELETE SET NULL ON UPDATE CASCADE)"
..Execute _
"INSERT INTO [Connection] (Lower_object_id) VALUES ('1')"
..Execute _
"INSERT INTO [Connection] (Lower_object_id) VALUES ('2')"
..Execute _
"INSERT INTO [Connection] (Lower_object_id) VALUES ('3')"
..Execute _
"INSERT INTO [Connection] (Lower_object_id) VALUES ('4')"
..Execute _
"INSERT INTO [Connection] (Lower_object_id) VALUES ('a')"
..Execute _
"INSERT INTO [Connection] (Lower_object_id) VALUES ('b')"
..Execute _
"INSERT INTO [Connection] (Lower_object_id) VALUES ('c')"
..Execute _
"UPDATE [Connection] SET Higher_object_id = '2' WHERE Lower_object_id =
'1'"
..Execute _
"UPDATE [Connection] SET Higher_object_id = '3' WHERE Lower_object_id =
'2'"
..Execute _
"UPDATE [Connection] SET Higher_object_id = '4' WHERE Lower_object_id =
'3'"
..Execute _
"UPDATE [Connection] SET Higher_object_id = 'b' WHERE Lower_object_id =
'a'"
..Execute _
"UPDATE [Connection] SET Higher_object_id = 'c' WHERE Lower_object_id =
'b'"
Dim rs1 As Object
Set rs1 = .Execute( _
"SHAPE {SELECT Lower_object_id, Higher_object_id FROM [Connection]} " &
_
"APPEND ({SELECT Lower_object_id, Higher_object_id FROM [Connection]}"
& _
" AS chapConnection1 RELATE Higher_object_id TO Lower_object_id)")
MsgBox rs1.GetString
rs1.MoveFirst
Dim rs2 As Object
Set rs2 = rs1.Fields("chapConnection1").Value
MsgBox rs2.GetString
End With
End Sub

returns the first level. To get the next level you'd have to relate it
again 'manually' e.g. by opening another hierarchical recordset through
reshaping and so on for each level.

The closest I've seen is for SQL Server:

HOW TO: Show Expanding Hierarchies by Using SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;248915

So if you have to 'fake' it in SQL Server, then you are going to have
to 'fake' it in Access :(
 
Back
Top