Alternatives for a LEFT OUTER JOIN query Problem

I have a SQL statement with an LEFT OUTER JOIN which works fine on our Microsoft SQL Server. My problem is that i have to be compatible with H2 Database and this one got a bug with OUTER JOINS.



SQL Query

SELECT *
FROM tSysNls
WHERE nlsGuid IN
    ( SELECT nlsGuid = CASE 
    WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid 
    WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid 
    END
    FROM tSysNLS en
    LEFT OUTER JOIN tSysNLS de ON en.nlsAttribute=de.nlsAttribute
        AND en.nlsClazz=de.nlsClazz
        AND de.nlsLocale= 'de'
    LEFT OUTER JOIN tSysNLS de_AT ON de.nlsAttribute=de_AT.nlsAttribute
        AND de.nlsClazz=de_AT.nlsClazz
        AND de_AT.nlsLocale= 'de_AT'
    WHERE en.nlsLocale= 'en'
        AND en.nlsClazz= 'Contact'
        AND en.nlsAttribute= 'firstName')




Table

CREATE TABLE tsysNLS
(
   nlsGuid           nvarchar(207)   NOT NULL,
   nlsLocale         nvarchar(5)     NOT NULL,
   nlsClazz          nvarchar(100)   NOT NULL,
   nlsAttribute      nvarchar(100)   NOT NULL,
   nlsDisplayName    nvarchar(255)   NOT NULL,
   nlsOldname        nvarchar(50),
   nlsDescription    nvarchar(255),
   nlsShapefilename  nvarchar(10)
);




Example data

INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.en', N'en', N'Contact', N'firstName', N'Name - First Name', N'conFirstName', N'Name - First Name', NULL);
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de', N'de', N'Contact', N'firstName', N'Vorname', N'conFirstName', NULL, N'ConNamVor');
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de_AT', N'de_AT', N'Contact', N'firstName', N'Vorname (AT)', N'conFirstName', NULL, N'ConNamVor');
You can rewrite the LEFT JOIN to a subquery, but it may still compile as an outer join query - I have no knowledge of those aspects of the H2 platform.

SELECT *
FROM tSysNls
WHERE nlsGuid IN (
    SELECT COALESCE(
           --- first option:
           (SELECT de_AT.nlsGuid
            FROM tSysNLS AS de_AT
            WHERE de_AT.nlsClazz=x.nlsClazz AND
                  de_AT.nlsAttribute=en.nlsAttribute AND
                  de_AT.nlsLocale='de_AT'),
           --- second option:
           (SELECT de.nlsGuid
            FROM tSysNLS AS de
            WHERE de.nlsClazz=x.nlsClazz AND
                  de.nlsAttribute=en.nlsAttribute AND
                  de.nlsLocale='de'),
           --- third option:
           en.nlsGuid)
    FROM tSysNLS AS en
    WHERE en.nlsLocale='en'
        AND en.nlsClazz='Contact'
        AND en.nlsAttribute='firstName');


Or you could build some type of temp table (or a regular table if H2 doesn't support temp tables), then populate it in three steps. Again, this will probably still compile using an anti-semijoin or some other type of outer join, but maybe it'll function as a workaround for your bug:

--- 1. de_AT, if there are any:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de_AT' AND nlsClazz='Contact' AND nlsAttribute='firstName';

--- 2. de for those that don't have de_AT:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

--- 3. en for the remaining records:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='en' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

SELECT * FROM #temptable;


But these are not pretty solutions.