January 26, 2009 at 4:14 pm
Hi,
I have a CUSTOM_PROFILE table with the following columns
ID integer
PARENT_ID integer
NAME varchar(50)
and it has the following sample data
ID PARENT_ID NAME
10 Consultant
11 Corporate Management
12 Business Development
13 12 Investor
14 12 Marketing
The table is managed by another group and we can't add or modify the table structure. We would like to create a view in our database schema by querying data from CUSTOM_PROFILE table and insert a ROOT node data into the view. Also, replacing any records with a NULL parent_id with a root node ID of 0.
The goal to have the view with the following data based on the sample data provided above.
ID PARENT_ID NAME
10 0 Consultant
11 0 Corporate Management
12 0 Business Development
13 12 Investor
14 12 Marketing
0 -1 ROOT
I was thinking of a solution by creating a new table in our database schema that contain just the root node. Then, creating a view by joining the external USER_PROFILE table with a new table. But, I am not how to substitute the parent ID with 0 value as shown above.
I appreciate any tips you can provide.
Thanks,
Tuan
January 26, 2009 at 4:36 pm
SELECT 0 AS ID, -1 AS PARENT_ID, ROOT AS NAME
UNION ALL
SELECT ID, ISNULL(PARENT_ID, 0), NAME
FROM TableName
It's good you have no power to alter the existing table.
Its structure is better than your approach.
One day when you hit the wall with meaningless "empty" level it will be easier to fix the solution having the original table untouched.
_____________
Code for TallyGenerator
January 26, 2009 at 4:59 pm
Hi,
Your solution is an elegant solution. Thanks so much for your quick help!
Tuan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply