sqlpervasivepervasive-sql

Pervasive SQL v10, Inner Join doesn't work


i am trying to join to tables in Pervasive SQL.

select GUID, b.BankName from Ad 
INNER JOIN AdBankKto b ON 
b.AdGUID = Ad.GUID

But i always get an error:
[LNA][Pervasive][ODBC Engine Interface]Error in expression: GUID

If i delete GUI, i get this error:
[LNA][Pervasive][ODBC Engine Interface]Error in predicate: b . AdGUID = Ad . GUID

Both columns are UNIQUEIDENTIFIER, i absolutly have no idea why this doesn't work, any ideas?

LG


Solution

  • First thing I noticed is that the GUID in your SELECT could be ambiguous. You should alias the field with the table name so something like:

    select Ad.GUID, b.BankName from Ad 
    INNER JOIN AdBankKto b ON 
    b.AdGUID = Ad.GUID
    

    Once properly aliased, the query works for me using PSQL v11. Make sure that the AdGUID exists in the AdBankKTo table. I tested with stub tables and it worked correctly for me:

    Create table AdBankKto (AdGUID uniqueidentifier, BankName char(100));
    create table Ad (GUID uniqueidentifier, Something char(100));
    insert into AdBankKto values ('9AA7B72F-64D4-4F3D-B5BB-716E0309D588', 'BankName');
    insert into Ad values ('9AA7B72F-64D4-4F3D-B5BB-716E0309D588', 'Something');
    
    select Ad.GUID, b.BankName from Ad 
    INNER JOIN AdBankKto b ON 
    b.AdGUID = Ad.GUID