|
I thought those who returned syntax error didn't read the context enough.
|
|
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
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.
|
|
|
dBforums
>
I thought those who returned syntax error didn't read the context enough.
02-10-12, 23:57
Linear Mode
