Alan Webb wrote:
Both Oracle and SQL Server, and
perhaps MSDE have techniques within the scripting languages for presenting
hierarchical data.
I don't work with MS SQL, but Oracle has the wonderful "Start
With...Connect by" as an SQL statement which displays the parent and
children and includes the "level" to which a child belongs, including
the direct parentage, the parent being 1.
Colleagues who work with the same maintenance management system I do,
but with an MS SQL back end (our back end is Oracle) tell me MS SQL does
not have the equivalent SQL. Is that incorrect? Or are the techniques
you mention more to do with stored procedures?
For an example of the Oracle SQL, see below. Boiler 1, with tag
UA1001-BLR-01 is the parent. The level numbers show the many and varied
different lines of parent-childrelationships. In the table
F_Equipment, FM_TAG_NO is the actual equipment tag number and
FM_MAJOR_TAG is the parent. Yes I know this is stupid, the designers
made a own rod for their back by not using primary and foreign keys, but
their reaction for my observation on this is typical of DB designers who
have done things a bad way - "normalization is good in theory but not
practical in practice" - the biggest load of horse pucky I've heard, but
I'm sure many of you have heard this sort of thing. Anyway, I digress -
there's the example:
SELECT
LEVEL,
(select FU_UNIT_ID from tma.f_areas where fu_pk = fm_fu_fk) "Area ID" ,
FM_TAG_NO "Eqpt ID",
substr(FM_DESCRIPTION,1, 20) "Description"
from
f_equipment
start with fm_tag_no = 'UA1001-BLR-01'
connect by prior fm_tag_no = fm_major_tag
LEVEL Area ID Eqpt ID Description
1 UA-1001 UA1001-BLR-01 Steam Boiler #1
2 UA-1001 UA1001-AIR-01 Air System Component
3 UA-1001 UA1001-CTRL-09 Damper Control - For
3 UA-1001 UA1001-FAN-01 Forced Draft Fan (fo
4 UA-1001 UA1001-MTR-26 Motor - Forced Draft
3 UA-1001 UA1001-WBOX-01 Wind Box (for UA1001
2 UA-1001 UA1001-EXH-01 Exhaust System Compo
3 UA-1001 UA1001-CTRL-10 Damper Control - Out
3 UA-1001 UA1001-SENR-07 Oxygen Sensor (for U
3 UA-1001 UA1001-SOOT-02 Soot Blower for #1 (
3 UA-1001 UA1001-SENR-08 Smoke Density Sensor
3 UA-1001 UA1001-SENR-09 Furnace Pressure Sen
3 UA-1001 UA1001-SOOT-01 Soot Blower for #1 (
2 UA-1001 UA1001-FUEL-01 Fuel System Componen
3 UA-1001 UA1001-VLVE-43 Control Valve - Fuel
3 UA-1001 UA1001-VLVE-45 Fuel Solenoid Valve
3 UA-1001 UA1001-METR-03 Bunker Fuel Meter (f
3 UA-1001 UA1001-VLVE-44 Fuel Solenoid Valve
2 UA-1001 UA1001-HTHW-01 HTHW System Componen
3 UA-1001 UA1001-HEX-01 Heat Exchanger #1 (H
4 UA-1001 UA1001-VLVE-42 Control Valve - HTHW
4 UA-1001 UA1001-PUMP-01 Primary Pump #1 (for
5 UA-1001 UA1001-POTC-06 Pot Cooler - Primary
5 UA-1001 UA1001-MTR-01 Motor - Primary Pump
4 UA-1001 UA1001-VLVE-41 Safety Valve - HTHW
2 UA-1001 UA1001-STM-01 Steam System Compone
3 UA-1001 UA1001-POTC-05 Pot Cooler - Conduct
3 UA-1001 UA1001-VLVE-46 Pressure Reducing Va
3 UA-1001 UA1001-VLVE-39 Safety Valve - Steam
3 UA-1001 UA1001-VLVE-40 Safety Valve - Steam
2 UA-1001 UA1001-WATR-01 Water System Compone
3 UA-1001 UA1001-VLVE-38 Control Valve - Feed
3 UA-1001 UA1001-CTRL-11 Controller - Feedwat
3 UA-1001 UA1001-WCOL-01 Water Column (for UA
3 UA-1002 UA1002-TANK-02 Chemical Tank (for U
4 UA-1002 UA1002-MTR-18 Agitator Motor (for
4 UA-1002 UA1002-PUMP-15 Chemical Pump (for U
5 UA-1002 UA1002-MTR-01 Motor - Chemical Pum
2 UA-1001 UA1001-PANL-01 Boiler Control Panel
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me