Skip to content
Advertisement

selecting columns which are NOT of a specific type SQL

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!

Advertisement

Answer

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

Table: enter image description here

Output: enter image description here enter image description here

10 People found this is helpful
Advertisement