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]ASSELECT 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] hubINNER 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 SourceTablePIVOT( MAX(hubGshiftTagValue) FOR hubGshiftTagName IN ([Content Type], [Traffic To Site], [Channel])) AS PivotTable
) lnk ON [hub].[hubGshiftKontextURLHk] = [lnk].[hubGshiftKontextURLHk]
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.
Thank you for feedback when that bug is fixed and in a release, you will get an email saying it's fixed. As a result this case will be sent to Development waiting on that out come.
But there is no official ETA timeline for such Improvement implementation, which should occur in a future version release.
Here is a Bug ID #: ERS-26557 for your references.
If you need other help, please log a support request online here: http://www.embarcadero.com/support
and our support representative will contact to help.