Using RowId To deduplicate result and getting: Ora-ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

Question

Readying here and there how to solve my use case, i came up with the following to deduplicate the result of a query i am performing.

Basically, in the code below, in the Links Table, we have Nodes and Controls.

Controls are things that Link Nodes together with a directionality expressed in the nType.

However Each row only gives half of the info. In other words, each row is a link between a node and a control.

I am trying to create a table where there is both nodes and the control in the middle.

The issue is repetition, i.e. duplicate.

If we have

Protein01 Biding   Ntype0
Molecule01 Binding Ntype 1

In the end i want one row

Protein01 Biding Ntype0 Molecule01 Ntype 1

The problem is given the self join here, i get

Protein01 Biding Ntype0 Molecule01 Ntype 1
Molecule01 Biding Ntype1 Protein01 Ntype 0

I need to eliminate one of the row, and i wrote the code below. But then i get the nasty error.

Ora-ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

What am i doing wrong ?

With dup AS
 (
         select "Links"."idControl",
                "ControlTypes"."sObjectTypeName" as "ControlType",
                "Links"."idNode",
                "NodeTypes"."sObjectTypeName"    as "NodeType",
                "Links"."nLinkType"              as "nLinkType1",
                "Links2"."idNode"                as "idNode2",
                "Links2"."sObjectTypeName"       as "Node2Type",
                "Links2"."nLinkType"             as "nLinkType2"
         from "Links"
                  join "Nodes" on "Links"."idNode" = "Nodes".ID
                  join "Controls" on "Links"."idControl" = "Controls".ID
                  join "ObjectTypes" "NodeTypes" on "Nodes"."idObjectType" = "NodeTypes".ID
                  join "ObjectTypes" "ControlTypes" on "Controls"."idObjectType" = "ControlTypes"."ID"
                  join (select "Links2"."idNode",
                               "Links2"."idControl",
                               "NodeTypes2"."sObjectTypeName",
                               "Links2"."nLinkType"
                        from "Links" "Links2"
                                 join "Nodes" "Nodes2" on "Links2"."idNode" = "Nodes2".ID
                                 join "Controls" "Controls2" on "Links2"."idControl" = "Controls2".ID
                                 join "ObjectTypes" "NodeTypes2" on "Nodes2"."idObjectType" = "NodeTypes2".ID
                                 join "ObjectTypes" "ControlTypes2" on "Controls2"."idObjectType" = "ControlTypes2"."ID"
         ) "Links2" On "Links2"."idControl" = "Links"."idControl" and "Links2"."idNode" != "Links"."idNode"
)
select * from dup a where rowid in ( select max(rowid) from dup b where a."idNode" = b."idNode2");

Note that the Links Table has a primary key which Links.ID

Answer 1

Your sample view is quite large and sample data is very unclear so I will use some synthetic data and view.

To deduplicate the rows you just need to have some other identifier instead of rowid. Also you can apply some analytic function over the view to determine which row to leave. From the question it looks like you need to leave random row. In this case you can use row_number to create identifier within the column group which should be deduplicated. Assume that you have the view with columns key1, key2, tail1, tail2, tail3 and the key1, key2 combination should be unique. In this case you can apply

with data_view as (
    select 1 key1, 1 key2, 1 tail1, 1 tail2, 1 tail3 from dual
    union all
    select 1, 1, 2, 2, 2 from dual
    union all
    select 1, 1, 3, 3, 3 from dual
    union all
    select 2, 2, 1, 2, 3 from dual
    union all
    select 2, 2, 3, 2, 1 from dual
    union all
    select 2, 2, 2, 2, 2 from dual
),
dub as (
    select row_number() over(partition by key1, key2 order by tail1) rn,
        key1, key2, tail1, tail2, tail3
    from data_view
)
select key1, key2, tail1, tail2, tail3 from dub a where rn = 1

As the other option - keeping the code like in question, you can just use rownum pseudocolumn inside view to create unique row identifier and then use it instead of rowid like

with data_view as (
    select 1 key1, 1 key2, 1 tail1, 1 tail2, 1 tail3 from dual
    union all
    select 1, 1, 2, 2, 2 from dual
    union all
    select 1, 1, 3, 3, 3 from dual
    union all
    select 2, 2, 1, 2, 3 from dual
    union all
    select 2, 2, 3, 2, 1 from dual
    union all
    select 2, 2, 2, 2, 2 from dual
),
dub as (
    select rownum rn,
        key1, key2, tail1, tail2, tail3
    from data_view
)
select * from dub a
where rn in (select max(rn) from dub b where a.key1 = b.key1 and a.key2 = b.key2)

See also fiddle