Pseudo-Field Variables

InterSystems SQL queries support the following pseudo-field values:

%ID — returns the RowID field value, regardless of the actual name of the RowID field.
%TABLENAME — returns the qualified name of an existing table that is specified in the FROM clause. The qualified table name is returned in the letter case used when defining the table, not the letter case specified in the FROM clause. If the FROM clause specifies an unqualified table name, %TABLENAME returns the qualified table name (schema.table), with the schema name supplied from either a user-supplied schema search path or the system-wide default schema name. For example, if the FROM clause specified mytable, the %TABLENAME variable might return SQLUser.MyTable.
%CLASSNAME — returns the qualified class name (package.class) corresponding to an existing table specified in the FROM clause. For example, if the FROM clause specified SQLUser.mytable, the %CLASSNAME variable might return User.MyTable.

Note:

The %CLASSNAME pseudo-field value should not be confused with the %ClassName()Opens in a new tab instance method. They return different values.

Pseudo-field variables can only be returned for a table that contains data.

If multiple tables are specified in the FROM clause you must use table aliases, as shown in the following Embedded SQL example:

 &sql(SELECT P.Name,P.%ID,P.%TABLENAME,E.%TABLENAME 
      INTO :name,:rid,:ptname,:etname
      FROM Sample.Person AS P,Sample.Employee AS E)
 IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
 ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
    
 WRITE "Person Name is: ",name,!
 WRITE "Person RowId is: ",rid,!
 WRITE "P alias TableName is: ",ptname,!
 WRITE "E alias TableName is: ",etname,!

try:
    result = iris.sql.exec("""
    SELECT P.Name, P.%ID, P.%TABLENAME, E.%TABLENAME
    FROM Sample.Person AS P, Sample.Employee AS E
    """)
    
    row = next(result, None)

    if row:
        pname, pid, ptname, etname = row
        print(f"Person Name is: {pname}")
        print(f"RowId is: {pid}")
        print(f"P alias TableName is: {ptname}")
        print(f"E alias TableName is: {etname}")
    else:
        print("No results found.")

except Exception as e:
    sql_code = e.sqlcode

    if sql_code < 0:
        print(f"SQLCODE error {sql_code} {str(e)}")
    elif sql_code == 100:
        print("Query returns no results")

The %TABLENAME and %CLASSNAME columns are assigned the default column name Literal_n, where n is the selectItem position of the pseudo-field variable in the SELECT statement.

Query Metadata

Queries Invoking Free-text Search