I use TDP 4.3 and this issue has been present for a while.
I have a table that is structured something like this:
So the data could look like this:
G1234, A0005, A0005, Doe, JohnG1234, A0005, A0003, Doe, JaneG1234, 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 OwnerFirstNameFROM 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?
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:
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:
Here's the query I built. It gives me a list of field names when I type the A "dot":
But this is what I get when I type B "dot"
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_FirstNameFROM dbo.tbl_Users B INNER JOIN dbo.tbl_Users A ON A.
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.
when I fully qualify the tables and wait for drop down I get the correct column joins
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.
It works fine for me in Oracle. It just has the delay. Do have all of the code completion options enabled?
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.
Same issue here. (I marked out my user id.) It's Oracle 126.96.36.199 on the server (not sure if that is any difference).
Here are my auto completion options:
Would you recommend changing any option?
Thanks for the response!
That option page looks fine. what about this one?
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
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;
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);
Okay, i finally replicated it. There is an issue here. I entered QAT-12225 to fix.
Awesome, thank you!