Editor Issue: Joining table to itself does not enable auto-complete for 2nd table.

Toad Data Point
1

Hi,

I use TDP 4.3 and this issue has been present for a while.

I have a table that is structured something like this:

Group_ID
Account_ID
Member_ID
LastName
FirstName

So the data could look like this:

G1234, A0005, A0005, Doe, John
G1234, A0005, A0003, Doe, Jane
G1234, A0005, A0001, Doe, Boy

Uniqueness is the first 3 columns. But I want the LastName and FirstName of the account holder to be on every row I return. So I write a query like this:

SELECT A.Group_ID, A.Account_ID, A.MemberID, A.LastName As MemberLastName, A.FirstName as MemberFirstName, B.LastName as OwnerLastName, B.FirstName as OwnerFirstName
FROM memberdata A
INNER JOIN memberdata B
ON A.Group_ID = B.Group_ID AND A.Account_ID = B.Account_ID;

As I’m typing the query in the editor window, everything with an “A” alias will let me auto complete. I type in A “dot” and a list of fields will pop up allowing me to select what field I want. However nothing with the “B” alias does this.

Hoping there is a setting somewhere I missed, or maybe this is a bug?

Thanks!

2

hi T.Tripp,

I’ve experienced this problem before when i wasn’t using a local instance of a server. Because of that sometimes it took like 20 seconds until the “pop up” showed up… but i literally had to stop pressing keys on keyboard to give the popup some time… So basically pressing “dot” to command the popup to start loading and wait… Usually it only happened once and then, when it was loaded for the first time the next attempts were instant (cached).

If you dont feel this is your case then could you please answer my below questions:

  1. further specific what database providers do you use? (SQL server, Oracle, MySQL)
  2. Is it a heterogeneous connection (cross-querry)?
  3. Does it happen always?
  4. Does it also happen if you switch tables (from B join A)?
    thanks,

Martin

3

Thanks for the reply.

It does this on every data source I have connected to (which includes DB2 z/OS, Oracle, and SQL Server). I don’t do cross-connection queries. It happens all the time. If anything, when I type the “dot”, it will pop up with something, but it’ll be filled with numbers instead of column names.

I just created a test table in SQL Server to illustrate. See attached images. When I type A “dot” it gives me the field names. When I type B “dot” it gives me a single 1 as an option (which is a unique value of the AccountHolder_ID column)

Here’s what the data looks like:

Snap4.png

Here’s the query I built. It gives me a list of field names when I type the A “dot”:

Snap1.png

But this is what I get when I type B “dot”

Snap2.png

The “1” listed is the unique value of AccountHolder_ID column in the table, so it looks like it’s trying to give me the values of the column instead of a column list.

I tried, as you asked, to switch the tables (B to A). It does the same. As I type the join, I’ll say ON B. and it will give me a list of columns on B, but then A stalls. Although if I swap those two in the join and type A “dot” first after the ON statement, it won’t pop up anything (query below)

SELECT A.[User_ID],
A.LastName,
A.FirstName,
B.LastName AS AccountHolder_LastName,
B.FirstName AS AccountHolder_FirstName
FROM dbo.tbl_Users B
INNER JOIN dbo.tbl_Users A ON A.

Thanks!

4

i get similar results when you do not fully qualify the table before the alias. Code completion has to parse the sql and try to resolve the alias and it looks like it is having difficulty.

Here is my example with similar results.

not qualified.png
not qualified.png827×368 33 KB

when I fully qualify the tables and wait for drop down I get the correct column joins

fully qualify.png
fully qualify.png808×449 30 KB

5

Try it on Oracle. I actually use Oracle most often. I fully qualify there (although not much more you can do beyond user.table_name format). Same difficulty there, too.

6

It works fine for me in Oracle. It just has the delay. Do have all of the code completion options enabled?

7

I have whatever the default options are (I haven’t specifically enabled or disabled anything). Let me see if I can grab some additional screen shots.

8

2703.Snap1.png
2703.Snap1.png838×220 13.8 KB

Same issue here. (I marked out my user id.) It's Oracle 11.2.0.4 on the server (not sure if that is any difference).

Here are my auto completion options:

2703.Snap2.png

Would you recommend changing any option?

Thanks for the response!

9

That option page looks fine. what about this one?

1323.options.png

If those are the same then we will need to get the DDL for the two tables including foreign key constraints. You might want to open a support ticket so you have more privacy of your files

10

Yep, same options.

Actually my test setup was created by running this script below.

CREATE TABLE MYUSER.TBL_TEST
(
USER_ID INT NOT NULL,
ACCTHOLDER_ID INT NOT NULL,
LST_NAME VARCHAR (25),
FRST_NAME VARCHAR (25)
);

INSERT INTO MYUSER.TBL_TEST VALUES(1, 1, ‘SMITH’, ‘JOHN’);
INSERT INTO MYUSER.TBL_TEST VALUES(2, 1, ‘SMITH’, ‘JANE’);
COMMIT;

11

Here’s the DDL script as Toad creates:

–DDL Script for TABLE “MYUSER”.“TBL_TEST”
CREATE TABLE “MYUSER”.“TBL_TEST”
(
“USER_ID” NUMBER(,0) NOT NULL ENABLE,
“ACCTHOLDER_ID” NUMBER(,0) NOT NULL ENABLE,
“LST_NAME” VARCHAR2(25),
“FRST_NAME” VARCHAR2(25) ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40
INITRANS 1
MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE (
INITIAL 163840
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS”;

–AlterTable for “MYUSER”.“TBL_TEST”
ALTER TABLE “MYUSER”.“TBL_TEST” MODIFY (“ACCTHOLDER_ID” NOT NULL ENABLE)
ALTER TABLE “MYUSER”.“TBL_TEST” MODIFY (“USER_ID” NOT NULL ENABLE);

12

Okay, i finally replicated it. There is an issue here. I entered QAT-12225 to fix.

13

Awesome, thank you!