bug - Refactor mistakenly identifying error in ranking func

fabianc2kfabianc2k Posts: 8
edited July 30, 2007 8:29PM in SQL Refactor Previous Versions
SELECT  1,
                ROW_NUMBER() OVER ( ORDER BY [Survey Element Order].[Order] ASC ),
                ROW_NUMBER() OVER ( ORDER BY [Survey Element Order].[Order] ASC ),
                [Question].[Question ID],
                [Question].[Question Category ID]
        FROM    dbo.[Question]
                INNER JOIN dbo.[Element]
                    ON [Question].[Question ID] = [Element].[Question ID]
                INNER JOIN dbo.[Survey Element Order]
                    ON [Element].[Element ID] = [Survey Element Order].[Element ID]
        WHERE   ( [Survey Element Order].[Survey ID] = 30 )
                AND ( [Question].[Question Type] = N'Custom' )
                AND [Question].Demographic = 'false'
        ORDER BY [Survey Element Order].[Order]

The ROW_NUMBER() functions are both identified with a red squiggly line, and the message:
This object was not qualified because Refactor was unable to find ROW_NUMBER in the database

I am having a number of problems with ranking functions and resharper... I will post another after this.

Comments

  • Eddie DEddie D Posts: 1,389 Rose Gold 1
    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: [email protected]
    Eddie Davis
    Technical Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • 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
    
Sign In or Register to comment.