Using CONCAT on a database table name

Question

I want to concat the table name with the lastDay date as a string, and have this kind of results :

dl-recommendation-engine:NDA_CHANEL_137002018.ga_sessions_20200128

I found this line to get me the day of yesterday

REPLACE(CAST(DATE_SUB(DATE_TRUNC(CURRENT_DATE(), DAY), INTERVAL 1 DAY) as STRING), "-","") I casted it as a string

But now I have to concat all and it doesn't work ...

date,
transaction.transactionId, 
ref.productSKU as productRef, 
associated.productSKU as productAssociated, 
ARRAY_LENGTH(hits.product) as nbProducts

FROM CONCAT("`dl-recommendation-engine:NDA_CHANEL_137002018.ga_sessions_",
REPLACE(CAST(DATE_SUB(DATE_TRUNC(CURRENT_DATE(), DAY), INTERVAL 1 DAY) as STRING), "-",""),
"`") as session,

UNNEST(hits) AS hits,
UNNEST(hits.product) as ref,
UNNEST(hits.product) as associated

AND hits.transaction.transactionId IS NOT NULL
AND ARRAY_LENGTH(hits.product) > 2```

You can't do this without using dynamic SQL to execute the query. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 29, 2020 at 12:55

Sabri Karagönen · Accepted Answer · 2020-01-29 16:11:20Z

2

Why don't you use _TABLE_SUFFIX?

I simplified the query by deleting unnecessary columns here:

SELECT *
FROM `dl-recommendation-engine:NDA_CHANEL_137002018.ga_sessions_*` as session,
WHERE 
_TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY))

answered Jan 29, 2020 at 16:11

Sabri Karagönen

2,3651 gold badge18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thao Ly Over a year ago

Yes that's what i did finally , thank you very much :)

Collectives™ on Stack Overflow

Using CONCAT on a database table name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related