This question is not answered

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

Follow / 11 Jan 2018 at 6:43pm

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!

Replies

All Replies
  • Follow / 12 Jan 2018 at 2:19pm

    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

  • Follow / 12 Jan 2018 at 8:26pm

    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_FirstName
    FROM dbo.tbl_Users B
    INNER JOIN dbo.tbl_Users A ON A.

    Thanks!

  • Follow / 13 Jan 2018 at 2:19am

    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

  • Follow / 13 Jan 2018 at 2:38am

    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.

  • Follow / 13 Jan 2018 at 2:40am

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

  • Follow / 13 Jan 2018 at 2:43am

    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.

  • Follow / 13 Jan 2018 at 3:00am

    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:

    Would you recommend changing any option?

    Thanks for the response!

  • Follow / 13 Jan 2018 at 3:05am

    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

  • Follow / 13 Jan 2018 at 3:11am

    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;

  • Follow / 13 Jan 2018 at 3:21am

    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);

  • Follow / 13 Jan 2018 at 4:16am

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

  • Follow / 13 Jan 2018 at 8:48am

    Awesome, thank you!