asked on

Odd View Behavior

I have a view which is something like this:

CREATE VIEW [dbo].[MyView]
AS
SELECT DISTINCT 
    t1.*, 
    CASE
        WHEN
            t1.rn = 1
        THEN
            t1.Field15
        ELSE
            0
    END AS Field15_True
from (
    SELECT
        Table1.*,
        row_number() over ( partition by 
                                        Table1.Field1,
                                        Table1.Field2, 
                                        Table1.Field3,
                                        Table1.Field4,
                                        Table1.Field5,
                                        Table1.Field6,
                                        Table1.Field7,
                                        Table1.Field9,
                                        Table1.Field10,
                                        Table1.Field11,
                                        Table1.Field12,
                                        Table1.Field13,
                                        Table1.Field14
                            order by 
                                        Table1.Field1,
                                        Table1.Field2, 
                                        Table1.Field3,
                                        Table1.Field4,
                                        Table1.Field5,
                                        Table1.Field6,
                                        Table1.Field7,
                                        Table1.Field9,
                                        Table1.Field10,
                                        Table1.Field11,
                                        Table1.Field12,
                                        Table1.Field13,
                                        Table1.Field14
                                        ) rn
    FROM
        dbo.Table1
) t1

Open in new window

If I just run the query within the view (that is, highlight from "SELECT" to the end and click "Execute", I get valid values for "Field15".

However if I run a query of the view:

SELECT
    *
FROM
    dbo.MyView

Open in new window

I get NULLs for Field15.

Why?

ralmada

you get nulls for column Field15 or for Field15_true?

Anyway, using order by clause in a view is not a good idea. You should do that operation outside the view or create a stored procedure or even a table based function, but not a view.

jvejskrab

Can you generate CREATE script of the view and send it here? (including SET OPTIONS)

Clif

ASKER

Sorry, you're right It is Field15_True
But I'll try it wiothout the ORDER BY

The create script for the view is in the OP. I only changed the table and field names, and the view name itself (company policy prevents me from posting the actual table and field names). But aside from that, the script is exactly what came from SQL Server Management Studio.

Well, there is the "default" stuff that appears in every create script:

USE [MyDatabase]
GO

/****** Object:  View [dbo].[MyView]    Script Date: 11/07/2011 13:51:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Open in new window

ralmada

now having a second look at the query, can you explain why you're using row_number - partition by like that?

It looks to me that you're trying to get rid of duplicates there. I'm not familiar with your data, but try something like below. If not please post sample data and the expected result of query. (don't have to be live data, but something that could give me an idea of what you're looking for.

select * from dbo.Table1 a
where a.Field15 = (select max(field15) from Table1 where id = a.id)

Open in new window

Clif

ASKER

The query in question was derived from another question HERE.

The solution worked for a while, it just suddenly stopped working.

ralmada

>>The solution worked for a while, it just suddenly stopped working. <<

maybe you have some issues with indexes there, try rebuilding them by using dbcc or ALTER index see here:

http://msdn.microsoft.com/en-us/library/ms181671.aspx

or maybe there's a new column in your table that needs to be taken into consideration for the partition by...

ASKER CERTIFIED SOLUTION

ralmada

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Clif

ASKER

That worked. Not sure why it worked before (with locks in place).

Thanks