Question

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

  • 7 December 2021
  • 0 replies
  • 16 views

Hello

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

 

 


This topic has been closed for comments