postgres Range Type equivalent in oracle. [message #683755] |
Thu, 18 February 2021 03:44 |
|
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 |
John Watson
Messages: 8938 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 #683758 is a reply to message #683757] |
Thu, 18 February 2021 11:17 |
John Watson
Messages: 8938 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 |
|
Michel Cadot
Messages: 68665 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.
|
|
|