Posted by Chris Hohmann on 09/19/05 21:18

"Joe" <joe@hotmail.com> wrote in message
news:e3Q1VQUvFHA.3764@TK2MSFTNGP09.phx.gbl...
> "Chris Hohmann" <nospam@thankyou.com> wrote in message
> news:OlyvseTvFHA.3080@tk2msftngp13.phx.gbl...
>>
>> The WHERE and ORDER BY clauses should be:
>>
>> WHERE utbl7.fld5 >= @fld1val
>> OR (utbl7.fld5 = @fld1val AND utbl7.Unique_ID > @uniqueid)
>> ORDER BY utbl7.fld5, utbl7.Unique_ID
>
> Hi Chris, thanks for the suggestion but I tried what you suggested and it
> returns zero records when sorting by columns containing NULL values (still
> works perfectly for columns filled with data).
>
> The reason for this is that the SP determines the first value on the page
> as NULL (@fld1val). This has the effect that the WHERE clause becomes
> "WHERE utbl7.fld5 >= NULL OR (utbl7.fld5 = NULL AND utbl7.Unique_ID >
> @uniqueid)", thus returning zero records. I realise there is just a flaw
> in my logic somewhere but I just can't seem to get my head round it.
>
> Any further suggestions?

WHERE utbl7.fld5 >= @fld1val
OR (utbl7.fld5 = @fld1val AND utbl7.Unique_ID > @uniqueid)
OR (utbl7.fld5 IS NULL AND @fld1val IS NULL AND utbl7.Unique_ID >
@uniqueid)
ORDER BY utbl7.fld5, utbl7.Unique_ID

• Next in forum: Re: Stumped by SQL challenge
• Prev in forum: Re: SQL Server paged recordset problem
• Thread view: Re: SQL Server paged recordset problem

[Reply to this message]