Scalar subquery WHERE clause DATA STUDIO

Question

Data studio connector returns an error, which I do not understand. I have done another SQL connector with the same scalar subquery but another source and it works okay. I do not understand what is difference but if I execute that subquery from the main query it can work. I tested all that thins a lot of times. So I believe I need help

DECLARE product_selected STRING;
SET product_selected = (
with total_product_selected as (
with total_product as (
SELECT 
CASE 
WHEN ... THEN ...
WHEN ... THEN ...
ELSE 'hostname'
END AS hostname,
array_to_string(array_agg(distinct ifnull(session_product_type, 'null')), '|') as session_product_type
FROM table
group by hostname
)
SELECT session_product_type FROM total_product
WHERE hostname != 'hostname'
AND REGEXP_CONTAINS(hostname, @dssite)
)
SELECT session_product_type FROM total_product_selected
group by session_product_type);

SELECT *
FROM table
WHERE 
REGEXP_CONTAINS(hostname, @dshostname) 
AND
REGEXP_CONTAINS(session_product_type , product_selected) = True
AND event_date between PARSE_DATE('%Y%m%d', @DS_START_DATE) and PARSE_DATE('%Y%m%d', @DS_END_DATE)

I also tried instead of put out separately product_selected just put inside REGEXP_CONTAINS in ().

I managed to get all needed info in standart sql but in DS I got an error like ' Sorry, we encountered an error and were unable to complete your request.

Error ID: 2bd4a150 '

all of this needed to get string like 'case|case' in regex where clause

Answer 1

You don't need 2 references to "with"

WITH total_product
AS (
    SELECT CASE 
            WHEN...
                THEN...
            WHEN...
                THEN...
            ELSE 'hostname'
            END AS hostname
        , array_to_string(array_agg(DISTINCT ifnull(session_product_type, 'null')), '|') AS session_product_type
    FROM TABLE
    GROUP BY hostname
    )
, total_product_selected
AS (
    SELECT session_product_type
    FROM total_product
    WHERE hostname != 'hostname'
        AND REGEXP_CONTAINS(hostname, @dssite)
    )
SELECT session_product_type
FROM total_product_selected
GROUP BY session_product_type;

Once you run that final query you cannot keep referring to those CTE's so I guess the following is just a different query unrelated to the CTE's:

SELECT *
FROM TABLE
WHERE REGEXP_CONTAINS(hostname, @dshostname)
    AND REGEXP_CONTAINS(session_product_type, product_selected) = True
    AND event_date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE)