How to get multiple AS statements with a WITH statement?

[info-rchitect]

Hi,

First let me say that I am not a SQL expert so I apologize ahead of time. I went through the unit tests and did not find an example that would reproduce something like this.

WITH UNIT_DEF AS
(
    SELECT DISTINCT SERIAL_NUMBER
    FROM UNIT_EVENT
    JOIN RUNTIME_INFO USING (RUNTIME_KEY)
    WHERE UNIT_START_DATE BETWEEN '2020-09-01' AND '2020-09-07'
        AND MFG_STEP_NAME IN ('FT1')
),
TEST_EVENT_01 AS
(
    SELECT 
         LOT_ID
        ,SERIAL_NUMBER
        ,MASTER_SERIAL_NUMBER
        ,UNIT_START_DATE
        ,RANK_DESC
    FROM UNIT_DEF
    JOIN UNIT_EVENT USING (SERIAL_NUMBER)
    JOIN RUNTIME_INFO USING (RUNTIME_KEY)
    JOIN UNIT_BIN_EVENT USING (MASTER_SERIAL_NUMBER, UNIT_START_DATE)
    JOIN TEST_EVENT USING (SERIAL_NUMBER, UNIT_START_DATE)
    WHERE 
    (MFG_STEP_NAME = 'WS1' AND RANK_DESC = 1)
),
TEST_DATA AS (
SELECT * FROM TEST_EVENT_01
)

I started with this:

import sqlparse
from pypika import Query, Table, Field, CustomFunction, analytics, Order, AliasedQuery, Tables, Criterion, Database
from pypika.dialects import SnowflakeQuery
(runtime_info, unit_event, unit_bin_event, test_event) = Tables('runtime_info', 'unit_event', 'unit_bin_event', 'test_event')
runtime_info = runtime_info.as_('r')
unit_event = unit_event.as_('ue')
unit_def_query = SnowflakeQuery.from_(unit_event).select('serial_number').distinct()
unit_def_criteria = Criterion.all([unit_event.unit_start_date['2020-09-01':'2020-09-07'], unit_event.mfg_step_name == 'FT1'])
unit_def_query = unit_def_query.where(unit_def_criteria)
unit_def_query = unit_def_query.join(runtime_info).using('runtime_key')
for sql_statement in sqlparse.split(str(unit_def_query)):
    print(sqlparse.format(sql_statement, reindent=True, keyword_case='upper'))

The SQL created is:

SELECT DISTINCT ue.serial_number
FROM unit_event "ue"
JOIN runtime_info "r" USING (runtime_key)
WHERE ue.unit_start_date BETWEEN '2020-09-01' AND '2020-09-07'
  AND ue.mfg_step_name='FT1'

How can I 'chain' the AS statements together within a single WITH statement?

thx

[anhqle]

Your query is a bit complex, so I tried something else simpler here that demonstrates multiple WITH statements.

from pypika import Table, AliasedQuery, Query

sub_query1 = Query.from_(customers).select('*')

sub_query2 = Query.from_('cust2').select('*')

test_query = (Query
            .with_(sub_query, "alias1")
            .with_(sub_query2, "alias2")
            .from_(AliasedQuery("alias1"))
            .select('*')
            .from_(AliasedQuery("alias2"))
            .select('test'))

print(test_query)

Result is

WITH alias1 AS (SELECT * FROM "customers") ,alias2 AS (SELECT * FROM "cust2") SELECT * FROM alias1,alias2