Category: %Table

Finding Data in Temp Tables

Here’s a simple little trick for Application Engines that have Temporary Tables assigned.

When you have temporary tables, you don’t know for sure which Temporary Table it is using.  You could write a quick select and change the number on the end until you find the one you are looking for, but here is a slightly quicker method.

Note: This is designed for Oracle, but you could easily change it up to work with SQL Server.

First, build a select statement that will list all of the tables.  We’ll assume our Temporary Table Record is MY_TEMP_TAO.  You can substitute with your specific record.

[sourcecode language=”sql”]
SELECT ‘UNION SELECT ”’ || TABLE_NAME || ”’ TBL, A.* FROM SYSADM.’ || TABLE_NAME || ‘ A’
FROM DBA_TABLES
WHERE table_name LIKE ‘PS_MY_TEMP_TAO%’;
[/sourcecode]

Update (thanks to Nicolas): You can avoid having to remove the first UNION if you use something more like this:

[sourcecode language=”sql”]
SELECT ‘SELECT ”’ || TABLE_NAME || ”’ TBL, A.* ‘ ||
‘FROM SYSADM.’ || TABLE_NAME || ‘ A’ ||
case when count(*)over()=rownum
then ‘;’ else ‘ UNION ‘ end
FROM ALL_TABLES
WHERE table_name LIKE ‘PS_MY_TEMP_TAO%’;
[/sourcecode]

The output should look something like this:

[sourcecode language=”sql”]
UNION SELECT ‘PS_MY_TEMP_TAO’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO A
UNION SELECT ‘PS_MY_TEMP_TAO1’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO1 A
UNION SELECT ‘PS_MY_TEMP_TAO2’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO2 A
UNION SELECT ‘PS_MY_TEMP_TAO3’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO3 A
UNION SELECT ‘PS_MY_TEMP_TAO4’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO4 A
UNION SELECT ‘PS_MY_TEMP_TAO5’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO5 A
UNION SELECT ‘PS_MY_TEMP_TAO6’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO6 A;
[/sourcecode]

You just simply need to delete the first Union to make something like this:

[sourcecode language=”sql”]
SELECT ‘PS_MY_TEMP_TAO’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO A
UNION SELECT ‘PS_MY_TEMP_TAO1’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO1 A
UNION SELECT ‘PS_MY_TEMP_TAO2’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO2 A
UNION SELECT ‘PS_MY_TEMP_TAO3’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO3 A
UNION SELECT ‘PS_MY_TEMP_TAO4’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO4 A
UNION SELECT ‘PS_MY_TEMP_TAO5’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO5 A
UNION SELECT ‘PS_MY_TEMP_TAO6’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO6 A;
[/sourcecode]

If you know your temp table has the process instance as the key and you know the one you are looking for, you could do something like this:

[sourcecode language=”sql”]
SELECT * FROM (
SELECT ‘PS_MY_TEMP_TAO’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO A
UNION SELECT ‘PS_MY_TEMP_TAO1’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO1 A
UNION SELECT ‘PS_MY_TEMP_TAO2’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO2 A
UNION SELECT ‘PS_MY_TEMP_TAO3’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO3 A
UNION SELECT ‘PS_MY_TEMP_TAO4’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO4 A
UNION SELECT ‘PS_MY_TEMP_TAO5’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO5 A
UNION SELECT ‘PS_MY_TEMP_TAO6’ TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO6 A;
) Z WHERE PROCESS_INSTANCE = 12345
[/sourcecode]

PeopleTools Reference: Meta-SQL %Table

%Table

Replaces with the actual table name of the given record.

Generally, this just means adding a “PS_” to the front of the record name. But, PeopleTools actually checks the alternate table name from the Record Type tab to see if a value is there first.

Two Uses:

  • Access a table with a reference or record object rather than embedding a table name in the SQL; PeopleTools will not index the table/record in a string literal.
  • Reference the Temporary table in an App Engine; %Table is the only way to reference the table because the table name is assign dynamically at run time.

Examples:

SELECT * FROM %Table(JOB) WHERE EMPLID = :1

SqlExec(“SELECT NAME FROM %Table(:1) WHERE EMPLID = :2”, Record.NAMES, &emplid, &name);

&sql = CreateSql(“SELECT * FROM %Table(:1) WHERE EMPLID = :2”, &MyRecord, &emplid);

INSERT INTO %Table(MYTEMP_TAO)
SELECT * FROM PS_MYDATATABLE