Is it possible to return the table name used in a query as a field in the result set?

Thanks,
David Brown
s2000 Field Service Solutions

You would have to construct your query so that the table name is in the select list as a constant:

dim sSQL as string
sSQL="select *," + sTableName + " from " + sTableName
 

 Unfortunately, doing that returns "Invalid column name" :(

Sorry, I made a mistake.  The tablename needs to be enclosed in double quotes to be treated as a constant.  Something like:

sSQL = "select *, " + chr$(34) + sTableName + chr$(34) + " from " + sTableName 

That didn't seem to work, either. Here's my statement:

SELECT Customer.LOCATIONKEY, "Customer" FROM CUSTOMER AS Customer

And the error:

S0022: [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Invalid column name: 'Customer'.

EDIT: The PSQL documentation says that an apostriphe (`) should be used with a closing apostrophe or single quote. However, that returns a syntax error rather than an invalid column error.

This worked for me:

select class.id, 'class' from class as class

I was wrong on the quotes (I didn't try it, I was going from memory).   Use single quotes and it'll work.

I'm curious to know why you're wanting the table name in the resultset though.

Single quotes return a syntax error for me.

I'm working on a Pervasive-specific ODBC driver for a web framework and the function that builds the results table must know the name of the PSQL table that the result is coming form. However, the way the base class is designed, there is no way to know the name of the table without using a function that extracts the table association from the result (which the ODBC libray does not have). I also can't read the table name from the query itself because the function has no knowledge of the original SQL statement.

It's a really limited system, I know. But, unfortunately, it's all I have to work with. By including the table name in the results, I can simply read it out like any other data in the result set.

 EDIT: Ok, single quotes seem to work as long as the select statement doesn't use an asterisk. I still need the table name in that case, though.

Can you try the query with single quotes in the Pervasive Control Center or ODBC Test?  I used ODBC Test and it worked for me.   

This works for me --

select 'Billing',Billing.* from "Billing"

You just need to qualify your * to make it work. 

Good luck

Ron