PostgreSQL - Execute Snap
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.
Forum Discussion
11 Replies
@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.
That’s strange, can you please try a simple SELECT query and check if you’re getting any data in the output?
yes I get data out of a select snap… @Snaplogic …does the snap I’m asking about not support CTEs?
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 tblFor 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)
You are right, I have also faced the same issue twice. As a solution, I removed the subquery from the CTE table. Also raised the same issue with SnapLogic via Corporate Infrastructure management team and got below response.
Issue /Situation Issue with Postgres execute snap
Current Status: You are seeing an issue with postgre execute snap when executing CTE with Sub expressions
Next Action:Our Dev team is currently working on a fix for this issue where if we are using any regular expression function inside CTEs then the user sees just the success message rather than the actual output.The fix is currently in testing phase and this fix will be released tentatively with a 4.34 GA release which is planned for the month of Aug 2023. Meanwhile you can try generic JDBC snap as work arounf and let me know if that works for you
The response from the team is unclear because I raised the issue about subquery but got an answer on regex expression.
Will update on the more, If I’ll get further update on this.
Thanks,
VirenderHi,
I want to use output parameter from sql server execute snap to postgre sql execute snap. This parameter contains a query that I want to run as it is using Postgre SQL execute snap. I have tried the syntax $Query. Its not working. Please help
