How to convert below query from Oracle to Postgres having connect by?

Question

I am converting my application DB from oracle to postgres. I am stuck on a function having connect by syntax. Below is the Oracle query.

PROCEDURE Get_Report_Data(parm_Billing_Month VARCHAR2, OUT Ref_Cur) IS
BEGIN
OPEN p_Data FOR
  SELECT CASE
           WHEN Id = 1 THEN
            'Amount < 10000'
           WHEN Id = 2 THEN
            '10000-15000'
           WHEN Id = 3 THEN
            '15000-20000'
           ELSE
            'Amount > 20000'
         END "Range",
         SUM(Nvl(N1, 0)) N1,
         SUM(Nvl(N2, 0)) N2,
         SUM(Nvl(C1, 0)) C1,
         SUM(Nvl(C2, 0)) C2,
         SUM(Nvl(C3, 0)) C3,
         SUM(Nvl(S1, 0)) S1,
         SUM(Nvl(S2, 0)) S2,
         COUNT(Site_Id) "No of Sites"
    FROM (SELECT CASE
                   WHEN Nvl(Ed.Actual_Bill, 0) < 10000 THEN
                    1
                   WHEN Ed.Actual_Bill < 15000 THEN
                    2
                   WHEN Ed.Actual_Bill < 20000 THEN
                    3
                   ELSE
                    4
                 END Amount_Sort,
                 Decode(Er.Region_Id, 1, 1, 0) N1,
                 Decode(Er.Region_Id, 2, 1, 0) N2,
                 Decode(Er.Region_Id, 3, 1, 0) C1,
                 Decode(Er.Region_Id, 4, 1, 0) C2,
                 Decode(Er.Region_Id, 5, 1, 0) C3,
                 Decode(Er.Region_Id, 6, 1, 0) S1,
                 Decode(Er.Region_Id, 7, 1, 0) S2,
                 Ed.Site_Id
            FROM Tbl_Details   Ed,
                 Tbl_Site      Es,
                 Tbl_Region    Er,
                 Tbl_Subregion Esr
           WHERE Ed.Site_Id = Es.Site_Id
             AND Es.Subregion_Id = Esr.Subregion_Id
             AND Esr.Region_Id = Er.Region_Id
             AND Ed.Billing_Month_f = parm_Billing_Month) Data,
         (SELECT Regexp_Substr('1,2,3,4,', '[^,]+', 1, Rownum) Id
            FROM Dual
          CONNECT BY Rownum <= Length('1,2,3,4,') -
                     Length(REPLACE('1,2,3,4,', ','))) All_Value
   WHERE Data.Amount_Sort(+) = All_Value.Id
   GROUP BY All_Value.Id
   ORDER BY AVG(All_Value.Id);
END;

When I convert this query to postgres having some changes like Ref_Cur to refcursor and NVL to Coalesce function. I am still unable to resolve the connect by syntax. Some people suggested to use CTE's but I am unable to get it. Any help guys?

Edit For random googlers below is the answer to my above problem. Special thanks to MTO.

WHERE Ed.Site_Id = Es.Site_Id
AND Es.Subregion_Id = Esr.Subregion_Id
AND Esr.Region_Id = Er.Region_Id
AND Ed.Billing_Month_f = p_Billing_Month) data

Right Outer Join (Select 1 as Id union All
                  Select 2 as Id union All
                  Select 3 as Id union All
                  Select 4 as Id) all_value 
            On data.Amount_Sort = all_value.Id
GROUP BY all_value.Id
ORDER BY AVG(all_value.Id);

user330315 · Accepted Answer · 2017-06-20 09:18:27Z

3

The "generation" of IDs can be simplified in Postgres.

either use a values() clause:

Right Outer Join ( values (1,2,3,4) ) as all_value(id) On data.Amount_Sort = all_value.Id

or, if those are always a consecutive numbers, use generate_series():

Right Outer Join generate_series(1,4) as all_value(id) On data.Amount_Sort = all_value.Id

answered Jun 20, 2017 at 9:18

user330315

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

3 Comments

Meta-Coder Over a year ago

Actually in my next reports, there is a complex query returning some numbers. So to simply I needed that syntax that MTO provided. But thanks a lot for the info.

user330315 Over a year ago

@Meta-Coder: I personally find the values () clause a lot simpler to read and maintain as the convoluted select .. union all select ... syntax

Meta-Coder Over a year ago

I totally agree with you. Even in my report I have used values (1,2,3,4) clause, but in my next reports I need a complex query having multiple joins. That was confusing me for the syntax with Connect by. Cheers !!!

MT0 · Accepted Answer · 2017-06-19 10:08:39Z

2

Since your hierarchical query appears to be using static strings, you can convert this:

SELECT Regexp_Substr('1,2,3,4,', '[^,]+', 1, Rownum) Id
FROM Dual
CONNECT BY Rownum <= Length('1,2,3,4,') - Length(REPLACE('1,2,3,4,', ',')

To:

SELECT 1 AS id FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL UNION ALL
SELECT 4 FROM DUAL

Which should then be simpler to convert to PostgreSQL.

answered Jun 19, 2017 at 10:08

MT0

173k12 gold badges70 silver badges136 bronze badges

1 Comment

Meta-Coder Over a year ago

Thanks alot. Very nice and simple answer.

Collectives™ on Stack Overflow

How to convert below query from Oracle to Postgres having connect by?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related