Can arguments be used in SQL FROM portion ?

[askoog66]

Is it possible to use arguments in a Select statement somewhere other than the Where part?

I have a multi-tenant database with a separate schema for each tenant in the same database, but they all have the same table structures.
For example, tables such as:
company1.employees
company2.employees
company3.employees
.....

So in a typical Grid or UX component select statement, I would like to do something like this:
Select * from :tenantid.employees

Tenantid would be a session variable setup on login identifying the company.

Have experimented in Query builder by setting the entire Tenantid argument to company1.employees and still get unexpected token error.
Obviously would need to do similar for all the dropdown statements, reports, etc.

Looks like arguments are only for the Where portion?

I have done similar thing having a tenantid column in every table and filtering data using the column. I am getting push back from potential users who cannot stand the idea of their data being co-mingled in a table with somebody else. Looking at an easy alternative.

Thanks

[rnoguera]

Re: Can arguments be used in SQL FROM portion ?

You should use separate databases for tenant.
This gives you the correct security.

Alpha 5 connection string then are generated by xbasic for each user, depending on his tenant.

Works flawlessly.

And you can use a tool like SQL Tool Belt to keep all your tenant databases in sync.

When you add a column or table (or anything) to a tenant database, you can sync all other tenants in a minute.

[lvasic]

Re: Can arguments be used in SQL FROM portion ?

You should try dynamic connection strings. From the documentation:

A dynamic connection string is a connection string that is resolved at the time the database connection is opened, rather than at the time the connection string is defined.

Discussion

Dynamic connection strings can be created in Alpha Anywhere in one of two ways: by defining a connection string using the DynamicConnection_ prefix or using the ::EVAL:: prefix, which allows you to define an Xbasic function or expression that is used to dynamically calculate the connection string.

Using the DynamicConnection_ Prefix to Define Dynamic Connection Strings
Dynamic connection strings can be defined using a prefix. A connection string named "DynamicConnection_<name>", where <name> is a user-defined string identifying the connection string, will be resolved dynamically at runtime. For example:

[peteconway]

Re: Can arguments be used in SQL FROM portion ?

See this post.