| SQL tree [message #60881] |
Thu, 11 March 2004 17:41  |
mohamed
Messages: 27
Registered: November 2001
|
Junior Member |
|
|
I have these two tables,
I need to create a description tree for the respective codes
This is a 12 digit code with 3 characrters forming one level, next 3 characters level2, next three level3, next three characters level4 and next three chars level 5
eg: ABKABWBYR000000
ABK is parent level since it has zeros at other positions
ABW is the child of ABK
BYR is the child of ABW ..so on
So I need to build a tree.
presently I wrote a stored procedure which is inefficient
it goes like,
I select all parents in 1st query
then i run a while loop
while EOF
print value
in second query select next level parents passing the 1st level value in the where condition
in third query select next level parents passing the 2nd level value in the where condition
and so on till 5 levels.
Can anyone help me do this tree.
Thanx
table
codes
ABK000000000000
ABKABW000000000
ABKABWBYR000000
ABKABWCCY000000
ABKABWCDG000000
ABKABWCDH000000
ACJ000000000000
ACJAA0AA1AA8000
ACJAA0AA1AW3000
ACJAA0AA2000000
ACJAA0AA2AA5000
ACJAA0AA2AN4000
ACJAA0AA2AUY000
ACJAA0AA2AW3000
ACJAC9000000000
ACJAC9A1D000000
ACJAC9A1Z000000
ACJAC9AA5000000
ACJAC9AEU000000
ACJAC9AH7000000
ACJAC9AL1000000
ACJAC9ALI000000
ACJAC9ALJ000000
ACJAC9AN4000000
ACJAC9AQF000000
ACJAC9AUO000000
ACJAC9AUY000000
ACJAC9AW3000000
ACJAC9BXS000000
ACJAC9BYL000000
ACJAC9CAF000000
ACJAC9CAPAH0AH1
ACJAC9CAPAH0AH2
ACJAC9CAPAH0AH3
ACJAC9CAPAH0AH4
ACJAC9CAPAHO000
ACJAC9CAPARVARW
ACJAC9CAPARVARX
ACJAC9CAPARVARY
table code_desc
A0B Antiobesity Agent
A0C Dietary Fat Absorption Inhibitor
A0H Diagnostic Agent, Myasthenia Gravis
A0N Pineal Gland Hormone
A0P Opioid/NSAID Combination
A0Q 5-Alpha Reductase Inhibitor
A0R Alopecia Agent
A11 Opioid/Acetaminophen Combination
A14 Diagnostic Agent, Parkinson's Disease
A18 Pigmentation Agent
A1A Endometrial Hyperplasia Agent
A1B Diagnostic Agent, Pulmonary Function
A1D Oxazolidinone
A1H Antihistamine/Decongestant Combination
A1I Antihistamine/Decongestant/Expectorant Combination
|
|
|
|
| Re: SQL tree [message #60952 is a reply to message #60881] |
Tue, 16 March 2004 10:19  |
ilver
Messages: 50
Registered: January 2004
|
Member |
|
|
Hi Mohamed,
I suggest you normalize you datamodel.
You have parent / child relations described in the content instead of by the database layout. Thats why you get problems and the database cant help you solve the query needed.
If it isn't possible you could consider using Java stored procedures for manipulating the string/field content, but still there is some way to the goal.
/ilver
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
