Home » SQL & PL/SQL » SQL & PL/SQL » postgres Range Type equivalent in oracle. (Oracle19c)
postgres Range Type equivalent in oracle. [message #683755] Thu, 18 February 2021 03:44 Go to next message
Shrin
Messages: 3
Registered: October 2019
Junior Member
I've a requirement to convert below Postgres queries into Oracle queries.
create temp table test1 as    
SELECT DISTINCT p.childid,
                    p.parentid,
                    tsrange(p.datecreated,
                            coalesce(r.datecreated, 'infinity'::timestamp))
                        AS range,
                    p.createdby
    FROM Invoice p
    JOIN contract USING (childid, parentid)
     LEFT OUTER JOIN LATERAL (
        SELECT Invoice.childid,
               Invoice.parentid,
               Invoice.datecreated,
               rank() OVER (ORDER BY datecreated)
        FROM Invoice
        WHERE Invoice.datecreated > p.datecreated
        AND Invoice.childid = p.childid
        AND Invoice.parentid = p.parentid
         ) r ON (r.rank = 1);

CREATE TEMP TABLE test2(id,
                              childid,
                              parentid,
                              range,
                              flag,
                              createdby) AS
        (SELECT
                nextval('test_seq'),
                childid,
                parentid,
                range,
                FALSE,
                createdby
                FROM test1
                )

UPDATE test2
    SET flag = TRUE
    FROM test2 AS b
    WHERE b.range @> test2.range
    AND b.id != test2.id
    AND b.childid = test2.childid
    AND b.parentid = test2.parentid;
How should I handle the Range types and Operator in Oracle. Can someone suggest?


[Edit MC: add code tags]

[Updated on: Thu, 18 February 2021 11:23] by Moderator

Report message to a moderator

Re: postgres Range Type equivalent in oracle. [message #683756 is a reply to message #683755] Thu, 18 February 2021 06:02 Go to previous messageGo to next message
John Watson
Messages: 8531
Registered: January 2010
Location: Global Village
Senior Member
What do you mean by "How should I handle the Range types and Operator" ? In your SQL, it looks as though you are using "range" to name a column, for example:
orclz>
orclz> select ename, sal+comm AS range from emp;

ENAME                RANGE
---------- ---------------
SMITH
ALLEN                 1900
WARD                  1750
JONES
MARTIN                2650
BLAKE
CLARK
SCOTT
KING
TURNER                1500
ADAMS
JAMES
FORD
MILLER

14 rows selected.

orclz>
Is it a key word, something with a special meaning, in Postgres?
Re: postgres Range Type equivalent in oracle. [message #683757 is a reply to message #683756] Thu, 18 February 2021 08:49 Go to previous messageGo to next message
Shrin
Messages: 3
Registered: October 2019
Junior Member
In my query, I've used tsrange() function and range operators (i.e @>,&&). They are used for Range types in Postgres.
You can refer below link for more information.

https://www.postgresql.org/docs/9.3/functions-range.html
Re: postgres Range Type equivalent in oracle. [message #683758 is a reply to message #683757] Thu, 18 February 2021 11:17 Go to previous messageGo to next message
John Watson
Messages: 8531
Registered: January 2010
Location: Global Village
Senior Member
I'm not going to learn Postgres just to help you for free.

If you describe what your range type, function, and operator do, perhaps someone can tell you the Oracle equivalent. If there is no equivalent, then perhaps you can use CREATE TYPE, CREATE FUNCTION, and CREATE OPERATOR to design your own?
Re: postgres Range Type equivalent in oracle. [message #683759 is a reply to message #683757] Thu, 18 February 2021 11:20 Go to previous message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You should explain us, with examples, what your expressions mean instead of relying we make the effort to read, understand and convert something we don't care about.
Above all when you point to a page which starts with "This documentation is for an unsupported version of PostgreSQL.".

For example, I have no idea of what could be the meaning of:
Operator 	Description 		Example 							Result
@> 		contains range 		int4range(2,4) @> int4range(2,3) 				t
@> 		contains element 	'[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp 	t
and won't read the whole Postgres documentation to know it.

Previous Topic: How to show records on zero on hand (Job pick list)
Next Topic: Trigger on Table (4 merged)
Goto Forum:
  


Current Time: Tue Apr 13 18:22:43 CDT 2021