Master-Detail Browser / Toad Version

Hi Paul,

Thanks for the details. I think we're more or less on the same page here.

  1. I haven't determined yet why you have edit problems after the SQL edits described above, but it's on my to-do list. There is probably some option you can change for this. I'll be looking at this again soon.
  2. this is fixed for next beta
  3. this is fixed for next beta
  4. next beta will have a drop-down arrow next to "add missing columns" that will just "find missing columns" and offer to copy to clipboard. Then you can paste that into your SQL. I can maybe come back later to look at adding w/o breaking your sQL.
  5. Next beta will also have save/save as buttons.

@Pavel is the one you can thank for these changes, not me. :slight_smile:

1 Like

Hi Paul,

I've looked at the edit/delete problem further. I found that master-detail actually is issuing the statement like "SELECT ID, COL1 as "_1", COL2 as "_2" except for me, it was not including the fields that are not part of the table, so I had no errors when editing/deleting.

The SQL wasn't coming up in Spool SQL (I'll fix that) which is why I thought it wasn't being executed. Maybe you got yours from SQL Tracker.

Anyway, my SQL was:

Select m.rowid, m.*,
       (select count(*) from dept d where d.deptno= m.empno) as dept_cnt
from   JDORLON.AA_EMP1 m

Was yours exactly as shown above or did you simplify it for the purpose of the post to the forum?

-John

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

Hi Paul,

Yeah we can add the file name to the form context. How about master->detail table name instead?
Seems more user friendly. I'm thinking just the first detail, if there are multiple. If you don't like that and would prefer to see file name, do you really want full path or just file name?

Thanks for the details on the edit/delete thing. Yup, looks like aliases are the key here. I'll see if I can make any improvement with that.

-John

Hi John

All good suggestions.

I like the idea of master->detail table name but many of my MDB sets are multiple levels deep, so not sure how the naming would work there? E.g.

MASTER
|-------DETAIL(1)
|              |------------SUB-DETAIL(1.1)
|              |------------SUB-DETAIL(1.2)
|-------DETAIL(2)
                |------------SUB-DETAIL(2.1)
                |                   |------------SUB-SUB-DETAIL(2.1.1)
                |------------SUB-DETAIL(2.2)

If you go the filename route, I think the full filename in the tab hint (or at least an ellipses version c:\longpath...\lastdir\myfile.md) helps when you have multiple variants of the same MDB name but in different folders.

Regards
Paul

If I just did master and first detail for the context, would that be enough info?

Or Master-first detail (myfile.md)?

I could also put the full path of the filename on the MD status bar.

So, even if I had more detail tables, the hint would be the same.

The filename in the hint with some path context helps me when I have multiple open. When you added that for editor files, I found it invaluable.

Maybe
Master-first detail (c:\longpath...\lastsubdir\myfile.md)?

1 Like

Hi John

Sorry to be a massive PITA but something else I noticed, if I edit a query in MDB to do something like add some joined data and I F4 describe a table, it doesn't allow the dragging of column names into the query editor. Any chance this could be allowed?

Regards.
Paul

Regarding the aliasing of columns issue, if I apply a sort via "Sort / Filter the dataset" button and pick columns to sort that are from a joined table, I get a similar problem of unknown column.

Hi Paul,

I've logged the F4/drag thing to look at next week.

Re: Filter/sort - yeah, that's kind of a known issue. Once you do a view/edit query and make changes, filter/sort isn't getting what it expects so stuff like that can happen. Its best to stick with view/edit query once you've made changes. I'll take a look though to see if I can get it to use the alias. That should prevent this error.

-John

Hi John

Many thanks. I was thinking the sorting thing might be an awkward problem to solve. We had a similar thing with column heading click sorting for grids in our applications. We used one of two approaches:

  1. Use index position of a column if it's not from the master table?
    order by 3 desc, m.MY_TAB_COL
  2. Inline the original query and sort on that
select *
from (
  -- Original Query
  select m.*, c.DESCR
  from MY_TAB m
  left join MY_CHILD_TAB c on c.PARENT_ID = m.ID
) a
order by DESCR, ID

I think the latter was more robust for us, but anyway, I'm sure you have some ideas.

Regards
Paul

I can look at inline, but for now...

For sorting, you can right-click in the grid and enable memory sorting. After that we won't run SQL each time you click a column header. The only downside to that is that it fetches all rows...so it's not ideal for large datasets.

Edit: Actually, it looks like column-header clicking (even w/o memory sorting, and w/o column aliases) is working fine. In that case we use "order by position" instead of "order by field name".

I know you are referring to the Filter/sort dialog. I'm just suggesting a workaround.

Hi John

It's when I want to order by multiple columns, unless there's some way of doing that with column clicks that I'm not aware of?

Regards
Paul

Hi Paul,

Ah. No, there's no way to get multi-column sorting by clicking the column headers. But it's been on my radar for about 20 years. I'll take a look at that. I think it would be easier than making the filter/sort dialog work after view/edit query anyway! :smiley:

-John

Hi John

That'll be great! I often find myself CTRL + Clicking on column headers to do multi-column sorting before forgetting that Toad doesn't yet support it.

Cheers
Paul

Yeah and the nice thing is that it would work for places that don't have filter/sort, namely the Editor but also Query Builder and probably a few other places.

1 Like

Hi John

I noticed another MDB issue (sorry). If you try to apply a filter and it fails (like picking a column you exposed via a join), it reloads the default table query, NOT the manually edited query you had saved in the *.md file or had edited before the filter was applied.

Regards
Paul

Hi Paul.

Thanks. I'll log that and get to it soon. Should be easy enough to go back to the prior SQL.

-John

1 Like

Hi John,

found this thread as I have been constantly dealing with the same issue where if my database connection drops and I have to reconnect, the MDB can't be refreshed. Only choice is to reload from a saved file. You mentioned that this has been fixed? Recently upgraded to 16.2 - was that fix included or is that still in beta?

Thanks

Hi Troy,

All of the changes that Paul and I talked about above went into version 16.3 beta, which you can get here. Also, at that link, you can scroll down a bit and see the change log. Do a CTRL+F and search for "Master-Detail".

16.3 will be officially released, non-beta, in about a month.

-John

1 Like