Looks like you are trying to provide a hierarchical structure to your data
but your current structure doesn't provide the flexibility that I think you
will need. Try this:
SUBCATID | SUBCATEGORY | PREVSUBCATID | HierPath
1 UNDERKAT 1 0 1/
2 UNDERKAT 2 1 1/2/
3 UNDERKAT 3 2
1/2/3/
4 UNDERKAT 4 3
1/2/3/4/
5 UNDERKAT 2.22 2 1/2/5/
6 UNDERKAT 3.33 3
1/2/3/6/
SELECT SUBCATEGORY
FROM SubCategoryTable
ORDER BY HierPath
Now the problem that you have is that you have inserted subcategories into
the middle of the hierarchy but you have not adjusted the subcategory
sequence, i.e. SUBCATID. So get rid of the current SUBCATID and actually use
the numbers you used in the SUBCATEGORY field as the SUBCATID so your table
will end up looking like:
SUBCATID | SUBCATEGORY | PREVSUBCATID | HierPath
1 UNDERKAT 1 0 1/
2 UNDERKAT 2 1 1/2/
3 UNDERKAT 3 2
1/2/3/
4 UNDERKAT 4 3
1/2/3/4/
2.22 UNDERKAT 2.22 2
1/2/2.22/
3.33 UNDERKAT 3.33 3
1/2/3/3.33/
Using the same select:
SELECT SUBCATEGORY
FROM SubCategoryTable
ORDER BY HierPath
You should now get the result you were looking for.
You could also add another field to contain the actual SUBCATNUMBER, and
keep the SUBCATID but use the SUBCATNUMBER in the HierPath field. Your
choice.
I've used this approach before and it works very well.
Michael MacGregor
Senior Database Manager
Timeline Technology Inc.
www.timelinetech.com
Quote:> Hello
> I'm having problems with a shape that works like an inner join (or
> something like that)
> And the table looks like this:
> SUBCATID | SUBCATEGORY | PREVSUBCATID
> 1 UNDERKAT 1 0
> 2 UNDERKAT 2 1
> 3 UNDERKAT 3 2
> 4 UNDERKAT 4 3
> 5 UNDERKAT 2.22 2
> 6 UNDERKAT 3.33 3
> the PREVSUBCATID should link to the previous SUBCATID (which is an
> identity column) and generate the following:
> UNDERKAT 1 /
> UNDERKAT 1 / UNDERKAT 2
> UNDERKAT 1 / UNDERKAT 2 / UNDERKAT 2.22/
> UNDERKAT 1 / UNDERKAT 2 / UNDERKAT 3 /
> UNDERKAT 1 / UNDERKAT 2 / UNDERKAT 3.33 /
> UNDERKAT 1 / UNDERKAT 2 / UNDERKAT 3 / UNDERKAT 4
> The shape looks like this at the moment:
> shape{select subcatid,prevsubcatid,subcategory from subcategory}
> append({select subcatid,prevsubcatid,subcategory from subcategory}
> as subcat relate prevsubcatid to subcatid)
> But it only generates this:
> / UNDERKAT 2 / UNDERKAT 2.22
> / UNDERKAT 3 / UNDERKAT 3.33
> / UNDERKAT 4
> Has anyone a good solution on this issue??
> I'd be grateful if anyone would help
> /Lasse
by