Google BigQuery Legacy Syntax Help Needed

Question

I'm having troubles converting a Google BigQuery statement from Standard SQL to Legacy SQL. For context, I have posted the Standard SQL and respective table schema.

In a nutshell...the code below selects the 'latest' (AS-IS) version of a Product Hierarchy for reporting. This was done with the use of STRUCTs in Standard SQL. I'm not sure how to do this in legacy SQL.

Any help would be greatly appreciated! clbarrineau

Standard SQL Example

    
SELECT    STR_NBR
,         SKU
,         SKU_CRT_DT
,         DS.*
,         (
           SELECT AS STRUCT
                     X.*
           FROM  (
                 SELECT  *
                 ,       ROW_NUMBER() OVER(ORDER BY EFF_BGN_DT DESC) AS ROW_NUM
                 FROM    SLS.PROD_HIER
                 ) AS X
           WHERE ROW_NUM = 1
           ) AS P_HIER
FROM      `XXXX.YYYY.SKU_STR_SLS_20141201` SLS
,         UNNEST(DAILY_SALES) AS DS;


Schema Definition
STR_NBR--------------------------------STRING-----------NULLABLE
SKU------------------------------------INTEGER----------NULLABLE
SKU_CRT_DT-----------------------------DATE-------------NULLABLE
DAILY_SALES----------------------------RECORD-----------REPEATED
DAILY_SALES.SLS_DT---------------------DATE-------------NULLABLE
DAILY_SALES.*(many other attributes) --XXXX-------------XXXX
PROD_HIER------------------------------RECORD-----------REPEATED
PROD_HIER.eff_bgn_dt-------------------DATE-------------NULLABLE
PROD_HIER.*(many other attributes) ----XXXX-------------XXXX

Can you explain why you need to use legacy SQL instead of standard SQL? — Elliott Brossard

Elliott Brossard Elliott Brossard · Accepted Answer · 2016-10-28T05:52:04

A couple of suggestions, though you may want to contact Tableau's support to ask what the status of being able to use standard SQL is as well. In some tools, it's possible to force standard SQL by putting #standardSQL at the top of the query.

For legacy SQL, instead of the comma operator with UNNEST, you'll need to use FLATTEN. Something like FLATTEN(XXXX.YYYY.SKU_STR_SLS_20141201, DAILY_SALES.SLS_DT), for example. Since you want to compute row numbers prior to flattening, though, you may need to apply FLATTEN to the subquery itself. My legacy SQL is a bit rusty, so I don't want to lead you astray with a non-functional query, but take a look at some of the other SO questions about FLATTEN to see how it's used.

Google BigQuery Legacy Syntax Help Needed

1 Answers