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 dualunionSELECT '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_CAREERFROM 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.