1

Simple Query:

I have a query that connects to a table in a work order management database (Oracle 12c; non-GDB).

select 
    wo.wonum
from 
    maximo.workorder wo

The query returns 428 records in Toad and 428 records in a query layer (in ArcMap).

This result is expected.


Complex Query:

If I change the query so that it joins to a related table:

select 
    cast(rownum as number(38,0)) as rownum_,
    wo.wonum
from 
    maximo.workorder wo
left join 
    maximo.wfassignment a 
    on wo.workorderid = a.ownerid

the query returns 1048 records in Toad and 428 records in the query layer.

This result is unexpected. The query layer ought to have returned 1048 records, just like Toad.


Analysis:

I understand that query layers often have a hard time with queries that are not 1 to 1 with an underlying table.

  • I don't know for sure, but I think query layers like to have a unique ID that comes from a column in a real table.
  • Sure, with simple 1 to 1 queries, rownum can often do the trick as a unique ID. But for complex queries (with joins that propagate-out additional records), rownum seems to be problematic.

A Solution:

Through trial an error, I came up with a solution that works with the joined table in the query layer:

select
    cast(null as number(38,0)) as rownum_,
    null as wonum
from 
    dual
where
    1<>1

union all

select 
    cast(rownum as number(38,0)) as rownum_,
    wo.wonum
from 
    maximo.workorder wo
left join 
    maximo.wfassignment a 
    on wo.workorderid = a.ownerid

The query returns 1048 records in Toad and 1048 records in the query layer.

This result is expected.


Question:

Why does unioning to the dual table make the complex query work properly in the query layer?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.