Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Converting a FoxPro Procedure to SQL

Author  Topic 

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-10 : 16:54:20
I have a standard adjacency matrix recursive table.

XOrder Child Parent
1 100 NULL
2 101 100
3 102 100
4 103 100
5 104 102
6 105 102
7 106 NULL
8 107 106

The problem is the rows are in a random order and XOrder is not populated. I need to populate XOrder such that when the table is ordered by XOrder it will be in the order above.
I originally did this in FoxPro. Being able to SCAN through records in a table made it easy. SQL however is a different story. What I did in Fox was

1. Indexed on XOrder.
2. Set XOrder to 1 for all Parent = NULL.
3. This placed all the parents at the top of the file.
4. This is where Fox has a SCAN command that would sequentially step through the file from top to bottom. Starting from the top of the file…
5. Find the children of this row.
6. Set their XOrder to the current XOrder+.5. This placed the children under the parent.
7. Sequentially number all remaining records starting from the XOrder of the current record.
8. Now the current record and the record immediately below it have the correct XOrder.
9. Next SKIP to the next record
10. Replete from step 5. This would be the end of the Fox SCAN loop and would automatically repeat until it reached the end of the table then exited the loop.

This will correctly set XOrder for the entire table.

The problem is there is no SCAN or SKIP in SQL. How would I accomplish this in SQL?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-10 : 18:34:45
you can do this in a SELECT statement without having to populate an XOrder column.

Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-10 : 18:47:12
here is my example:


CREATE TABLE test (XOrder INT, Child INT, Parent INT)
go

INSERT INTO test VALUES (1, 100, NULL)
INSERT INTO test VALUES (1, 107, 106)
INSERT INTO test VALUES (1, 106, NULL)
INSERT INTO test VALUES (1, 101, 100)
INSERT INTO test VALUES (1, 102, 100)
INSERT INTO test VALUES (1, 104, 102)
INSERT INTO test VALUES (1, 103, 100)
INSERT INTO test VALUES (1, 105, 102)
go

DROP TABLE test
go

-- Test without ORDER BY
SELECT * FROM test
go

XOrder Child Parent
----------- ----------- -----------
1 100 NULL
1 107 106
1 106 NULL
1 101 100
1 102 100
1 104 102
1 103 100
1 105 102


-- Test with ORDER BY
SELECT * FROM test ORDER BY child, parent
go


XOrder Child Parent
----------- ----------- -----------
1 100 NULL
1 101 100
1 102 100
1 103 100
1 104 102
1 105 102
1 106 NULL
1 107 106


So, XOrder is completely unneccesary with SQL




-ec

Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-10 : 19:34:45
The sample table provided was just a simplified version of my actual data which represents a tree. In the actual table a child ID may have many parents, the child IDs are not sequential and are in random order. Like wise the parent IDs are also not sequential and are in random order. For this reason ordering by those columns will not place the table in hierarchy order. For example child 102 may have a parent of 100 and 106.

In order to get the rows into hierarchy order a column must be populated containing the correct order. And an index created on that column. The Script I outlined was how I populated that column in FoxPro. Equivalent commands are not available in SQL.
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-10 : 19:54:39
give us real test data then and we can help you with the SQL statement.



-ec

Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-10 : 23:19:38
This is representitive of the actual data.

XOrder Child Parent
----------- ----------- -----------
NULL 100 NULL
NULL 107 106
NULL 106 NULL
NULL 101 100
NULL 102 100
NULL 104 102
NULL 103 100
NULL 125 106
NULL 015 NULL
NULL 105 100
NULL 105 106
NULL 107 015
NULL 888 106
NULL 075 107
Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-11 : 15:02:25
This is not the right approach in SQL. I will open a new thread with a more appropriate subject.
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-10-11 : 16:54:58
quote:
Originally posted by kirknew2SQL

This is representitive of the actual data.

XOrder Child Parent
----------- ----------- -----------
NULL 100 NULL
NULL 107 106
NULL 106 NULL
NULL 101 100
NULL 102 100
NULL 104 102
NULL 103 100
NULL 125 106
NULL 015 NULL
NULL 105 100
NULL 105 106
NULL 107 015
NULL 888 106
NULL 075 107



with your new test data and the simple SELECT I have from above I get the following output. looks good to me.

XOrder      Child       Parent
----------- ----------- -----------
NULL 15 NULL
NULL 75 107
NULL 100 NULL
NULL 101 100
NULL 102 100
NULL 103 100
NULL 104 102
NULL 105 100
NULL 105 106
NULL 106 NULL
NULL 107 15
NULL 107 106
NULL 125 106
NULL 888 106




-ec
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 17:06:45
quote:
Originally posted by kirknew2SQL

This is not the right approach in SQL. I will open a new thread with a more appropriate subject.



Just change the subject then.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 17:17:47
Are you trying to set a PATH (for Hierarchical data)?

Kristen
Go to Top of Page
   

- Advertisement -