Hi John
The example with the inline count also had a joined column in, and on investigation it is to do with that, where a joined column isn't aliased. I've created a simple example that shows the problem:
create table T_PS_TEST (
ID primary key
, DECSR
) as
select 1, 'Test1' from dual union all
select 2, 'Test2' from dual;
-- Create MDB on T_PS_TEST, edit query to this (notice DUMMY isn't alias qualified) :
Select m.rowid,
m.ID,m.DECSR,DUMMY
from PAUL.T_PS_TEST m
left join dual on m.ID = 1
When I edit DESCR, this happens:
--------------------------------------------------------------------------------
Timestamp: 01/12/2022 10:14:41
SAVEPOINT LOCK_
Elapsed time: 0.022
--------------------------------------------------------------------------------
Timestamp: 01/12/2022 10:14:41
SELECT ID AS "_1", DECSR AS "_2", DUMMY AS "_3" FROM PAUL.T_PS_TEST
WHERE
ROWID = 'AAEdXVAAAAABdxjAAA'
FOR UPDATE NOWAIT
Error occurred: [904] (ORA-00904: "DUMMY": invalid identifier
)
--------------------------------------------------------------------------------
Timestamp: 01/12/2022 10:14:41
ROLLBACK TO SAVEPOINT LOCK_
Elapsed time: 0.023
If I change the query, aliasing dual and the Dummy column, it works:
Select m.rowid,
m.ID,m.DECSR,d.DUMMY
from PAUL.T_PS_TEST m
left join dual d on m.ID = 1
Timestamp: 01/12/2022 10:18:20
SAVEPOINT LOCK_
Elapsed time: 0.025
--------------------------------------------------------------------------------
Timestamp: 01/12/2022 10:18:20
SELECT ID AS "_1", DECSR AS "_2" FROM PAUL.T_PS_TEST
WHERE
ROWID = 'AAEdXVAAAAABdxjAAA'
FOR UPDATE NOWAIT
Old_ROWID=['AAEdXVAAAAABdxjAAA']
Elapsed time: 0.028
In my actual case, the columns in many tables have a column prefix (yuk, not my design), so aliasing isn't necessary. E.g. T_MY_TABLE, columns would be MT_ID, MT_DESCR etc. At least I now know a workaround.
Whilst work is being done in this area, if I load a MDB set, could you add the filename to the hint you get when hover over a MDB tab please? E.g. SCHEMA@INSTANCE Master-Detail Browser - C:\User\Paul\Test.md. I often have many open and it's hard to see at a glance which I loaded from.
Many thanks @Pavel for your efforts in this area!
Regards
Paul
