Hi EveryBody
Our Application generates a statement which invokes a view in a SQL-Server DB.
This statement selects DISTINCT Data-fields from the view
passing a restricted subset of IDs.
Background:
I am using SQL-Server 2008 R2 running on Windows Server 2008 R2
With an example-table I will try to explain the problem.
Given this example table: [TabA]
ID DATA
51 A1
50 A1
110 A5
100 A5
We then create the following view:
CREATE VIEW ViewOnTabA
AS
SELECT
MAX(ta.ID) As ID, ta.DATA
FROM
TabA ta
GROUP BY ta.DATA
GO
With this statement, generated by our application,
we call the view passing some ID values:
SELECT
ID, DATA
FROM ViewOnTabA
WHERE ID in (51,50,110,100)
The result is Ok
The Data fields are grouped and complete:
ID DATA
51 A1
110 A5
If instead of all IDs we pass only the smaller ID number: (50 instead of 51)
...
WHERE ID in (50,110,100)
The result is incomplete (ID 50 is missing):
ID DATA
110 A5
But we expected
ID DATA
50 A1
110 A5
It seems that the Aggregate->MAX in the view works whith the ID of the
whole [TabA] table and not whith the IDs of the calling statement(WHERE ID in ...).
Executing this Select in a normal query including
the (WHERE ID in ...) restriction it works fine:
SELECT
MAX(ta.ID) As ID, ta.DATA
FROM
TabA ta
WHERE ID in (50,110,100)
GROUP BY ta.DATA
output>>>>
ID DATA
50 A1
110 A5
For simulating this example
here are the create & insert statements:
create table TabA
(
ID int ,
DATA varchar(8)
)
go
insert into TabA values (51,'A1')
insert into TabA values (50,'A1')
insert into TabA values (110,'A5')
insert into TabA values (100,'A5')
Any help would be greatly appreciated.
Regards,
Alberto