Looker is generating multiple SQL statements that are not separated by semicolons

[mary_schulte]

Hello

I am porting  the marketplace marketplace_retail-block-v2 application to work with another database.     It’s going well so far.

 

The marketetplace-retail-block-v2 uses pdts, so I have retained that code in my version.

 

I’m going through testing all my views and explores and I get an sql error.    shown below.

 

 

The full code that is being generated looks like this:    The SQL statements themselves look fine.  The problem is that there 4 separate (all valid) SQL statements here, but they are not separated by semicolons…so the database throws an error.

 

CREATE TABLE retail.LR$CMHS61638805463410_store_tiering AS WITH store_tiering_base AS (SELECT

        stores.store_id  AS id,

        stores.name  AS name,

        COALESCE(SUM(transactions__line_items.sale_price ), 0) AS total_sales

FROM retail.orders  AS transactions

LEFT JOIN retail.lineitem  AS transactions__line_items ON transactions.transaction_id = transactions__line_items.transaction_id

LEFT JOIN retail.store  AS stores ON stores.store_id = transactions.store_id

WHERE

  ((( transactions.order_date ) >= ((TIMESTAMPADD(year,-1, DATE_TRUNC('year', DATE_TRUNC('day', CURRENT_TIMESTAMP)) ))) AND ( transactions.order_date ) < ((TIMESTAMPADD(year,2, TIMESTAMPADD(year,-1, DATE_TRUNC('year', DATE_TRUNC('day', CURRENT_TIMESTAMP)) ) )))))

GROUP BY 1,2)

SELECT store_id, store_name, (rn/20)+1 AS tier_id

    FROM

          (SELECT id as store_id, name as store_name, ROW_NUMBER() OVER (ORDER BY total_sales desc) as rn

          FROM store_tiering_base)

GRANT SELECT ON TABLE retail.LR$CMHS61638805463410_store_tiering TO GROUP looker_pdt_group

-- finished store_tiering => retail.LR$CMHS61638805463410_store_tiering

SELECT

TOP 500

        stores.name  AS "stores.name",

        store_tiering.tier_id  AS "store_tiering.tier_id",

        COUNT(*) AS "stores.count"

FROM retail.store  AS stores

LEFT JOIN retail.LR$CMHS61638805462956_store_tiering AS store_tiering ON stores.store_id = store_tiering.store_id

GROUP BY 1,2

ORDER BY 3 DESC

 

Is there a way to affect this?

The applicable source lookml code is shown below.

Basically Looker is generating multiple SQL statements that are not separated by semicolons.

Thanks,

Mary