bug - Refactor mistakenly identifying error in ranking func

Comments

• Eddie D Redgate › Posts: 1,800 Rose Gold 5

  July 30, 2007 1:23PM

  Thank you for your post into the forum.
  
  I am uncertain as to why SQL Refactor could not qualify your ROW_NUMBER() functions.
  
  However SQL Refactor will inform if any objects could not be qualified in the manner you described, in displaying an error dialog box and underlining in red the object name in the query editor.
  
  Eddie Davis
  Red Gate Software Ltd
  Technical Support
  E-Mail: Support@red-gate.com

  Eddie Davis
  Senior Product Support Engineer
  Redgate Software Ltd
  Email: support@red-gate.com

  0 · Share on Twitter
• fabianc2k Posts: 8

  July 30, 2007 8:29PM

  1. Run this create script against an empty database
  2. Smart Rename ColumnTwo
  3. Open the _BadView views
  

  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  IF NOT EXISTS ( SELECT  *
                  FROM    sys.objects
                  WHERE   object_id = OBJECT_ID(N'[dbo].[RowNumberProblem_Table]')
                          AND type in ( N'U' ) ) 
      BEGIN
          CREATE TABLE [dbo].[RowNumberProblem_Table]
              (
                [ColumnOne] [int] IDENTITY(1, 1)
                                  NOT NULL,
                [ColumnTwo] [int] NOT NULL,
                CONSTRAINT [PK_RowNumberProblem_Table] PRIMARY KEY CLUSTERED
                  ( [ColumnOne] ASC )
                  WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                         IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                         ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
              )
          ON  [PRIMARY]
      END
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  IF NOT EXISTS ( SELECT  *
                  FROM    sys.views
                  WHERE   object_id = OBJECT_ID(N'[dbo].[RowNumberProblem_BadView]') ) 
      EXEC dbo.sp_executesql @statement = N'
  CREATE VIEW [dbo].[RowNumberProblem_BadView]
  WITH SCHEMABINDING
  AS
  SELECT     ColumnOne, ColumnTwo, ROW_NUMBER() OVER (ORDER BY ColumnTwo DESC) AS ColumnTwoDescRank
  FROM         dbo.RowNumberProblem_Table
  ' 
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  IF NOT EXISTS ( SELECT  *
                  FROM    sys.views
                  WHERE   object_id = OBJECT_ID(N'[dbo].[RowNumberProblem_GoodView]') ) 
      EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[RowNumberProblem_GoodView]
  AS
  SELECT     TOP (100) PERCENT ColumnOne, ColumnTwo
  FROM         dbo.RowNumberProblem_Table
  ORDER BY ColumnTwo DESC
  ' 
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  IF NOT EXISTS ( SELECT  *
                  FROM    sys.views
                  WHERE   object_id = OBJECT_ID(N'[dbo].[RowNumberProblem_BadViewSchemaBound]') ) 
      EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[RowNumberProblem_BadViewSchemaBound]
  WITH SCHEMABINDING 
  AS
  SELECT     ColumnOne, ColumnTwo, ROW_NUMBER() OVER (ORDER BY ColumnTwo DESC) AS ColumnTwoDescRank
  FROM         dbo.RowNumberProblem_Table
  ' 
  GO
  

  0 · Share on Twitter