Using WITH Clause to mimic App Engine Temp Tables

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.