Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Complicated select via VfpOleDb
Message
De
14/04/2010 16:57:56
Dragan Nedeljkovich (En ligne)
Now officially retired
Zrenjanin, Serbia
 
 
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Versions des environnements
Visual FoxPro:
VFP 9 SP1
OS:
Windows XP SP2
Divers
Thread ID:
01460193
Message ID:
01460200
Vues:
53
>>I have a fairly complicated piece of SQL select that I need to make available to an outside party (who will be using, AFAIK, Access, of all things). Since the ODBC dbf driver is hopelessly stuck in the XX century (supports VFP6 features and wasn't updated since), I tried OleDB. It should be something that Access people should be familiar with.
>>
>>However, the select involves two levels of subquery, or just one if I break it into two pieces (or none, if I break it even further). The trouble is how to persist the temporary cursors across calls, or how to issue multiple statements in one oConn.Execute (lcScript ) call. Neither works; I'm either getting syntax error or missing clause error.
>>
>>After searching UT, Sergey's and the wiki, I'm no smarter than before. The examples everywhere go as far as "select * from customer", and show maybe how to update the customer table. None of the examples involve subqueries, or temp cursors being reused in the next select... so, am I the first to do this?
>>
>>I hope not, and that someone will tell me where's the switch to turn this on.
>
>Can you use derived tables?

Maybe, but what after? That would complicate things, though, because these would then be free tables and I have many fields with long names; I could create a temp database and send intermediate results there... but, again - cleanup? This is supposed to be something I can give to the Access guys, with "here's your script, just run this and you'll have your recordset". I've done this for other such cases, but for pulling data from SQL server, and there was no problem.

Besides, creating a temp database etc - wouldn't that involve some heavier scripting and teaching the guys I've never seen some VFP tricks? I just want to have

select ...from table1 join table2 on ... join (select something from table3 where ...) alias3 group by... etc.etc... into whatever1

select ... from myothertable join whatever1 on {some condition}

and have the results of the 2nd select in a recordset.

Writing a little app which would export the data is also not an option - it would involve installation, distribution, maintenance etc, again not viable.

back to same old

the first online autobiography, unfinished by design
What, me reckless? I'm full of recks!
Balkans, eh? Count them.
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform