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

  • Hello Stanley,

    it's over 4 years since your previous comment, but it seems like the bug is still in its place. And it continues to create many obstacles.

    I had a chance to try ER/Studio 19.1 (one of the recent) on postgresql model. When I imported the DB to the reverse-engineered model, most of working views didn't pass built-in validation. Actually the most primitive did. At the same time at physical level of real DB they all work with no problem. Interesting that even some words like 'MATERIALIZED' or 'EXCEPT' simply considered as an error. Despite the parser is set to 'Postgre SQL 10.x-12.x' apparently it doesn't correspond the actual version of Postgre SQL within DB. The worst is that when I generated new script for the updated DB, all invalid views appeared at the end of the script in alphabetic order (I guess). It took me hours to rebuild them within the script manually in order to re-build dependencies between views. So it has become not only a parser problem, it becomes a usability bottleneck.

    Do you think, there is a chance to overcome this issue in the nearest updates? Actually ER/Studio is a great piece of software but 'the devil hides in tiny details'.

    Thank you for sharing your thoughts!

  • Hello all,

    once you've read up to here, here is the 'glitch' that can be used to a least generate your SQL in proper order.

    Here is how I trick the built-in parser ER/Studio:

    1. Get the list of sources (tables, other views) which are really needed for you view;

    2. Make a generic (blanket) SELECT on DDL tab of view using FROM to list all your sources, i.e.:

    CREATE VIEW My_View  AS

    SELECT * from

    table_1, table_2, .... table_k, view_1, view_2, view_X;

    This will let you insert you dummy view with the name My_View in the right place while generating the SQL. Make sure code is primitive enough to pass the validation;

    3. Go to the  PreSQL&PostSQL tab and switch to PostSQL sub-tub;

    4. Place here code

    DROP VIEW My_View;

    in the first line;

    5. Then in the second line copy-paste the actual code of your view, which ER/Studio doesn't validate starting with CREATE VIEW....

    6. Push OK and generate the SQL.

    As the result first dummy code will be generate right where you need your view and immediately after that the view will be dropped and re-created in the same place with actual code, that doesn't pass the validation within ER/Studio.

    Still this is just a glitch, hopefully in this millennium some people from Idera will have a look at this problem and fix it :-)

    The same approach can apply to both reverse and direct engineering process. Hope this helps, guys!

Reply
  • Hello all,

    once you've read up to here, here is the 'glitch' that can be used to a least generate your SQL in proper order.

    Here is how I trick the built-in parser ER/Studio:

    1. Get the list of sources (tables, other views) which are really needed for you view;

    2. Make a generic (blanket) SELECT on DDL tab of view using FROM to list all your sources, i.e.:

    CREATE VIEW My_View  AS

    SELECT * from

    table_1, table_2, .... table_k, view_1, view_2, view_X;

    This will let you insert you dummy view with the name My_View in the right place while generating the SQL. Make sure code is primitive enough to pass the validation;

    3. Go to the  PreSQL&PostSQL tab and switch to PostSQL sub-tub;

    4. Place here code

    DROP VIEW My_View;

    in the first line;

    5. Then in the second line copy-paste the actual code of your view, which ER/Studio doesn't validate starting with CREATE VIEW....

    6. Push OK and generate the SQL.

    As the result first dummy code will be generate right where you need your view and immediately after that the view will be dropped and re-created in the same place with actual code, that doesn't pass the validation within ER/Studio.

    Still this is just a glitch, hopefully in this millennium some people from Idera will have a look at this problem and fix it :-)

    The same approach can apply to both reverse and direct engineering process. Hope this helps, guys!

Children
No Data