title: UNNEST ARRAY and UNION ALL to other TABLES with String Value
body: <p>Currently I have two CTEs each looking at the same table. In one I am dealing with regular records.</p>
  <pre><code>WITH unsubscribe_logs AS (
          SELECT Subscriber_Key                                                                                 AS cust_sf_id,
              'Email Unsubscribe'                                                                               AS engagement_type,
              Email_Att1                                                                                        AS campaign_code,
              PARSE_TIMESTAMP('%m/%d/%Y %I:%M:%S %p', Insert_Local_DT)                                          AS engagement_datetime,
              Email_Name                                                                                        AS details,
              CAST(NULL as STRING)                                                                              AS url,
              CAST(NULL as STRING)                                                                              AS link_name,
              CAST(NULL as STRING)                                                                              AS domain,
              unsubscribe_type                                                                                  AS sub_engagement_type,
              JobID                                                                                             AS job_id,
              CAST(NULL as INT64)                                                                               AS list_id,
              CAST(NULL as INT64)                                                                               AS batch_id,
              source_timestamp                                                                                  AS source_timestamp,
              ingestion_timestamp                                                                               AS ingestion_timestamp
          FROM `dl_salesforce_marketingcloud_uk.behaviour_log_salesforce_daily_unsubscribe_v1`
          WHERE 1=1
              AND CAST(source_timestamp AS date) &gt;= IFNULL(filter_date,&quot;1990-01-01&quot;)
  ),
  
   unsubscribe_logs_part_two AS (
          SELECT Subscriber_Key                                                                                 AS cust_sf_id,
              'Email Unsubscribe'                                                                               AS engagement_type,
              Email_Att1                                                                                        AS campaign_code,
              PARSE_TIMESTAMP('%m/%d/%Y %I:%M:%S %p', Insert_Local_DT)                                          AS engagement_datetime,
              Email_Name                                                                                        AS details,
              CAST(NULL as STRING)                                                                              AS url,
              CAST(NULL as STRING)                                                                              AS link_name,
              CAST(NULL as STRING)                                                                              AS domain,
              JobID                                                                                             AS job_id,
              CAST(NULL as INT64)                                                                               AS list_id,
              CAST(NULL as INT64)                                                                               AS batch_id,
              source_timestamp                                                                                  AS source_timestamp,
              ingestion_timestamp                                                                               AS ingestion_timestamp,
              split (unsubscribe_type, &quot; and &quot;)                                                                 AS unsub_type_parts
          From `pah_andrew_test.unsubscribe_testing`
          WHERE 1=1
              AND unsubscribe_type != 'Marketing' AND unsubscribe_type != 'Reminder' AND unsubscribe_type != ''
              AND CAST(source_timestamp AS date) &gt;= IFNULL(filter_date,&quot;1990-01-01&quot;)
  ),
  
  all_salesforce_engagement as
                    (SELECT
                          NULLIF(cust_sf_id, '')                                                                            as cust_sf_id,
                          engagement_type                                                                                   as engagement_type,
                          NULLIF(campaign_code, '')                                                                         as campaign_code,
                          engagement_datetime                                                                               as engagement_datetime,
                          NULLIF(details, '')                                                                               as details,
                          NULLIF(url, '')                                                                                   as url,
                          NULLIF(link_name, '')                                                                             as link_name,
                          NULLIF(domain, '')                                                                                as domain,
                          NULLIF(TRIM(sub_engagement_type), '')                                                             as sub_engagement_type,
                          CAST(job_id AS STRING)                                                                            as job_id,
                          CAST(list_id AS STRING)                                                                           as list_id,
                          CAST(batch_id AS STRING)                                                                          as batch_id,
                          source_timestamp                                                                                  as source_timestamp,
                          ingestion_timestamp                                                                               as ingestion_timestamp
  
                    FROM
                                  (SELECT * FROM unsubscribe_logs
                                  UNION ALL
                                  SELECT * EXCEPT (unsub_type_parts) FROM unsubscribe_logs_part_two LEFT JOIN unnest(unsub_type_parts) as sub_engagement_type)),
  
  </code></pre>
  <p>I then need to union all the two CTEs there is a mismatch in the type so is there a way to cast my null values which come through as numeric values when split? or a way to just stop the union all query from failing.</p>
  <p>Understand this may not be best but learning SQL on the job so any help is appreciated. Basically i need to stop the mismatch so that the CTE can deal with the numeric type after the unnest.</p>