I think you mean ASCII not ANSI???

Lynn Pettis (4/3/2012)

---

bplvid (4/3/2012)

---

I"m back here.When I execute my SP I get it inserted but it throws me msg saying

"Subquery returned more than 1 value. This is not permitted...

aaron.reese (4/3/2012)

---

Sean,

Thanks for that. I can rewrite it as

AND NOT (

D.SubBalA = coalesce(P.SubBalA,0) AND

D.SubBalB = coalesce(P.SubBalB,0) AND

)

Which sort of gets rid of the inequality by reversing the logic,...

bplvid (4/3/2012)

---

I"m back here.When I execute my SP I get it inserted but it throws me msg saying

"Subquery returned more than 1 value. This is not permitted when the...

D-SQL (4/3/2012)

---

Most of the tables have a composite PK, and usually the last column in the key is EffectiveDate. All tables have an EffectiveDate column but we don’t really use...

This is a 3 year old thread. It might be better to start your own since your issue is certainly a bit different. That being said, it depends. There are...

This is a pretty old thread but you are correct this type of approach will work for that sort of "catch all" query. However, Gail wrote a blog post about...

shermola1 (4/3/2012)

---

Thanks again for the suggestions! Sean, I didn't do Insert statements because I wanted to give a larger dataset than I was willing to type out manually and...

Your scan is because your where clause is not sargable.

D.SubBalA<> coalesce(P.SubBalA,0)OR

D.SubBalB<> coalesce(P.SubBalC,0)OR

D.SubBalC<> coalesce(P.SubBalC,0)OR

D.SubBalD<> coalesce(P.SubBalD,0)

You have both the inequality AND a function. both these are non-sargable.

Seems that Lynn and I both agree that storing this data would be better than calculating it on the fly...didn't see the response as I was typing mine. 😛

So just to clarify, you want a list of the counts of all vendors within each distance range for every single zip code on your list? You said something about...

It would be a lot easier if your sample data was insert statements. The idea here is for you to make it as easy as possible for the people helping....

capn.hector (4/2/2012)

---

Koen Verbeeck (4/2/2012)

---

EL Jerry (4/2/2012)

---

Sean,

I'm quite sure the community will agree with me that you deserve the Nobel Patience Prize for your participation in that thread. 🙂

"El"...

It is most likely that scalar function that is causing you issues. Scalar functions can absolutely destroy your server.

If you can post some ddl and sample data I think...

The first step to an answer can be found by reading the first link in my signature.