Home -
General /
All posts - vbscript to run SQL on a table object?
|
If, in a VBscript, I run an SQL SELECT on a table in my (Manifold 9) project, the result is a table object. Eg: Set app = Manifold.Application Set db = app.GetDatabaseRoot() ' the opened MAP file sql = "SELECT * FROM TableA WHERE ...) ;" Set TableB = db.Run(sql) Now if I want to run some SQL on TableB, how do I reference TableB in the SQL string? I suppose I could include an "INTO TableB" clause in my first SQL string, which would create a table which I could query as usual, but I was wondering if there was a way to query the TableB object directly. (There does not seem to be a "query" method for the Table object in the API documentation!)
|
|
Easy: just write your sql string to use the table your SELECT creates. An SQL expression that returns a table can be used anywhere a table can be used. Suppose you have a [Mexico Table] with a bunch of fields, one of which is a Population field. What you're asking is conceptually the same as doing: SELECT * INTO [TableB] FROM [Mexico Table]; SELECT [Population] FROM [TableB]; But you can do that in a single line of SQL: SELECT [Population] FROM (SELECT * FROM [Mexico Table]);
|
|
Thanks, yes that works fine. It seems odd that a table object in a vbscript cannot be queried directly. And I can't see a way of saving a table object as a table component so that I could query it with SQL E.g. a "SaveAs" method of the table object would be nice!
|