I want to select all the columns in BigQuery that are not of the type “TIMESTAMP”. I have written the query which returns such columns which is:
SELECT COLUMN_NAME FROM d1.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1' AND DATA_TYPE != 'TIMESTAMP'
But I am struggling to return data from only these columns in SQL, I have tried the following query which results in “Scalar subquery produced more than one element”.
SELECT ( SELECT COLUMN_NAME FROM d1.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1' AND DATA_TYPE != 'TIMESTAMP' ) from `d1.t1`;
Can anyone please help me out here in getting the columns from table ‘t1’ where the datatype!=TIMESTAMP? Thanking you in anticipation!
Applying @Mikhail’s advise in the comment’s section, you can try this query:
BEGIN DECLARE col_not_tmstmp STRING; -- putting the result of your query into a variable SET col_not_tmstmp = (SELECT STRING_AGG(COLUMN_NAME) FROM <your-project>.<your-dataset>.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<your_table>' AND DATA_TYPE != 'TIMESTAMP'); -- use execute immediate to apply the variable to main query EXECUTE IMMEDIATE FORMAT (""" SELECT %s FROM `<your-project>.<your-dataset>.<your_table>` """, col_not_tmstmp ); END