sql - How to select the nth column, and order columns' selection in BigQuery

Question

I have this huge table upon which I apply a lot of processing (using CTEs), and I want to perform a UNION ALL on 2 particular CTEs.

SELECT  *
        , 0 AS orders
        , 0 AS revenue
        , 0 AS units
FROM secondary_prep_cte WHERE purchase_event_flag IS FALSE
UNION ALL
SELECT  *
FROM results_orders_and_revenues_cte

I get a "Column 1164 in UNION ALL has incompatible types : STRING,DATE at [97:5]

Obviously I don't know the name of the column, and I'd like to debug this but I feel like I'm going to waste a lot of time if I can't pin-point which column is 1164. I also think this is a problem of the order of columns between the CTEs, so I have 2 questions:

1. How do I identify the 1164th column
2. How do I order my columns before performing the UNION ALL

I found this similar question but it is for MSSQL, I am using BigQuery

标签： sqlgoogle-bigquery

Answer 1

您可以从中获取信息，INFORMATION_SCHEMA.COLUMNS但您需要从 CTE 创建表或视图：

CREATE OR REPLACE VIEW `project.dataset.secondary_prep_view` as select * from (select 1 as id, "a" as name, "b" as value)

然后：

SELECT * FROM dataset.INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'secondary_prep_view';