I have table with records:
CREATE TABLE dwh_dds.sales (
region TEXT,
product TEXT,
sales_amount NUMERIC
) DISTRIBUTED randomly;
INSERT INTO dwh_dds.sales (region, product, sales_amount) VALUES
('North', 'product_a', 100),
('North', 'product_b', 150),
('South', 'product_a', 200),
('South', 'product_b', 250);
When i use crosstab function and table from greerplum base (dwh_dds.sales) i have error when run dbt model:
function cannot execute on a QE slice because it accesses relation “dwh_dds.sales” (seg22 slice1 192.168.111.195:6006 pid=1403063)
CONTEXT: SQL statement ” select * from dwh_dds.sales order by region “
— dbt model:
WITH
crosstab_query AS (
SELECT *
FROM crosstab(
$$select * from dwh_dds.sales order by region$$,
$$select * from (select 'product_a'::text p union select 'product_b'::text) s order by p$$
) AS ct (
region TEXT,
product_a NUMERIC,
product_b NUMERIC
-- Добавьте больше продуктов по необходимости
)
)
SELECT * FROM crosstab_query
When i don’t use table, rows formed in query, I don’t have error, dbt understand crosstab construction, model compiled successfully
WITH
crosstab_query AS (
SELECT *
FROM crosstab(
$$ select * from (
SELECT 'a'::text region, 'product_a'::text product, 100::NUMERIC sales_amount
union all
SELECT 'b'::text region, 'product_b'::text product, 200::NUMERIC sales_amount) s
ORDER BY 1$$,
$$select * from (select 'product_a'::text p union select 'product_b'::text) s order by p$$
) AS ct (
region TEXT,
product_a NUMERIC,
product_b NUMERIC
-- Добавьте больше продуктов по необходимости
)
)
SELECT * FROM crosstab_query
How to solve error “function cannot execute on a QE slice because it accesses relation “dwh_dds.sales” (seg22 slice1 192.168.111.195:6006 pid=1403063)” ?
How to change dbt model?