View OK in SQL Invalid in ER/Studio

I used the Compare & Merge tool to bring in a view from MS SQL 2014.  The view functions as expected on the SQL database engine.  ER/Studio says it is invalid.  Are we not allowed to use a pivot in E/R Studio, or is there some other issue I am missing.  All of the view dependencies are also in E/R Studio, eliminating that as a potential cause.  E/R Studio validation fails with  "Syntax error near 'MAX', line 30, column 2".  Here's the code in question:

CREATE VIEW [BusinessVault].[gshiftKontextUrlTagSatDerived]
AS
SELECT
hub.[hubGshiftKontextURLHk]
,hub.[LoadDate]
,'Derived in BV.gshiftKontextUrlTagSat' [RecordSource]
,[gshiftChannel]
,[gshiftContentType]
,case when [gshiftTrafficToSite] IS NOT NULL
THEN 'Visits From Content to Site'
ELSE 'Content Visits'
END gshiftCategoryType
,[gshiftTrafficToSite]
FROM [Gshift].[hubGshiftKontextUrl] hub
INNER JOIN
(
SELECT [hubGshiftKontextURLHk], [Content Type][gshiftContentType], [Traffic To Site][gshiftTrafficToSite], [Channel][gshiftChannel]
FROM
(
SELECT [hubGshiftKontextURLHk], hubGshiftTagValue, hubGshiftTagName
FROM [BusinessVault].[gshiftTagHub] hub
INNER JOIN [BusinessVault].[gshiftKontextURLTagLnk] lnk
ON [hub].[hubGshiftTagHk] = [lnk].[hubGshiftTagHk]
WHERE hubGshiftTagName IN ('Content Type', 'Traffic To Site', 'Channel')
) AS SourceTable
PIVOT
(
MAX(hubGshiftTagValue)
FOR hubGshiftTagName IN ([Content Type], [Traffic To Site], [Channel])
) AS PivotTable


) lnk ON [hub].[hubGshiftKontextURLHk] = [lnk].[hubGshiftKontextURLHk]

Parents
  • Hi,

    The parser in ER/Studio doesn't catch everything so some views will be marked as invalid even though they are okay views in the database.  I would recommend contacting support and get this logged as a bug.  I do know that the parser for ER/Studio is being worked on and hopefully that will resolve this issue and similar issues in the near future.

    Regards,
    Stan

Reply Children
No Data