Manifold Community Site: vbscript to run SQL on a table object?

RE: vbscript to run SQL on a table object?

menicoll — Fri, 23 May 2025 00:35:41 GMT

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!)

RE: vbscript to run SQL on a table object?

Dimitri — Fri, 23 May 2025 05:22:16 GMT

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]);

RE: vbscript to run SQL on a table object?

menicoll — Mon, 26 May 2025 17:49:35 GMT

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!