I've been working on an App Engine that makes use of Temp tables and uses the %Table meta-sql. It's sometimes difficult to debug your App Engine SQL because the data in the temp table gets deleted at the end of the run. So I get around this in my SQL client by using the WITH clause
WITH AS
(subquery_sql_statement),
AS
(sql_statement_from_alias_one)
That doesn’t make a lot of sense, so here’s a simple example you can run.
with NY_SSR_TSC_TMP AS
(
SELECT '0' PROCESS_INSTANCE, 1 REPORT_REQUEST_NBR, 1 REQUEST_SEQ_NBR, 1 REPORT_NUMBER, '9999991' EMPLID, 'PGT' ACAD_CAREER FROM dual
union
SELECT '1' PROCESS_INSTANCE, 1 REPORT_REQUEST_NBR, 1 REQUEST_SEQ_NBR, 1 REPORT_NUMBER, '9999992' EMPLID, 'UG' ACAD_CAREER FROM dual
)
select * from NY_SSR_TSC_TMP;
So I’ll turn an App Engine sql statement like this (from the delivered SSR_TSC_LIB App Engine - it's a library used by the Campus Solutions Transcript Generation Process):
SELECT Z.REPORT_REQUEST_NBR
,Z.REQUEST_SEQ_NBR
,Z.REPORT_NUMBER
,%Bind(SEQNO)
,%Bind(PRINT_AREA_CODE)
,%Bind(SSR_ROWTYPE)
,A.AID_YEAR
,A.ITEM_TYPE
,A.ACAD_CAREER
,B.DESCR
,A.DISBURSED_AMOUNT
,A.ACCEPT_AMOUNT
,%Bind(TSCRPT_TYPE_AET.AWARD_AID_YEAR_PRT)
,%Bind(TSCRPT_TYPE_AET.AWARD_AMOUNT_PRT)
FROM %Table(SSR_TSC_TMP) Z
, PS_STDNT_AWARDS A
, PS_ITEM_TYPE_FA B
WHERE Z.PROCESS_INSTANCE = %ProcessInstance
AND A.EMPLID = Z.EMPLID
AND A.INSTITUTION = %Bind(INSTITUTION)
AND A.ACAD_CAREER = Z.ACAD_CAREER
AND A.ACCEPT_AMOUNT > 0
AND A.ITEM_TYPE = B.ITEM_TYPE
AND A.SETID = B.SETID
AND A.AID_YEAR = B.AID_YEAR
AND B.INCLUDE_IN_TSCRPT = 'Y'
AND A.AWARD_STATUS = 'A'
AND B.EFF_STATUS = 'A'
AND B.EFFDT = (
SELECT MAX(B1.EFFDT)
FROM PS_ITEM_TYPE_FA B1
WHERE B.SETID = B1.SETID
AND B.ITEM_TYPE = B1.ITEM_TYPE
AND B.AID_YEAR = B1.AID_YEAR);
into something like this (%Table(SSR_TSC_TMP) changed to NY_SSR_TSC_TMP, %ProcessInstance changed to 0, some %Bind’s removed and the %Bind(INSTITUTION) in the WHERE clause changed to 'ZZZZZ'):
with NY_SSR_TSC_TMP AS
(
SELECT '0' PROCESS_INSTANCE,
1 REPORT_REQUEST_NBR,
1 REQUEST_SEQ_NBR,
1 REPORT_NUMBER,
'9999991' EMPLID,
'PGT' ACAD_CAREER
FROM dual
)
SELECT Z.REPORT_REQUEST_NBR
,Z.REQUEST_SEQ_NBR
,Z.REPORT_NUMBER
,A.AID_YEAR
,A.ITEM_TYPE
,A.ACAD_CAREER
,B.DESCR
FROM NY_SSR_TSC_TMP Z
, PS_STDNT_AWARDS A
, PS_ITEM_TYPE_FA B
WHERE Z.PROCESS_INSTANCE = 0
AND A.EMPLID = Z.EMPLID
AND A.INSTITUTION = 'ZZZZZ'
AND A.ACAD_CAREER = Z.ACAD_CAREER
AND A.ACCEPT_AMOUNT > 0
AND A.ITEM_TYPE = B.ITEM_TYPE
AND A.SETID = B.SETID
AND A.AID_YEAR = B.AID_YEAR
AND B.INCLUDE_IN_TSCRPT = 'Y'
AND A.AWARD_STATUS = 'A'
AND B.EFF_STATUS = 'A'
AND B.EFFDT = (
SELECT MAX(B1.EFFDT)
FROM PS_ITEM_TYPE_FA B1
WHERE B.SETID = B1.SETID
AND B.ITEM_TYPE = B1.ITEM_TYPE
AND B.AID_YEAR = B1.AID_YEAR);
The power of the WITH clause really proves itself out whenever the new dummy table is referenced more than once in your SQL statement.
