Does the PostgreSQL - Execute Snap not support using CTE’s as part of the query? When I try to include 1 or more CTE’s. all I get out of the snap is a status and message field returned, not the actual data.
That’s strange, can you please try a simple SELECT query and check if you’re getting any data in the output?
Try a simple SELECT query on your EXECUTE snap, you should still get your data.
that’s not the point…I want to have 1 or more CTEs building different sets of records and then select from that. this is basic query functionality. I want @Snaplogic to either ack they don’t support, show us how it’s supported, or see it on a roadmap.
It is supported with the PostgreSQL - Execute Snap, I was able to get it working with:
WITH tbl AS (
SELECT ID, Name from Names
)
SELECT DISTINCT Name from tbl
For example, we only support it with a single statement, but you could attach additional CTEs with comma separating them, though.
Hi @chmurray88 To reply your question , yes Postgres Execute snap supports CTEs as part of a query. I took 2 CTEs and did a sample left join (both CTE is referring to same table with diff filter criteria) and the output result is as expected
My SQL statement in Execute snap
with cte as (
select * from
“all_data_types” where c_int <= 1000 limit 100
)
,
cte2 as
(select * from
“all_data_types” where c_int > 1000 limit 100
)
select * from cte
left join
cte2 on cte.c_smallint=cte2.c_smallint
@mdawlekar - thanks for sharing. In my example…I start with one cte…call that cte is the next cte…then select from the 2nd cte. As output all I get is a status and message field, not the fields from the “last” select query.
I just have a placeholder data example, but I see this working in my case:
the SQL Statement is:
with tbl as (
select id, name from public.names
), tbl2 as (
select distinct name from tbl
) select name from tbl2 order by name desc;
Do you have a link to your pipeline (or org, project, pipeline name) we can take a look at it (feel free to DM me if you prefer)
1 Like
@chmurray88 I have used nested CTEs and I am still able to get the output results fields
with inner_cte as (
select * from
“all_data_types” where c_int <= 1000 limit 100)
,
outer_cte as
(select * from
inner_cte where c_int is not null limit 100
)
select * from outer_cte
Please share us your Execute statement that you are using , So I can check and let you know.
1 Like