Describes a bug in GDR and Data Dude where SQL views using derived tables cause unresolved reference errors, impacting database project development and validation.
This is not isolated to GDR, but seams to exist in Data Dude as well. If you create the following SQL:
1 1: CREATE VIEW [dbo].[v_SomeView] AS
1 2: SELECT [BH].[Col1],
1 3: [BH].[Col2],
1 4: [BH].[Col3],
1 5: [BH].[Col4],
1 6: [BH].[Col5],
1 7: CASE WHEN [BHPP].[OtherCol1] IS NULL THEN -1 ELSE 1 END As [Col6],
1 8: CASE WHEN [BHPP].[OtherCol1] IS NULL THEN 'Not Applicable' ELSE 'PowerPack' END As [Col7],
1 9: CASE WHEN [BHPP].[OtherCol1] IS NULL THEN [BH].[Col5] ELSE [BHPP].[OtherCol2] END As [Col8]
1 10: FROM [dbo].[Table1] as [BH]
1 11: LEFT JOIN (SELECT [OtherCol1], [OtherCol2], [OtherCol3]
1 12: FROM [$(CMD)].[dbo].[Table2]
1 13: WHERE [OtherCol1] <> -1) as [BHPP]
1 14: ON [BH].[Col2] = [BHPP].[Col2]
And add it to your Database project, but using proper table names :) You will get the following error for every use of [BHPP]:
Error 13 SR0029 : Microsoft.Validation : View: [dbo].[v_SomeView] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects:….
This is a show stopper for us as we can’t (without good cause) be creating more views just to do a derived table…
I have submitted a Bug , so vote as you like, but please vote…
Bug: GDR - derived tables
Technorati Tags: ALM
If you've made it this far, it's worth connecting with our principal consultant and coach, Martin Hinshelwood, for a 30-minute 'ask me anything' call.
We partner with businesses across diverse industries, including finance, insurance, healthcare, pharmaceuticals, technology, engineering, transportation, hospitality, entertainment, legal, government, and military sectors.
CR2
Schlumberger
MacDonald Humfrey (Automation) Ltd.
Alignment Healthcare
SuperControl
Ericson
Boxit Document Solutions
Emerson Process Management
Teleplan
Slaughter and May
Graham & Brown
NIT A/S
Hubtel Ghana
Trayport
Lockheed Martin
Boeing
Deliotte
Higher Education Statistics Agency
Washington Department of Transport
Department of Work and Pensions (UK)
Nottingham County Council
New Hampshire Supreme Court
Royal Air Force
Washington Department of Enterprise Services
New Signature
Xceptor - Process and Data Automation
Big Data for Humans
Teleplan
Graham & Brown
Trayport