How do I convert this connect by oracle sql query to Postgres recursive with?

Question

 SELECT DISTINCT connect_by_root(po1.product_offer_name) AS Offer
        po.product_offer_id product_offer_id
      FROM product_offer_offer poo,
        product_offer po,
        product_offer po1
      WHERE connect_by_isleaf = 1
      AND po.product_offer_id = poo.child_product_offer_id
      AND po1.product_offer_id = poo.product_offer_id
        START WITH poo.product_offer_id IN
        (SELECT product_offer_id
        FROM product_offer
        WHERE sysdate BETWEEN available_start_date AND available_end_date
        AND product_id        IS NOT NULL
        AND     (:TopLevelOffer = 0 OR product_offer_id = :TopLevelOffer)
        )
        CONNECT BY prior poo.child_product_offer_id = poo.product_offer_id

What should be the recursive query for this oracle code trying to get products and their child products

Welcome to the SO community. The community will help with your issues, but there are certain expectations on you. Please take a few minuets to take the Tour and review How to Ask. Then update your question to include sample data, table definition (ddl scripts), the expected results of that data , all as text - no images. Further clearly describe what you are attempting, not how but what, and where you are having issues. What does the current query produce? — Belayer
– Belayer, Commented Oct 13, 2022 at 22:46

SQLpro · Accepted Answer · 2022-10-13 13:47:53Z

-2

CONNECT BY is a proprietary functionnality for recursive queries. The SQL ISO standard is based on CTE for RECURSIVE queries.

Just convert the query to the ISO standard SQL and everything will be done.

https://www.sqlservercentral.com/articles/recursive-queries-in-sql1999-and-sql-server-2005

answered Oct 13, 2022 at 13:47

SQLpro

5,3121 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Belayer Over a year ago

This is not a valid answer as OP's question could be rephrased as how to convert Oracle proprietary hierarchy query to standard sql? Furthermore references to MS SQL Server do not necessary apply to Postgres.

SQLpro Over a year ago

This paper is about ISO Standard SQL about CTE and recursive queries, and applies to SQL Server and PostGreSQL too, that conforms the standard...

KONDETI AJAY KUMAR Over a year ago

What is the alternative for Connect by isleaf in postgres, everything else is fine but I can't get what condition to use in place of connect by isleaf=1

Collectives™ on Stack Overflow

How do I convert this connect by oracle sql query to Postgres recursive with?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related