I'm trying to create a view table and would like to custom one of my field name based on latest date. For example like this:
SELECT SUM(total_settlement) AS CONCAT('total_settlement_', CAST(DATE_TRUNC(CURRENT_DATE(), WEEK) AS STRING))
FROM `xxx.dataset.table_source`
WHERE transaction_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
However, I got an error in that field-aliasing part. Is it possible to do this?
Thank you
You can try for an alternative solution, first calculate SUM() and Coll name seperately then apply PIVOT over the result.
Note: On BigQuery we can Column names with character ("-"), we need to replace it.
Your modified Query will be as
SELECT Sum_value , REGEXP_REPLACE(Coll_name , "-", "_") Coll_name FROM
SELECT SUM(total_settlement) AS Sum_value , CONCAT('total_settlement_', CAST(DATE_TRUNC(CURRENT_DATE(), WEEK) AS STRING)) AS Coll_name
FROM `xxx.dataset.table_source`
WHERE transaction_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE())
Output response will be
Then you can apply PIVOT as per reff given above.
Final result will be as
Consider much simpler solution
EXECUTE IMMEDIATE '''
SELECT SUM(total_settlement) AS total_settlement_''' || REGEXP_REPLACE('' || DATE_TRUNC(CURRENT_DATE(), WEEK), '-', '_') ||
''' FROM `xxx.dataset.table_source`
WHERE transaction_date BETWEEN CURRENT_DATE() - 7 AND CURRENT_DATE()
'''
