While the Validator and MS SQL Server returned syntax error,
DB2 returned expected result without syntax error.

What did you think of my opinion wrote in Title?

Quote Originally Posted by tonkuma View Post
Modified examples passed the test on Mimer SQL Developers - Mimer SQL-2003 Validator

The result of Example 1 was
Code:
Result:

The following features outside Core SQL-2003 are used:

F391, "Long identifiers"
T611, "Elementary OLAP operations"
F591, "Derived tables"
F401, "Extended joined table"
The result of Example 2 was
Code:
Result:

The following features outside Core SQL-2003 are used:

F391, "Long identifiers"
T611, "Elementary OLAP operations"
T491, "LATERAL derived table"
F401, "Extended joined table"

If used "or" instead of "aor", the Validator also returned syntax error, like
Code:
Result:

SELECT cc.Surname 
     , ga.InterestGroupADesc 
     , gb.InterestGroupBListADesc 
 FROM  dbo.aspnet_cprdContacts                 AS cc 
 LEFT  OUTER JOIN 
       ( 
       SELECT ai .ContactCode 
            , iga.InterestGroupADesc 
            , ROW_NUMBER() 
                 OVER( PARTITION BY ai.ContactCode 
                           ORDER BY iga.InterestGroupADesc 
                     ) AS rnum 
        FROM  dbo.aspnet_AreaofInterest        AS ai 
        INNER JOIN 
              dbo.aspnet_InterestGroupA        AS iga 
          ON  ai.InterestCode = iga.InterestGroupAID 
       )                                       AS ga 
 FULL  OUTER JOIN 
       ( 
       SELECT or.ContactCode 
              ^-
syntax error: or
  correction: <identifier>

            , igb.InterestGroupBListADesc 
...
...

I thought those who returned syntax error didn't read the context enough.

Here is the result of test on DB2.

Example 3:
Note 1: replaced table names with alias itself like
/*dbo.aspnet_cprdContacts AS*/ cc
so on...
Note 2: WITH clause was used to generate test data.
Code:
------------------------------ Commands Entered ------------------------------
WITH
  cc(Surname , ContactCode) AS (
VALUES
  ( 'Mascrier'  ,  1 )
, ( 'Newdecade' ,  9 )
, ( 'Zolotow'   , 99 )
)
, ai(ContactCode , InterestCode) AS (
VALUES
  ( 1 , 11 )
, ( 1 , 12 )
, ( 1 , 13 )
, ( 9 , 19 )
)
, iga(InterestGroupAID , InterestGroupADesc) AS (
VALUES
  ( 11 , 'Biologics'       )
, ( 12 , 'Devices'         )
, ( 13 , 'Health'          )
, ( 19 , 'Auto Mobile'     )
, ( 99 , 'Special Mission' )
)
, or(ContactCode , ObsResearchCode) AS (
VALUES
  ( 1 , 21 )
, ( 1 , 22 )
, ( 9 , 92 )
, ( 9 , 93 )
)
, igb(InterestGroupBListAID , InterestGroupBListADesc) AS (
VALUES
  ( 21 , 'Data Linkage'            )
, ( 22 , 'Device Related Research' )
, ( 92 , 'Electric Veicle'         )
, ( 93 , 'Hydrogen Fuel Veicle'    )
)
SELECT cc.Surname
     , ga.InterestGroupADesc
     , gb.InterestGroupBListADesc
 FROM  /*dbo.aspnet_cprdContacts          AS*/ cc
 LEFT  OUTER JOIN
       (
       SELECT ai .ContactCode
            , iga.InterestGroupADesc
            , ROW_NUMBER()
                 OVER( PARTITION BY ai.ContactCode
                           ORDER BY iga.InterestGroupADesc
                     ) AS rnum
        FROM  /*dbo.aspnet_AreaofInterest        AS*/ ai
        INNER JOIN
              /*dbo.aspnet_InterestGroupA        AS*/ iga
          ON  ai.InterestCode = iga.InterestGroupAID
       ) AS ga
 FULL  OUTER JOIN
       (
       SELECT or .ContactCode
            , igb.InterestGroupBListADesc
            , ROW_NUMBER()
                 OVER( PARTITION BY or.ContactCode
                           ORDER BY igb.InterestGroupBListADesc
                     ) AS rnum
        FROM  /*dbo.aspnet_ObservationalResearch AS*/ or
        INNER JOIN
              /*dbo.aspnet_InterestGroupBListA   AS*/ igb
          ON  or.ObsResearchCode = igb.InterestGroupBListAID
       ) AS gb
   ON  gb.ContactCode = ga.ContactCode
   AND gb.rnum        = ga.rnum
   ON  cc.ContactCode = COALESCE(gb.ContactCode , ga.ContactCode)
-- WHERE cc.Surname = 'Mascrier'
;
------------------------------------------------------------------------------

SURNAME   INTERESTGROUPADESC INTERESTGROUPBLISTADESC
--------- ------------------ -----------------------
Mascrier  Biologics          Data Linkage           
Mascrier  Devices            Device Related Research
Mascrier  Health             -                      
Newdecade Auto Mobile        Electric Veicle        
Newdecade -                  Hydrogen Fuel Veicle   
Zolotow   -                  -                      

  6 record(s) selected.