Reputation: 1897
SELECT jt.*
FROM JSON_TABLE (
TO_CLOB ('[{"A":1,"B":11},{"A":1,"B":11}]'),
'$[*]'
COLUMNS (A VARCHAR2 (200) PATH '$.A', B VARCHAR2 (200) PATH '$.B'))
AS jt
MINUS --all
SELECT jt.*
FROM JSON_TABLE (
TO_CLOB ('[{"A":1,"B":11},{"A":12,"B":111}]'),
'$[*]'
COLUMNS (A VARCHAR2 (200) PATH '$.A', B VARCHAR2 (200) PATH '$.B'))
AS jt
the first query return this table
A | B |
---|---|
1 | 11 |
1 | 111 |
the second query return this table
a | b |
---|---|
1 | 11 |
12 | 111 |
with minus or minus all I should become this table
a | b |
---|---|
1 | 11 |
that's what what I've got with minus but minus doesn't work why?
Upvotes: 1
Views: 189
Reputation: 167972
The MINUS ALL
operator was introduced in Oracle 21c and that version does include it in the Set Operators documentation.
Your db<>fiddle is using Oracle 18c which does not have that feature and it is not in the Oracle 18 Set Operators documentation or the Oracle 19 Set Operators documentation.
If you want to use MINUS ALL
then upgrade to use Oracle 21.
Upvotes: 2
Reputation: 15893
You can achieve it with the help of row_number() window function.
Since in below query every A and B combination has unique row numbers only matching number of rows will be removed.
Query:
select row_number()over(partition by A,B order by A,B)rn,A,B from (SELECT jt.*
FROM JSON_TABLE (
TO_CLOB ('[{"A":1,"B":11},{"A":1,"B":11}]'),
'$[*]'
COLUMNS (A VARCHAR2 (200) PATH '$.A', B VARCHAR2 (200) PATH '$.B'))
AS jt)
MINUS --all
select row_number()over(partition by A,B order by A,B)rn,A,B from (SELECT jt.*
FROM JSON_TABLE (
TO_CLOB ('[{"A":1,"B":11},{"A":12,"B":111}]'),
'$[*]'
COLUMNS (A VARCHAR2 (200) PATH '$.A', B VARCHAR2 (200) PATH '$.B'))
AS jt)
Output:
RN | A | B |
---|---|---|
2 | 1 | 11 |
db<>fiddle here
Upvotes: 1