Bigquery parser error: Expecting ordered_sql, found ", tpv AS (

[lveras]

Hi! We found the error below!

Error: Expecting ordered_sql, found ", tpv AS (" (at char 1779), (line:40, col:2)

(the full query now)

WITH balance AS (
    SELECT
        beyblade_id                                               AS beyblade_id
        ,babala_id                                             AS babala_id
        ,CASE 
            WHEN datinha_marota = \"9999-12-31\" 
            THEN due_date 
            ELSE datinha_marota 
        END                                                     AS age
        ,SUM(hugo_boss)                            AS hugo_boss
        ,SUM(net_dos_milagres)                              AS net_dos_milagres                                  
    FROM `*******************.**********************.********************` mov
    WHERE
        reference_date <= DATE_SUB(_reference_date, INTERVAL 2 DAY)
        AND 
        (datinha_marota > DATE_SUB(_reference_date, INTERVAL 2 DAY) OR datinha_marota IS NULL)
    GROUP BY 1, 2, 3
), balance_settled_to_disregard AS (
    SELECT
        beyblade_id
        ,babala_id
        ,datinha_marota
        ,SUM(hugo_boss)                                        AS hugo_boss
        ,SUM(net_dos_milagres)                                          AS net_dos_milagres
    FROM `*******************.**********************.********************` mov
    WHERE  
        (datinha_marota >= DATE_SUB(_reference_date, INTERVAL 1 DAY)
        AND datinha_marota <= _reference_date)
        AND reference_date <= DATE_SUB(_reference_date, INTERVAL 2 DAY)
    GROUP BY 1, 2, 3
), tpv AS (
    SELECT
        IF(teraband_discreto = 'VISA', 10,
            IF(teraband_discreto = 'MasterCard', 40,
                IF(teraband_discreto = 'Elo', 20,
                    IF(teraband_discreto = 'Hiper / HiperCard', 78,
                        IF(teraband_discreto = 'American Express', 23,
                            IF(teraband_discreto = 'Cabal', 1235, 1234)))))) as beyblade_id
        ,CASE WHEN
            teraband_discreto = 'MasterCard' THEN
                IF(tipinho_maroto_id = 2, 1234, 12354)
            ELSE
                IF(tipinho_maroto_id = 10, 123, 234)
        END AS babala_id
        ,DATE(IF(
            tipinho_maroto_id = 2, 
            DATE_ADD(dia_da_apresentacaozinha, INTERVAL 28+(installment_number-1)*30 DAY), 
            dia_da_apresentacaozinha
        ))                                                                              AS age       
        ,SUM(
        CASE 
            WHEN tx_type = 1 
            THEN +instagram_original 
            ELSE -instagram_original 
        END)                                                                            AS hugo_boss
        ,SUM(
        CASE 
            WHEN tx_type = 1 
            THEN +instagram_original 
            ELSE -instagram_original 
        END) -
        SUM(
        CASE 
            WHEN tx_type = 1 
            THEN +amount 
            ELSE -amount 
        END)                                                                            AS net_dos_milagres
    FROM `*******************.**********************.********************` t
    WHERE
        CASE 
            WHEN tipinho_maroto_id = 1
            THEN (
                CASE 
                    WHEN is_week_day
                    THEN (
                        DATE(dia_da_apresentacaozinha) = _reference_date
                    )
                    ELSE (
                        DATE(dia_da_apresentacaozinha) 
                        BETWEEN 
                        DATE_SUB(_reference_date, INTERVAL 1 DAY) AND
                        _reference_date

                    )
                END 
            )
            ELSE (
                DATE(dia_da_apresentacaozinha) 
                BETWEEN 
                DATE_SUB(_reference_date, INTERVAL 1 DAY) AND
                _reference_date 
            )
        END
    GROUP BY 1, 2, 3
)

SELECT
    stg.reference_date
    ,inst.name                  AS hytofly
    ,st.name                    AS service_type
    ,stg.age
    ,stg.hugo_boss
    ,stg.net_dos_milagres
    ,stg.updated_at
FROM (
    SELECT 
        _reference_date                                                                                     AS reference_date
        ,IFNULL(IFNULL(bal.babala_id, bstd.babala_id), t.babala_id)                       AS babala_id
        ,IFNULL(IFNULL(bal.beyblade_id, bstd.beyblade_id), t.beyblade_id)                          AS beyblade_id
        ,COALESCE(DATE(t.age), DATE(bal.age))                                                               AS age
        ,IFNULL(bal.hugo_boss, 0) - IFNULL(bstd.hugo_boss, 0) + IFNULL(t.hugo_boss, 0)             AS hugo_boss
        ,IFNULL(bal.net_dos_milagres, 0) - IFNULL(bstd.net_dos_milagres, 0) + IFNULL(t.net_dos_milagres, 0)                   AS net_dos_milagres
        ,current_timestamp                                                                                  AS updated_at
    FROM balance bal
    FULL JOIN balance_settled_to_disregard bstd 
        ON DATE(bal.age) = DATE(bstd.datinha_marota)
        AND bal.babala_id = bstd.babala_id
        AND bal.beyblade_id = bstd.beyblade_id
    FULL JOIN tpv t
        ON DATE(bal.age) = DATE(t.age)
        AND bal.babala_id = t.babala_id
        AND bal.beyblade_id = t.beyblade_id
) stg
    JOIN `*******************.**********************.********************` st
        ON st.babala_id = stg.babala_id
    JOIN `*******************.**********************.********************` inst
        ON inst.beyblade_id = stg.beyblade_id

[klahnakoski]

thank you. looking...

[klahnakoski]

https://github.com/klahnakoski/mo-sql-parsing/commit/bb7553265caf8242d60945a5903292115306ff9d#diff-c27809480953c5908cdc63856a3e545a7fd67d739492c0968b61f5090a107b0cR315

[klahnakoski]

https://pypi.org/project/mo-sql-parsing/9.281.22341/