locked
Duplicate alias in a SQL statement RRS feed

  • Question

  • Hello Everyone!

    I am getting an error "There is a duplicate table alias 'RAC4' in the FROM clause"

    I did have the table with alias in the Data Environment, but I took it out and opened the table locally, no difference.

    IF !USED("RAC2")
       USE F:\Tv\TvWork Again ALIAS RAC2 IN 0 SHARED
    ENDIF
    IF !USED("RAC4")   
       USE F:\TV\tv_Billmaster Again ALIAS RAC4 IN 0 SHARED
    ENDIF
    IF !USED("RAC3")   
      USE F:\Tv\tv_billdetail Again ALIAS RC3 IN 0 SHARED  
    ENDIF
    thisform.createrwcursor()
    SELECT ;
     RAC4.master_pk as Serial, RAC4.InvNo, RAC4.wono as Ref, RAC4.Bill_Date as InvDate,;
       RAC4.grossamt as Total, RAC4.Discount, RAC4.billamt as Net, RAC4.scope as Work,;
       RAC3.Description as Detail, RAC3.Qty, RAC3.Price, RAC2.usercode as Store;
       FROM RAC4,RAC3,RAC2; 
       INNER Join RAC4 ON RAC2.wono = RAC4.wono ;
       INNER JOIN RAC4 ON RAC3.wono = RAC4.wono ;
        Where RAC2.Client = "09RC01";
         AND Between(RAC2.invDate,ldBegin,ldEnd);
         INTO CURSOR RAC5;     
         ORDER BY RAC4.InvNo

    I can't figure it out....any ideas?

    Thanks again!


    Dennis Downey

    Sunday, April 12, 2015 8:11 PM

Answers

  • You are INNER JOINin RAC4 twice additional to having it in the FROM clause. That can't work, of course.

    PS: Joins are part of the FROM clause, you query FROM all tables you join.

    Bye, Olaf.


    Olaf Doschke - TMN Systemberatung GmbH

    http://www.tmn-systemberatung.de

    • Edited by Olaf Doschke Monday, April 13, 2015 12:08 PM
    • Marked as answer by TransApp Monday, April 13, 2015 3:00 PM
    Monday, April 13, 2015 5:22 AM
  • You're welcome.

    You could even do without all the USE .. ALIAS stuff. you can define alias names in the FROM clause whenever you write FROM or JOIN SOMETABLE AS RACX,eg

    FROM tv_Billmaster as RAC4, tv_billdetail as RAC3, TvWork as RAC2
    INNER JOIN tv_Billmaster as bm2 ON RAC2.field = bm2.field

    Notice I joined tv_billmaster with yet another alias. Each from or joined table has to have a unique alias name, if the table name itself is not unique, especially with such self joins. And of course you also use alias names to shorten queries and still have unambigous names. I prefer alias short names abbreviating table names instead of numbering RAC1,RAC2,RAC3, but that's of course your choice.

    And one more catch: If you yould SET FILTER TO something in your workareas RAC2,3,4 this'll not effect the query result. SQL will always go from newly opeend DBF(alias) anyway, unless you use the new WITH SqlBuffering option, then it'll read from the buffered changes of a workarea, but still not be effected b ya FILTER.

    The only thing you save in your query is initially writing in the full table names. If you'd use a DBC that would only be the table names and no path, which is another reason for DBCs: No need to change any code but OPEN DATABASE, if a data moves to a new path.

    Bye, Olaf.


    Olaf Doschke - TMN Systemberatung GmbH

    http://www.tmn-systemberatung.de

    • Edited by Olaf Doschke Monday, April 13, 2015 3:55 PM
    • Marked as answer by TransApp Monday, April 13, 2015 4:57 PM
    Monday, April 13, 2015 3:49 PM

All replies

  • You are INNER JOINin RAC4 twice additional to having it in the FROM clause. That can't work, of course.

    PS: Joins are part of the FROM clause, you query FROM all tables you join.

    Bye, Olaf.


    Olaf Doschke - TMN Systemberatung GmbH

    http://www.tmn-systemberatung.de

    • Edited by Olaf Doschke Monday, April 13, 2015 12:08 PM
    • Marked as answer by TransApp Monday, April 13, 2015 3:00 PM
    Monday, April 13, 2015 5:22 AM
  • Olaf!!

    Thank You.......you were exactly correct. I put so many hours into this application over the weekend that I got a little  punch drunk and just didn't see the obvious.

    This morning, after reading your response, I sounded like Homer Simpson....."DOH!!!"

    I also didn't see where I created the alias RAC3......I misspelled RAC.....

    You have helped me many times....you are greatly appreciated.


    Dennis Downey

    Monday, April 13, 2015 3:04 PM
  • You're welcome.

    You could even do without all the USE .. ALIAS stuff. you can define alias names in the FROM clause whenever you write FROM or JOIN SOMETABLE AS RACX,eg

    FROM tv_Billmaster as RAC4, tv_billdetail as RAC3, TvWork as RAC2
    INNER JOIN tv_Billmaster as bm2 ON RAC2.field = bm2.field

    Notice I joined tv_billmaster with yet another alias. Each from or joined table has to have a unique alias name, if the table name itself is not unique, especially with such self joins. And of course you also use alias names to shorten queries and still have unambigous names. I prefer alias short names abbreviating table names instead of numbering RAC1,RAC2,RAC3, but that's of course your choice.

    And one more catch: If you yould SET FILTER TO something in your workareas RAC2,3,4 this'll not effect the query result. SQL will always go from newly opeend DBF(alias) anyway, unless you use the new WITH SqlBuffering option, then it'll read from the buffered changes of a workarea, but still not be effected b ya FILTER.

    The only thing you save in your query is initially writing in the full table names. If you'd use a DBC that would only be the table names and no path, which is another reason for DBCs: No need to change any code but OPEN DATABASE, if a data moves to a new path.

    Bye, Olaf.


    Olaf Doschke - TMN Systemberatung GmbH

    http://www.tmn-systemberatung.de

    • Edited by Olaf Doschke Monday, April 13, 2015 3:55 PM
    • Marked as answer by TransApp Monday, April 13, 2015 4:57 PM
    Monday, April 13, 2015 3:49 PM
  • Olaf!

    I took your advice and changed my code.....I like it

    Thanks again for your help


    Dennis Downey

    Monday, April 13, 2015 4:55 PM
  • I can't believe this.....I changed everything.....read a bunch to fully understand what you were saying and the thing still won't work.

    I actually went to the extreme of pulling out the SQL statement and putting it into a separate program so it is easier for me to test........it still won't work.

    I am getting an error message of "SQL Column WONO isn't found" I double checked the select statement itself, I checked the Tables and it exists in each, character (7) in each.

    LOCAL ldbegin,ldend 
    ldbegin = "03/02/2015"
    lend = "03/30/2015"
    SET DEFAULT to F:\TV\
    OPEN DATABASE TV.dbc
    SELECT TV_BILLMASTER.Master_pk as Serial,TV_BILLMASTER.Invno,TV_BILLMASTER.WONO as Ref,;
        TV_BILLMASTER.Bill_date as InvDate,TVwork.usercode as Store,TV_BILLDETAIL.Descr as Detail,;
        TV_Billdetail.QTY,TV_BillDetail.Price,TV_BILLMASTER.Grossamt as Total,TV_BILLMASTER.Discount,;
        TV_BILLMASTER.Billamt as Net,TV_BILLMASTER.Scope as Work;
        FROM TV!TVWORK TVWORK INNER JOIN (TV!TV_BILLDETAIL TV_BILLDETAIL INNER JOIN TV!TV_BILLMASTER TV_BILLMASTER ON TVWORK.WONO = TV_BILLMASTER.WONO) ON TV_BILLMASTER.wono = TV_BILLDETAIL.wono;
        WHERE (TVWORK.CLIENT='09RC03'AND BETWEEN(TVWORK.INVDATE,ldbegin,ldend)) AND TVWORK.wotype<'5';
        INTO CURSOR RAC5;
        ORDER BY TV_BILLMASTER.Invno
        BROWSE
        return

    After testing and testing...I took out the alias names and just went with the actual table names....

    Now......while I was testing this..I wanted to be sure of the error message so I went and ran it again....and I got a blank browse window......it worked but didn't get any records.....it should have gotten 200.

    I have been messing with this simple little SQL statement for 3 days.

    what am I missing?

     


    Dennis Downey

    Tuesday, April 14, 2015 2:24 AM