Hierarchical Data in Jet

[Tim Marshall]

Given: a table which contains a Name or tag field (say for equipment),
FM_TAG_NO and which also has a field referencing FM_TAG_NO, ie
FM_MAJOR_TAG to effect a parent/child relationship.

For example, a piece of equipment:

FM_TAG_NO FM_MAJOR_TAG
UA1001-BLR-01
UA1001-AIR-01 UA1001-BLR-01
UA1001-EXH-01 UA1001-BLR-01

which makes the second two items children of the first, UA1001-BLR-01.

Oracle (and I assume MS SQL) has a nice clause in SQL where you can
retrieve the following, which shows the complete set of children,
grandchildren, great grandchildren, etc (indicated as LEVEL in the
following) - the FM_MAJOR_TAG field is left out here:

FM_TAG_NO LEVEL Item
UA1001-BLR-01 1 Steam Boiler #1
UA1001-AIR-01 2 Air System Components (for UA1001-BLR-01)
UA1001-CTRL-09 3 Damper Control - Forced Draft Fan
UA1001-FAN-01 3 Forced Draft Fan (for UA1001-BLR-01)
UA1001-MTR-26 4 Motor - Forced Draft Fan #1 (for UA1001-BLR-01)
UA1001-WBOX-01 3 Wind Box (for UA1001-BLR-01)
UA1001-EXH-01 2 Exhaust System Components (for UA1001-BLR-01)
UA1001-CTRL-10 3 Damper Control - Outlet (for UA1001-BLR-01)
UA1001-SENR-07 3 Oxygen Sensor (for UA1001-BLR-01)

etc, etc. CTRL-09, FAN-01, WBOX-01 are children of AIR-01 which is the
child of BLR-01, MTR-26 is the child of FAN-01 and so on.

The SQL for this uses the start with/connect by prior clause:

SELECT
FM_TAG_NO,
level,
FM_DESCRIPTION "Item"
from
tma.f_equipment
start with fm_tag_no = 'UA1001-BLR-01'
connect by prior fm_tag_no = fm_major_tag

Does Jet have anything similar?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Steve Jorgensen]

Neither JET nor MS SQL Server prior to the 2005 release has any direct support
for heirarchical querying. It appears that SQL Server 2005 does have a way to
do it.

On Thu, 27 Jan 2005 11:15:12 -0330, Tim Marshall <TI****@antarctic.flowerpots>
wrote:

Given: a table which contains a Name or tag field (say for equipment),
FM_TAG_NO and which also has a field referencing FM_TAG_NO, ie
FM_MAJOR_TAG to effect a parent/child relationship.

For example, a piece of equipment:

FM_TAG_NO FM_MAJOR_TAG
UA1001-BLR-01
UA1001-AIR-01 UA1001-BLR-01
UA1001-EXH-01 UA1001-BLR-01

which makes the second two items children of the first, UA1001-BLR-01.

Oracle (and I assume MS SQL) has a nice clause in SQL where you can
retrieve the following, which shows the complete set of children,
grandchildren, great grandchildren, etc (indicated as LEVEL in the
following) - the FM_MAJOR_TAG field is left out here:

FM_TAG_NO LEVEL Item
UA1001-BLR-01 1 Steam Boiler #1
UA1001-AIR-01 2 Air System Components (for UA1001-BLR-01)
UA1001-CTRL-09 3 Damper Control - Forced Draft Fan
UA1001-FAN-01 3 Forced Draft Fan (for UA1001-BLR-01)
UA1001-MTR-26 4 Motor - Forced Draft Fan #1 (for UA1001-BLR-01)
UA1001-WBOX-01 3 Wind Box (for UA1001-BLR-01)
UA1001-EXH-01 2 Exhaust System Components (for UA1001-BLR-01)
UA1001-CTRL-10 3 Damper Control - Outlet (for UA1001-BLR-01)
UA1001-SENR-07 3 Oxygen Sensor (for UA1001-BLR-01)

etc, etc. CTRL-09, FAN-01, WBOX-01 are children of AIR-01 which is the
child of BLR-01, MTR-26 is the child of FAN-01 and so on.

The SQL for this uses the start with/connect by prior clause:

SELECT
FM_TAG_NO,
level,
FM_DESCRIPTION "Item"
from
tma.f_equipment
start with fm_tag_no = 'UA1001-BLR-01'
connect by prior fm_tag_no = fm_major_tag

Does Jet have anything similar?

[Chris2]

"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:ct**********@coranto.ucs.mun.ca...

<snip>

The SQL for this uses the start with/connect by prior clause:
<snip>
Does Jet have anything similar?
As Steven Jorgensen points out, the answer is no.

The "START WITH . . . . CONNECT BY PRIOR" statement is an ORACLE
propriety feature, and is generally non-portable.
Sincerely,

Chris O.

Tim http://www.ucs.mun.ca/~tmarshal/

[Tim Marshall]

Chris2 wrote:

As Steven Jorgensen points out, the answer is no.

The "START WITH . . . . CONNECT BY PRIOR" statement is an ORACLE
propriety feature, and is generally non-portable.

Thanks Steve and Chris.

What a pity, it's an incredibly useful feature.

How do other folks handle hierarchical type reporting, then? VLB code?
Or a complete redesign of how the parent child relationship is set up?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David Schofield]

On Thu, 27 Jan 2005 13:35:41 -0330, Tim Marshall
<TI****@antarctic.flowerpots> wrote:

How do other folks handle hierarchical type reporting, then? VLB code?
Or a complete redesign of how the parent child relationship is set up?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<

Hi
Take a lookat "Trees in SQL" by Joe Celko
http://www.intelligententerprise.com...equestid=29530
for a radical approach

David