Pierre-olivier Gendraud
Pierre-olivier Gendraud

Reputation: 1897

Minus all doesn't work although minus works

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?

code

Upvotes: 1

Views: 189

Answers (2)

MT0
MT0

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

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

Related Questions