Comment on page
UP10100 - Can not Select Records in an UNNEST Statement
- You tried to select an expression of type row/record within an UNNEST SELECT statement which is not supported. For example:
CREATE SYNC JOB extract_emails
ADD_MISSING_COLUMNS = TRUE
AS INSERT INTO default_glue_catalog.exmaple.tbl MAP_COLUMNS_BY_NAME
UNNEST(SELECT
orderid::STRING as orderid,
$event_date::DATE as partition_date,
MAP_WITH_INDEX(emails) AS emails
FROM default_glue_catalog.example.raw_data_table
WHERE time_filter());
Here, MAP_WITH_INDEX returns a row type with multiple subfields, this can't appear in the SELECT CLAUSE of an UNNEST query.
Use a LET statement and select specific fields of the resulted record, for example:
CREATE SYNC JOB my_merge_job
ADD_MISSING_COLUMNS = TRUE
AS INSERT INTO default_glue_catalog.exmaple.tbl MAP_COLUMNS_BY_NAME
UNNEST(SELECT
orderid::STRING as orderid,
$event_date::DATE as partition_date,
email_records[].index AS emails,
email_records[].value AS emails
FROM default_glue_catalog.example.raw_data_table
LET email_records = MAP_WITH_INDEX(emails)
WHERE time_filter());
By incorporating the LET statement and making use of the subfields fields (
email_records[].index and email_records[].value), you can appropriately handle the record-type expression within the UNNEST SELECT statement.Last modified 3mo ago