My database schema is as below:
ID IDLANE COMPONENT (XMLType COlumn) IDDATE
--- ------ --------- ------
111 111R1 <?xml version="1.0" encoding="utf-8"?> 2019-02-10T10:00:00
<Rowsets>
<Rowset Name="Item">
<Row>
<IDLANE>111R1</IDLANE>
<ID>111</ID>
<ORDER>0111000</ORDER>
<ITEM>000010</ITEM>
<MATERIAL>000000000257</MATERIAL>
</Row>
<Row>
<IDLANE>111R1</IDLANE>
<ID>111</ID>
<ORDER>0111000</ORDER>
<ITEM>000020</ITEM>
<MATERIAL>000000000258</MATERIAL>
</Row>
</Rowset>
</Rowsets>
111 111R2 <?xml version="1.0" encoding="utf-8"?> 2019-02-10T11:00:00
<Rowsets>
<Rowset Name="Item">
<Row>
<IDLANE>111R2</IDLANE>
<ID>111</ID>
<ORDER>0111000</VBELN>
<ITEM>000010</ORDER>
<MATERIAL>000000000257</MATERIAL>
</Row>
<Row>
<IDLANE>111R2</IDLANE>
<ID>111</ID>
<ORDER>0111000</ORDER>
<ITEM>000020</ITEM>
<MATERIAL>000000000258</MATERIAL>
</Row>
</Rowset>
</Rowsets>
222 222R1 <?xml version="1.0" encoding="utf-8"?> 2019-02-10T13:00:00
<Rowsets>
<Rowset Name="Item">
<Row>
<IDLANE>222R1</IDLANE>
<ID>222</ID>
<ORDER>0222000</ORDER>
<ITEM>000010</ITEM>
<MATERIAL>000000000257</MATERIAL>
</Row>
<Row>
<IDLANE>222R1</IDLANE>
<ID>111</ID>
<ORDER>0222000</ORDER>
<ITEM>000020</ITEM>
<MATERIAL>000000000258</MATERIAL>
</Row>
</Rowset>
</Rowsets>
222 222R2 <?xml version="1.0" encoding="utf-8"?> 2019-02-10T14:00:00
<Rowsets>
<Rowset Name="Item">
<Row>
<IDLANE>222R2</IDLANE>
<ID>222</ID
<ORDER>0222000</ORDER>
<ITEM>000010</ITEM>
<MATERIAL>000000000259</MATERIAL
</Row>
</Rowset>
</Rowsets>
333 333R1 <?xml version="1.0" encoding="utf-8"?> 2019-02-10T18:00:00
<Rowsets>
<Rowset Name="Item">
<Row>
<IDLANE>333R1</IDLANE>
<ID>333</ID
<ORDER>0333000</ORDER>
<ITEM>000010</ITEM>
<MATERIAL>000000000259</MATERIAL
</Row>
</Rowset>
</Rowsets>
My requirement is to get all the IDs which :
exists more than one AND
<ORDER> is same for all of them AND
<ITEM> is same for all of them AND
<MATERIAL> is same for all of them
For the data that I have provided, the query output should be :
ID IDLANE IDDATE
111 111R1 2019-02-10T10:00:00
111 111R2 2019-02-10T11:00:00
It wont include 222 because both 222 IDs have different Material.
I wrote the query to identify more than one ID as below:
SELECT ID FROM IDTABLE
GROUP BY ID
HAVING COUNT(*) > 1
Above query gives me 111 & 222 as answers because both of the IDs exist more than one. But I need to compare XMLTyple COlumn's , and too with each same ID appearing twice. But I do not know how to compare XML Type columns.
Can anyone provide some inputs.
Thanks
I suppose you need rows with the same data in the columns you are interested in, but with different values in the IDDATE column.
with
t as (
select
111 as ID, '111R1' as IDLANE,
xmltype(q'~
<Rowsets>
<Rowset Name="Item">
<Row>
<IDLANE>111R1</IDLANE>
<ID>111</ID>
<ORDER>0111000</ORDER>
<ITEM>000010</ITEM>
<MATERIAL>000000000257</MATERIAL>
</Row>
<Row>
<IDLANE>111R1</IDLANE>
<ID>111</ID>
<ORDER>0111000</ORDER>
<ITEM>000020</ITEM>
<MATERIAL>000000000258</MATERIAL>
</Row>
</Rowset>
</Rowsets>
~') as COMPONENT, timestamp'2019-02-10 10:00:00' as IDDATE from dual
union all
select
111, '111R2',
xmltype(q'~
<Rowsets>
<Rowset Name="Item">
<Row>
<IDLANE>111R2</IDLANE>
<ID>111</ID>
<ORDER>0111000</ORDER>
<ITEM>000010</ITEM>
<MATERIAL>000000000257</MATERIAL>
</Row>
<Row>
<IDLANE>111R2</IDLANE>
<ID>111</ID>
<ORDER>0111000</ORDER>
<ITEM>000020</ITEM>
<MATERIAL>000000000258</MATERIAL>
</Row>
</Rowset>
</Rowsets>
~'), timestamp'2019-02-10 11:00:00' from dual
union all
select
222, '222R1',
xmltype(q'~
<Rowsets>
<Rowset Name="Item">
<Row>
<IDLANE>222R1</IDLANE>
<ID>222</ID>
<ORDER>0222000</ORDER>
<ITEM>000010</ITEM>
<MATERIAL>000000000257</MATERIAL>
</Row>
<Row>
<IDLANE>222R1</IDLANE>
<ID>111</ID>
<ORDER>0222000</ORDER>
<ITEM>000020</ITEM>
<MATERIAL>000000000258</MATERIAL>
</Row>
</Rowset>
</Rowsets>
~'), timestamp'2019-02-10 13:00:00' from dual
union all
select
222, '222R2',
xmltype(q'~
<Rowsets>
<Rowset Name="Item">
<Row>
<IDLANE>222R2</IDLANE>
<ID>222</ID>
<ORDER>0222000</ORDER>
<ITEM>000010</ITEM>
<MATERIAL>000000000259</MATERIAL>
</Row>
</Rowset>
</Rowsets>
~'), timestamp'2019-02-10 14:00:00' from dual
union all
select
333, '333R1',
xmltype(q'~
<Rowsets>
<Rowset Name="Item">
<Row>
<IDLANE>333R1</IDLANE>
<ID>333</ID>
<ORDER>0333000</ORDER>
<ITEM>000010</ITEM>
<MATERIAL>000000000259</MATERIAL>
</Row>
</Rowset>
</Rowsets>
~'), timestamp'2019-02-10 18:00:00' from dual
),
a as (
select
t."ID", t.IDLANE, t.IDDATE,
count(*) over (
PARTITION BY t."ID", x."order", x.ITEM, x.MATERIAL
) as qnt
from t,
xmltable('//Row'
passing t.component
columns
"order" varchar2(10) path 'ORDER',
item varchar2(10) path 'ITEM',
material varchar2(20) path 'MATERIAL') x
)
select distinct
"ID", IDLANE, to_char(IDDATE, 'yyyy-MM-dd hh24:mi:ss') as IDDATE
from a where qnt > 1;
Output:
+-----+--------+---------------------+
| ID | IDLANE | IDDATE |
+-----+--------+---------------------+
| 111 | 111R2 | 2019-02-10 11:00:00 |
| 111 | 111R1 | 2019-02-10 10:00:00 |
+-----+--------+---------------------+
Test it online with db<>fiddle.