How to solve STRUCT errors in this query?

Question

I am trying to get 'video IDs/ project code' from different tables and adding them all to my base table. All of the video IDs have a brand name which I tried to get by cross join. I got to the below query and I am getting errors (No matching signature for operator || for argument types: DATE, STRUCT. Supported signatures: STRING || STRING; BYTES || BYTES; ARRAY || ARRAY on my key error'code'), does anyone know how can I get this query to work? My main goal is to get all of the 'video IDs/ project code' in one table so that I can get the metadata later on. I would appreciate any help.

WITH
base AS
    (
        SELECT
            LOWER(date || project_code || k.parentbreand ) key,
            date,
            project_code asset,
            k.parentbreand brand,
        FROM
            UNNEST((SELECT GENERATE_DATE_ARRAY(${when(incremental(),`${constants.ads_starting_date}`, `${constants.last_fy_start}`)}, ${constants.ads_ending_date}))) AS date  
            CROSS JOIN (SELECT DISTINCT ParentBrand FROM ${ref("master_brand_key_views")} UNION ALL
             SELECT DISTINCT Parent_Brand FROM ${ref("master_brand_parent_list")} WHERE (New_Vertical = '*' AND Parent_Brand <> '*') 
                OR LOWER(Parent_Brand) in ('*','*','*','*','*','*','*','*')) k

            CROSS JOIN ((SELECT COALESCE(REGEXP_EXTRACT(upper(title), r'([A-Za-z0-9]+)SC\b'),
            regexp_extract(upper(title),r'\d{6,7}?'), 
            regexp_extract(upper(title),r'\b[A-Za-z0-9]{6,7}\b')) FROM ${ref({database: "datamart-internal", schema: "video", name: "snapchat"})} WHERE COALESCE(REGEXP_EXTRACT(upper(title), r'([A-Za-z0-9]+)SC\b'),
            regexp_extract(upper(title),r'\d{6,7}?'), 
            regexp_extract(upper(title),r'\b[A-Za-z0-9]{6,7}\b')) is not null) 
            UNION ALL (Select universal_video_id FROM ${ref({database: "datamart-internal", schema: "video", name: "facebook"})} where universal_video_id is not NULL)

            UNION ALL (Select project_code FROM ${ref({database: "datamart-internal", schema: "video", name: "youtube_detailed"})} where project_code is not NULL)

            UNION ALL (Select project_code FROM ${ref({database: "datamart-internal", schema: "video", name: "jwplayer_performance"})} where project_code is not NULL)) as project_code 
    
     
         LEFT JOIN ${ref("master_brand_parent_list")} p ON k.ParentBrand = p.Parent_Brand 
         LEFT JOIN ${ref({database: "datamart-internal", schema: "video", name: "snapchat"})} s ON k.ParentBrand = s.brand 
         LEFT JOIN ${ref({database: "datamart-internal", schema: "video", name: "facebook"})} f ON k.ParentBrand = f.brand 
         LEFT JOIN ${ref({database: "datamart-internal", schema: "video", name: "youtube_detailed"})} y ON k.ParentBrand = y.parent_brand
         LEFT JOIN ${ref({database: "datamart-internal", schema: "video", name: "jwplayer_performance"})} j ON k.ParentBrand = j.brand 

    ),

I made these changes and it worked: LOWER(ARRAY_TO_STRING([CAST(date AS STRING),project_code.project_code,k.parentbrand],"")) key, date, project_code.project_code, k.parentbrand brand, — Zahra Safdari, Apr 12 '22 at 12:52

score 0 · Answer 1 · answered Apr 12 '22 at 12:54

I made these changes and it worked: it seems that it could not read correctly the big cross join.

select
LOWER(ARRAY_TO_STRING([CAST(date AS STRING),project_code.project_code,k.parentbrand],"")) key,
 date,
 project_code.project_code,
 k.parentbrand brand,

from(the rest)

How to solve STRUCT errors in this query?

1 Answers1