Converting a FoxPro Procedure to SQL

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.

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

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.

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

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

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.

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

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/

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 17:17:47

Are you trying to set a PATH (for Hierarchical data)?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources