Hi,

I have a question about a query which I want to share with you. Maybe
someone knows how this strange behavior occurs.

Here is what I do:

SELECT top 1 actstage FROM v_OpportunityFase WHERE oppId = 247

This produces the result 4, which in my case is correct.

And now the following query:

SELECT ISNULL((SELECT top 1 actstage FROM v_OpportunityFase WHERE
oppId = 247), 7)

This query produces the result 5, this is very unexpected.

Does someone know why these two queries do not produce the same
result?

Thanks,
Rik Moed

Rik Moed (rik.moed@capgemini.com) writes:
> Here is what I do:
>
> SELECT top 1 actstage FROM v_OpportunityFase WHERE oppId = 247
>
> This produces the result 4, which in my case is correct.
>
> And now the following query:
>
> SELECT ISNULL((SELECT top 1 actstage FROM v_OpportunityFase WHERE
> oppId = 247), 7)
>
> This query produces the result 5, this is very unexpected.
>
> Does someone know why these two queries do not produce the same
> result?

Because you didn't ask for any particular result, you asked for any
result. If you need a certain result, you must use an ORDER BY clause.

Then again, if you are looking for the smallest number, surely

SELECT coalesce(min(actstage), 7) FROM v_OpportunityFase WHERE oppId = 247

is better.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp