I want to change the schema of the nested on my VIEW.
But BigQuery won't do it because I'm calling the record "productPrice".
Indeed, if I don't call this, I can't keep it nested in my view.
Error Message when i use "productPrice"
Column xx in UNION ALL has incompatible types: STRUCT<type STRING, price DOUBLE, currency STRING, ...>, STRUCT<taxRate DOUBLE, taxType STRING, priceStartDate STRING, ...> at [xx:x]
table1
productPrice RECORD NULLABLE
productPrice.type STRING NULLABLE
productPrice.price FLOAT NULLABLE
productPrice.currency STRING NULLABLE
productPrice.priceStartDate STRING NULLABLE
productPrice.taxRate FLOAT NULLABLE
productPrice.taxType STRING NULLABLE
table2
productPrice RECORD NULLABLE
productPrice.taxRate FLOAT NULLABLE
productPrice.taxType STRING NULLABLE
productPrice.priceStartDate STRING NULLABLE
productPrice.currency STRING NULLABLE
productPrice.price FLOAT NULLABLE
productPrice.type STRING NULLABLE
Request with productPrice
CREATE VIEW product_view AS
SELECT
productPrice,
productPrice.taxRate,
productPrice.taxType,
productPrice.priceStartDate,
productPrice.currency,
productPrice.price,
productPrice.type,
from table1
UNION ALL
SELECT
productPrice,
productPrice.taxRate,
productPrice.taxType,
productPrice.priceStartDate,
productPrice.currency,
productPrice.price,
productPrice.type,
FROM table2
Request without productPrice
CREATE VIEW product_view AS
SELECT
--productPrice,
productPrice.taxRate,
productPrice.taxType,
productPrice.priceStartDate,
productPrice.currency,
productPrice.price,
productPrice.type,
from table1
UNION ALL
SELECT
--productPrice,
productPrice.taxRate,
productPrice.taxType,
productPrice.priceStartDate,
productPrice.currency,
productPrice.price,
productPrice.type,
FROM table2
Result without "productPrice" in the view
type STRING NULLABLE
taxRate FLOAT NULLABLE
taxType STRING NULLABLE
priceStartDate STRING NULLABLE
currency STRING NULLABLE
price FLOAT NULLABLE
Below is for BigQuery Standard SQL
#standardSQL
SELECT
STRUCT(
productPrice.taxRate,
productPrice.taxType,
productPrice.priceStartDate,
productPrice.currency,
productPrice.price,
productPrice.type
) AS productPrice
FROM table1
UNION ALL
SELECT
STRUCT(
productPrice.taxRate,
productPrice.taxType,
productPrice.priceStartDate,
productPrice.currency,
productPrice.price,
productPrice.type
)
FROM table2