I am trying to set up a filtered paging ObjectDataSource for a gridvoew control.
My code works, my query doesn't...
This is OK: Get all rows of data:
"SELECT UCO, country_code, country_name, enabled, concurrency FROM CountryCodes "
Returns all records as expected
This is OK: Getting rows of data with no Filter:
"SELECT UCO, country_code, country_name, enabled, concurrency FROM (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO, country_code, country_name, enabled, concurrency FROM CountryCodes ) As TempRowTable WHERE Row >= 0 AND Row <= 10"
Returns expected data
This is OK: Making sure my WHERE filter works:
"SELECT UCO, country_code, country_name, enabled, concurrency FROM CountryCodes WHERE country_name LIKE '%u%' "
Returns 85 records
This is BROKEN!!!: I want to return 10 rows of filtered data:
"SELECT UCO, country_code, country_name, enabled, concurrency FROM (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO, country_code, country_name, enabled, concurrency FROM CountryCodes ) As TempRowTable WHERE ( Row >= 0 AND Row <= 10 ) AND ( country_name LIKE '%u%' ) "
It Returns 0 records!!!
What am I doing wrong in the last query?
Or even better how do I fix it?
Thanks,
Roger
Why don't you just do SELECT TOP 10 UCO,...|||
if you don't want use top 10.
The problem is that your where clause needs to be in the derived table.
SELECT UCO, country_code, country_name, enabled, concurrency FROM (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO, country_code, country_name, enabled, concurrency FROM CountryCodes WHERE country_name LIKE '%u%') As TempRowTable WHERE ( Row >= 0 AND Row <= 10 )
|||
You will have to post a repro script demonstrating the problem. There is nothing wrong with your query. And it is more efficient using the TOP clause in this case to get the top 10 rows instead of using ROW_NUMBER and then filtering on it.
|||Roger,
Your query is most likely working as it should - it should
return 0 rows, given your data, I believe, since you asked
for those rows with country_name like '%u%' that appear in
the first 10 rows of CountryCodes (ordered by country_name).
Apparently none of the first 10 rows have country_name values
like '%u%'.
If you wanted the first 10 rows with country_name like '%u%',
you can either change the problem Ryan noticed (shown here
using a similar query from the AdventureWorks sample database).
select
FirstName, LastName
from (
select
FirstName, LastName,
row_number() over (order by ContactID) as row
from Person.Contact
where LastName like '%x%'
) as C
where row > 0 and row <= 10
go
or use Ryan's and Anith's suggestion of TOP:
select top (10)
FirstName, LastName
from Person.Contact
where LastName like '%x%'
order by ContactID
Note that a query like yours will return no rows here
as well:
select
FirstName, LastName
from (
select
FirstName, LastName,
row_number() over (order by ContactID) as row
from Person.Contact
) as C
where LastName like '%x%'
and row > 0 and row <= 10
go
Steve Kass
Drew University
rogerw@.discussions.microsoft.com wrote:
> I am trying to set up a filtered paging ObjectDataSource for a gridvoew
> control.
>
> My code works, my query doesn't...
>
> This is OK: Get all rows of data:
> "SELECT UCO, country_code, country_name, enabled, concurrency FROM
> CountryCodes "
> Returns all records as expected
>
> This is OK: Getting rows of data with no Filter:
> "SELECT UCO, country_code, country_name, enabled, concurrency FROM
> (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO,
> country_code, country_name, enabled, concurrency FROM CountryCodes ) As
> TempRowTable WHERE Row >= 0 AND Row <= 10"
> Returns expected data
>
> This is OK: Making sure my WHERE filter works:
> "SELECT UCO, country_code, country_name, enabled, concurrency FROM
> CountryCodes WHERE country_name LIKE '%u%' "
> Returns 85 records
>
> This is BROKEN!!!: I want to return 10 rows of filtered data:
> "SELECT UCO, country_code, country_name, enabled, concurrency FROM
> (SELECT ROW_NUMBER() OVER (ORDER BY country_name ) As Row, UCO,
> country_code, country_name, enabled, concurrency FROM CountryCodes ) As
> TempRowTable WHERE ( Row >= 0 AND Row <= 10 ) AND ( country_name LIKE
> '%u%' ) "
> It Returns 0 records!!!
>
> What am I doing wrong in the last query?
> Or even better how do I fix it?
>
> Thanks,
> Roger
>
>|||
Duh - I thought I had tried that. Must be a clear case of tunnel vision.
Thanks for the help,
Roger
