I have no idea what the official name for it is so maybe that's why i can't find anything online. Basically, when you use sql in vfp it does an initial pass through (sometimes 2?) without moving the record cursor or saving the results. Unfortunately I have sub routines in my sql that run and change things during that initial pass.
Why am i using subroutines in sql queries? Because vfp doesn't support referencing outside a subquery within the select items (once again i don't know the official name).
Example: select id, (select detail.name from detail where master.id == detail.id) name from master
This does work though: select id, getname(id) from master
where getname() is a sub routine containing the sql from the first example.
You could also use a join, but the above is just an example and a join does not work in my case.
Is there any way to deal with initial pass throughs? Does vfp create a boolean like firstpass or something? I suppose i could add a count to my subroutine, but that seems messier than it already is.
Alternatively can someone explain or link me an explanation to vfp's initial pass? I believe it was only doing one initial pass before but now it's doing two after changing some code.
Edit: ok, i was wrong. The above example does work. What doesn't work is the following:SELECT d2.id, (SELECT TOP 1 d1.lname l FROM dpadd d1 WHERE d1.id== d2.id ORDER BY l) FROM dpadd d2
It gives me a "SQL: Queries of this type are not supported" error.
Strangely it works if i do the following:SELECT d2.id, (SELECT COUNT(d1.lname) FROM dpadd d1 WHERE d1.id == d2.id) FROM dpadd d2
About the subroutines, they are methods of my form. The databases are local .dbf files. I'm not interacting with any servers, just running straight sql commands with into cursor clauses and then generating reports (usually).
I'll post back in a few minutes with an actually useful select statement that "is not supported". I'm sure you've noticed the top 1 example is completely useless.
