Why Does UNPIVOT Work on Compatibility Level 80 DB?

Question

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
        [OPEX_Group] = 'SRC' THEN [Amount] END),0) "Upg MDF"
FROM [Channel_Steering].[dbo].[AQ1_OPEX_View]
UNPIVOT (Amount FOR [Date] IN ( [Jan-14],
                                [Feb-14],
                                [Mar-14],
                                [Apr-14],
                                [May-14],
                                [Jun-14],
                                [Jul-14],
                                [Aug-14],
                                [Sep-14],
                                [Oct-14],
                                [Nov-14],
                                [Dec-14])) UnPiv
WHERE   (   [Channel_Org] IN ('Retail','TPR')
                AND
            [GL Desc] IN ('MDF (OEM)', 'EIP')
        )
GROUP BY
    [Channel_Org],
    YEAR(CAST('1-'+UnPiv.[Date] AS DATE)),
    MONTH(CAST('1-'+UnPiv.[Date] AS DATE))

Answer 1

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.