January 24, 2007 at 7:51 am
I have a view ( a complicated one) that returns single INT column. The view works fine and always returns the proper values but when I
SELECT * FROM my_view
I got one record with the value of 1. When I
SELECT * FROM my_view WHERE ID = 1
no records get returned.
It looks like the view and WHERE does not work when there is only small number of items returned and when there is more then it works.
Does anyone exprienced something like that? Do you know if Microsoft is working on a new SP for 2000?
Any suggestions are welcome.
We are running MSDE SP4.
Thanks
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
January 24, 2007 at 8:00 am
What's the ddl from the original view. Without that it's tough to give an opinion on your problem.
Tom
January 24, 2007 at 8:34 am
The view is working fine. Just the WHERE from the view is going nuts. It is some kind of the bug in the SQL.
But if you would like to have a look at the view here it is:
CREATE VIEW dbo.DV_ReagentsOnboard
AS
SELECT KitID
FROM dbo.DV_ReagentWedgesOnboard -- Lists KitIDs of all reagent wedges on board
WHERE KitID NOT IN ( -- Eliminate from this list all kits with missing wedges:
SELECT R.KitID
FROM dbo.DV_ReagentWedgesOnboard W --Outer join a list of all reagent wedges on board
RIGHT OUTER JOIN dbo.Reagents R ON R.KitID = W.KitID --with the reagents based on the KitID
AND ISNULL(R.WedgeIndex,0) = ISNULL(W.WedgeIndex,0) -- and the wedge index
INNER JOIN dbo.Kits K ON K.KitID = R.KitID -- inner join the Reagents table with kits
AND K.Active <> dbo.DF_EnumStatusGet('Active', 'Deleted') -- to eliminate deleted kits
INNER JOIN dbo.DV_ReagentWedgesOnboard AS W2 -- inner join the Kits table with a list of all reagent wedges on board
ON W2.KitID = K.KitID -- in order to minimize the list of
WHERE W.KitID IS NULL -- Kit Ids where something is missing onboad the instrument
)
Here is the definition of the second view used by the first one
CREATE VIEW dbo.DV_ReagentWedgesOnboard
AS
SELECT DISTINCT
C.KitID,
C.WedgeIndex
FROM dbo.ReagentCarousel AS RC
INNER JOIN dbo.Components AS C ON RC.ComponentID = C.ComponentID
INNER JOIN dbo.Kits AS K ON K.KitID = C.KitID -- kit is not deleted
AND K.Active <> dbo.DF_EnumStatusGet('Active', 'Deleted')
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
January 24, 2007 at 11:10 am
I modified the first view by removing the last INNER JOIN (which was not doing much anyway and the logic did not change) and now the WHERE clause on the view works all the time. I guess there is a maximum level of views after which SQL gets a bit unpredictable.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply