Maybe it's the wrong function you're using:

AVG(NULLIF(F.WinningBid,0)/NULLIF(F.AuctionSellerReserve, 0))

Straight from BOL (BooksOnLine):

NULLIF returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a...

You need to link the CTE back to your original table. Since I don't have any other field unique field I used the addr field. I expect you have an...

If you compare two character fields and you run into the collation conflict you can change the collation inside the query:

SELECT [object_id]

FROM report_objects

WHERE report_id = @report_id

AND...

How about the following code?

It is based on the assumption that, if there is another blank character after the one that follows the direction separator [dpos], then dpos is a...

Going back to your very first post:

anshu84onnet (1/26/2010)

---

i am writing this query--' Select 8 from table' and this is returning number of rows present in table times 8 as output...

RHaverty 8478 (1/27/2010)

---

This is a function I use to return 0.00 for a null value. The use is

Declare Amt Decimal(10,2)

Select Amt = dbo.ConvertNullToZero(DbAmtVal)

...

If you want all the other columns to be in there as well, how would your expected output look like?

Either you have one row per firstName then you'd have to...

@ Lynn:

I'd experct the second query to be SELECT * instead of another SELECT 8...

Glad it finally worked out! 🙂

Did you notice how fast you had a tested solution once you provided ready to use data? 😉

I see you use the varchar(8000) instead of...

Without table definition and sample data provided as per the first link in my signature we can't really help you.

When I asked for an example I was talking about sample...

Now that we have ready to use data it becomes really easy:

You have to use a dynamic SELECT statement using the ISNULL function inside your dynamic sql statement:

...

Please provide all related table definition together with sample data and expected result in a ready to use format.

The query you provided won't run due to missing tables and columns.

I don't think there will be a "best practice" answer...

It's more like one for the "it depends" category... But I might be wrong.

I, personally would use the first option since...

Would you mind sharing an example so we have something to play with?

You have to apply the ISNULL() function to your outer SELECT statement. That means you cannot use a SELECT *.

Unfortunately, your sample data and your query don't match (columns [Columns]...