Subscribe to this thread
Home - General / All posts - vbscript to run SQL on a table object?
menicoll19 post(s)
#23-May-25 00:35

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

Dimitri


7,557 post(s)
#23-May-25 05:22

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

menicoll19 post(s)
#26-May-25 17:49

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!

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.