Why Does UNPIVOT Work on Compatibility Level 80 DB?

I'm trying to pull data using UNPIVOT on a SQL Server 2008 SP3 database running at Compatibility Level 80. This should mean UNPIVOT doesn't work, but in my case it does under certain circumstances...

Works:

Independent SELECT query of the form:

SELECT...FROM...UNPIVOT...WHERE...GROUP BY

Doesn't work:

Same query, within a LEFT JOIN () on other tables on different databases within the same server. All at compatibility level 80.

I get the usual error message:

Msg 325, Level 15, State 1, Line 165
Incorrect syntax near 'UNPIVOT'. You may need to set the compatibility level
of the current database to a higher value to enable this feature. See help for
the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

UNPIVOT workarounds seem cumbersome and I'd like this query to be self-contained and refreshable if possible. It seems logical that if I can get the query to work in isolation, it should be possible to use it in a JOIN.

Questions:

Why does this work sometimes?

How can I achieve the UNPIVOT sub-query inside the JOIN under these conditions?

Unpivot Sub-Query: (By request...)

```
SELECT
YEAR(CAST('1-'+UnPiv.[Date] AS DATE)) "Year",
MONTH(CAST('1-'+UnPiv.[Date] AS DATE)) "Month",
CASE [Channel_Org]
WHEN 'TPR' THEN 'ERP'
ELSE [Channel_Org]
END AS [Channel_Org],
ISNULL(SUM(CASE WHEN [GL Desc] = 'EIP' AND
[OPEX_Group] = 'Postpaid SAC' THEN [Amount] END),0) "Post EIP",
ISNULL(SUM(CASE WHEN [GL Desc] = 'EIP' AND
[OPEX_Group] = 'Prepaid SAC' THEN [Amount] END),0) "Pre EIP",
ISNULL(SUM(CASE WHEN [GL Desc] = 'EIP' AND
[OPEX_Group] = 'SRC' THEN [Amount] END),0) "Upg EIP",
ISNULL(SUM(CASE WHEN [GL Desc] = 'MDF (OEM)' AND
[OPEX_Group] = 'Postpaid SAC' THEN [Amount] END),0) "Post MDF",
ISNULL(SUM(CASE WHEN [GL Desc] = 'MDF (OEM)' AND
[OPEX_Group] = 'Prepaid SAC' THEN [Amount] END),0) "Pre MDF",
ISNULL(SUM(CASE WHEN [GL Desc] = 'MDF (OEM)' AND
[O

The answer is in the error message:


You may need to set the compatibility level of the current database to
a higher value to enable this feature.

Where 'current database' means the context database - i.e. the database the query is executed in, which is not necessarily the same as the database referenced in the query. For example, the following produces the error message:

USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks 
SET COMPATIBILITY_LEVEL = 80;
GO
SELECT
    U.SystemInformationID,
    U.[Database Version],
    U.ColumnName,
    U.Value
FROM AdventureWorks.dbo.AWBuildVersion AS ABV
UNPIVOT 
(
    Value 
    FOR ColumnName IN 
        (
        VersionDate,
        ModifiedDate
        )
) AS U;

The following executes successfully (assuming tempdb has a compatibility level > 80):

USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks 
SET COMPATIBILITY_LEVEL = 80;
GO
USE tempdb; -- Context database
GO
SELECT
    U.SystemInformationID,
    U.[Database Version],
    U.ColumnName,
    U.Value
FROM AdventureWorks.dbo.AWBuildVersion AS ABV -- Still references data in AW DB
UNPIVOT 
(
    Value 
    FOR ColumnName IN 
        (
        VersionDate,
        ModifiedDate
        )
) AS U;

You need to check what the context database is for the connection that produces the error.

StackExchange Database Administrators Q#71750, answer score: 10