COALESCE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

COALESCE

Author

Topic

kristian
Starting Member

21 Posts

Posted - 2002-06-15 : 20:08:46

I am trying to select links based on 1 of 4 possible search criterial. I followed the advice in this article about using coalesce[url]http://www.sqlteam.com/item.asp?ItemID=2077[/url]

And i came up with this procedure

CREATE PROCEDURE Get_Links
(
@Input1 NVARCHAR(40),
@Input2 NVARCHAR(40),
@Input3 NVARCHAR(50),
@Input4 NVARCHAR(50)
)
AS
SELECT ID, Title, URL, Description, Country
FROM Links
WHERE
(
Cat1 = COALESCE(@Input1, Cat1)
OR SubCat1 = COALESCE(@Input1, SubCat1)
OR Cat2 = COALESCE(@Input1, Cat2)
OR SubCat2 = COALESCE(@Input1, SubCat2)
OR Cat3 = COALESCE(@Input1, Cat3)
OR SubCat3 = COALESCE(@Input1, SubCat3)
)
AND (
Gen1 = COALESCE(@Input2, Gen1)
OR SubGen1 = COALESCE(@Input2, SubGen1)
OR Gen2 = COALESCE(@Input2, Gen2)
OR SubGen2 = COALESCE(@Input2, SubGen2)
OR Gen3 = COALESCE(@Input2, Gen3)
OR SubGen3 = COALESCE(@Input2, SubGen3)
)AND
Country = COALESCE(@Input3, Country)
AND
City = COALESCE(@Input4, City)

Thing is it dont seem to work, when i replace the inputs with variables and paste the code into query analizer, it rewrites my code to look like mess

SELECT ID, Title, URL, Description, Country
FROM Links
WHERE (Cat1 = COALESCE (NULL, Cat1)) AND (Gen1 = COALESCE ('Hard House', Gen1)) AND (Country = COALESCE (NULL, Country)) AND
(City = COALESCE (NULL, City)) OR
(Cat1 = COALESCE (NULL, Cat1)) AND (Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND
(SubGen1 = COALESCE ('Hard House', SubGen1)) OR
(Cat1 = COALESCE (NULL, Cat1)) AND (Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND
(Gen2 = COALESCE ('Hard House', Gen2)) OR
(Cat1 = COALESCE (NULL, Cat1)) AND (Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND
(SubGen2 = COALESCE ('Hard House', SubGen2)) OR
(Cat1 = COALESCE (NULL, Cat1)) AND (Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND
(Gen3 = COALESCE ('Hard House', Gen3)) OR
(Cat1 = COALESCE (NULL, Cat1)) AND (Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND
(SubGen3 = COALESCE ('Hard House', SubGen3)) OR
(Gen1 = COALESCE ('Hard House', Gen1)) AND (Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND
(SubCat1 = COALESCE (NULL, SubCat1)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen1 = COALESCE ('Hard House', SubGen1)) AND
(SubCat1 = COALESCE (NULL, SubCat1)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (Gen2 = COALESCE ('Hard House', Gen2)) AND
(SubCat1 = COALESCE (NULL, SubCat1)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen2 = COALESCE ('Hard House', SubGen2)) AND
(SubCat1 = COALESCE (NULL, SubCat1)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (Gen3 = COALESCE ('Hard House', Gen3)) AND
(SubCat1 = COALESCE (NULL, SubCat1)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen3 = COALESCE ('Hard House', SubGen3)) AND
(SubCat1 = COALESCE (NULL, SubCat1)) OR
(Gen1 = COALESCE ('Hard House', Gen1)) AND (Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND
(Cat2 = COALESCE (NULL, Cat2)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen1 = COALESCE ('Hard House', SubGen1)) AND
(Cat2 = COALESCE (NULL, Cat2)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (Gen2 = COALESCE ('Hard House', Gen2)) AND
(Cat2 = COALESCE (NULL, Cat2)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen2 = COALESCE ('Hard House', SubGen2)) AND
(Cat2 = COALESCE (NULL, Cat2)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (Gen3 = COALESCE ('Hard House', Gen3)) AND
(Cat2 = COALESCE (NULL, Cat2)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen3 = COALESCE ('Hard House', SubGen3)) AND
(Cat2 = COALESCE (NULL, Cat2)) OR
(Gen1 = COALESCE ('Hard House', Gen1)) AND (Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND
(SubCat2 = COALESCE (NULL, SubCat2)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen1 = COALESCE ('Hard House', SubGen1)) AND
(SubCat2 = COALESCE (NULL, SubCat2)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (Gen2 = COALESCE ('Hard House', Gen2)) AND
(SubCat2 = COALESCE (NULL, SubCat2)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen2 = COALESCE ('Hard House', SubGen2)) AND
(SubCat2 = COALESCE (NULL, SubCat2)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (Gen3 = COALESCE ('Hard House', Gen3)) AND
(SubCat2 = COALESCE (NULL, SubCat2)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen3 = COALESCE ('Hard House', SubGen3)) AND
(SubCat2 = COALESCE (NULL, SubCat2)) OR
(Gen1 = COALESCE ('Hard House', Gen1)) AND (Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND
(Cat3 = COALESCE (NULL, Cat3)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen1 = COALESCE ('Hard House', SubGen1)) AND
(Cat3 = COALESCE (NULL, Cat3)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (Gen2 = COALESCE ('Hard House', Gen2)) AND
(Cat3 = COALESCE (NULL, Cat3)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen2 = COALESCE ('Hard House', SubGen2)) AND
(Cat3 = COALESCE (NULL, Cat3)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (Gen3 = COALESCE ('Hard House', Gen3)) AND
(Cat3 = COALESCE (NULL, Cat3)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen3 = COALESCE ('Hard House', SubGen3)) AND
(Cat3 = COALESCE (NULL, Cat3)) OR
(Gen1 = COALESCE ('Hard House', Gen1)) AND (Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND
(SubCat3 = COALESCE (NULL, SubCat3)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen1 = COALESCE ('Hard House', SubGen1)) AND
(SubCat3 = COALESCE (NULL, SubCat3)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (Gen2 = COALESCE ('Hard House', Gen2)) AND
(SubCat3 = COALESCE (NULL, SubCat3)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen2 = COALESCE ('Hard House', SubGen2)) AND
(SubCat3 = COALESCE (NULL, SubCat3)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (Gen3 = COALESCE ('Hard House', Gen3)) AND
(SubCat3 = COALESCE (NULL, SubCat3)) OR
(Country = COALESCE (NULL, Country)) AND (City = COALESCE (NULL, City)) AND (SubGen3 = COALESCE ('Hard House', SubGen3)) AND
(SubCat3 = COALESCE (NULL, SubCat3))

Anyone know whats going on? Should i reprogram my procedure to look like the above mess. And is that gonna be efficient. It sure dont look it at first glance to a relative newby.

Any help would be much appreciated.

Thanks

Chris

kristian
Starting Member

21 Posts

Posted - 2002-06-15 : 20:10:08

Ps, when the above code runs in Query Analyzer it does return the records expected.

kristian
Starting Member

21 Posts

Posted - 2002-06-16 : 05:59:15

Come on guys, please help.
This is the most important query and will be the most commonly used command on my site, so it has to be as optimized as it can be. Im hoping i can write the command in such a way that the execution plan will be cached. I really dont want to use a text command if i can help it because ive managed to place every other command into a stored procedure so it would be a shame if the main command wasnt.

Kristian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-16 : 10:53:26

quote:
Come on guys, please help.

Kristian-

Just so you know, it is the weekend, and most of us (besides me)

are doing other things, and generally taking a break from SQL Server. It is strictly voluntary and we're not beholden to you to solve your problem. It doesn't help to be impatient, because you promote the feeling that we owe you something just because you posted a question:

quote:
This is the most important query and will be the most commonly used command on my site, so it has to be as optimized as it can be

That's great, I'm happy for ya. Amazingly there are only about 40 million other programmers who also have to create search pages for web sites, and somehow many of them also think we've got nothing better to do that tweak their queries for them, without providing enough information for us to help.

You also have NOT described what you want the results to look like or what your table structures look like. To you, listing Title, URL, Description and Country might be all the info you need, but none of us are mind readers. A more detailed description of the overall process would be helpful too.

BTW, you HAVE a working query, do you not? You didn't say if it performs too slowly, so I don't see any reason for us to fix something that ain't broke. And you already have this written as a stored procedure, I don't know where you got the idea that you have to use a "text command" or whatever that means instead.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-06-16 : 12:19:33

quote:
Thing is it dont seem to work, when i replace the inputs with variables and paste the code into query analizer, it rewrites my code to look like mess

Are saying that Query Analyzer rewrites your code? Are you sure you aren't using Enterprise Manager? What type of field is Cat1? What are you storing in it? I'm assuming all the other Cat and SubCat fields are the same. How about the Gen and SubGen? Maybe you could post your table layouts? And agreeing with Rob, what is a text command? Did you read the comments on the article? If so, you noticed that COALESCE will not use indexes on the columns it's checking. Very slow.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

kristian
Starting Member

21 Posts

Posted - 2002-06-16 : 18:19:55

Hey, thanks for the help robvolk

Im not asking you to rewrite my query, i was just asking if that big old mess above looks inefficient. Surely a one sentance answer saying yes of no would of been a nicer and quiker for you to write? Or can you just not get you head round the idea without me writing create table scripts and detailing the output. If you cant depict what Title, URL, Description and Country are gonna look like then you should give up programming now, your gonna find it to complicated!

quote:
"text command" or whatever that means

So you know for future this is what .net calls it when you pass an SQL command as a string rather than calling a stored procedure.

graz. Thanks. Your last 2 words answered my question. And your right i did mean enterprise manager and not query analizer.

Kristian

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-16 : 19:14:07

kristian you're a very brave man

. One little tip though, that number under robs name means that he's helped over 4000 people more than you on this forum

The more detail in a post the greater the chance of a reply and the greater the chance it will be what you are looking for.

It is common netiquette to provide DDL and sample data so that people are able help you . People help out of the goodness of their own heart, no reward except the satisfaction of (hopefully

)having helped somone out. There's no one out there who doesn't need a little help or a prod in the right direction every so often. What goes around comes around, people are more likely to help you if you help them help you. Having to create sample tables and data is time consuming for those trying to help when the poster has the ability to very quickly generate the scripts.Posting the version of SQL is also important as it saves someone spending 10 minutes coding a UDF only to find the poster is on version 7 or 6.5

quote:
If you cant depict what Title, URL, Description and Country are gonna look like then you should give up programming now, your gonna find it to complicated!

Yeah Rob - might as well call it a day

kristian
Starting Member

21 Posts

Posted - 2002-06-16 : 20:06:06

Yeah
I noticed he was moderator.
All i asked was a nod in the right direction regarding the above code being as inefficient as i though it was, and he came back both unhelpfull and sounding very arrogent.
Perhaps the roles gone to his head.

Remember

Kristian

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-06-16 : 20:35:44

No actually Rob was telling you how it is.
You came back sounding arrogant. Perhaps when you have helped as many people with the jobs that THEY GET PAID FOR as Rob you might get it.

Damian

Nazim
A custom title

1408 Posts

Posted - 2002-06-17 : 00:39:51

Kristian !!!!! what are you upto . what do u think Rob should toss a coin and put head a yes and tail a no and which ever comes that should be his answer . what he was trying to xplain was you should provide enough information so that we would be able to answer it.

i had myself read your post yesterday and couldnt come up with anything. i assumed(curse me , curse me!!!!) that when you have read the article , you might have already read the comments. but Royal you ,why will you read it . we are there for you to help and how should we help, we should dream , we should guess And if we are not able to do that we are no programmers or DBA or whatever the heck.

Cool down , change your attitude this kinda attitude wouldnt take you anywhere.

quote:

All i asked was a nod in the right direction regarding the above code being as inefficient as i though it was, and he came back both unhelpfull and sounding very arrogent.
Perhaps the roles gone to his head

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Doug G
Constraint Violating Yak Guru

331 Posts

Posted - 2002-06-17 : 00:51:59

I have a theory.

If you're not going to help someone out in a support forum, probably the best course is to say nothing.

======
Doug G
======

Nazim
A custom title

1408 Posts

Posted - 2002-06-17 : 00:57:37

Dough i appreciate it. but then ,who will bell the cat

quote:

If you're not going to help someone out in a support forum, probably the best course is to say nothing.

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-17 : 02:28:15

kristian,

If this is the most important query in your app then a couple of suggestion...

1) Because Input3 and Input4 are based on 1 field each, I would branch those out into an IF statement.. It will give the optimiser a better head start...

2) Why not put some of this logic in the front-end and make a few more stored procs?
eg. If Input2 is null then call this proc etc...

Speed is the most important factor for you here, right?

DavidM

"SQL-3 is an abomination.."

kristian
Starting Member

21 Posts

Posted - 2002-06-17 : 06:34:04

Thanks byrmol

Ill was considering using more than one query, depending on the search criteria received. I couldnt possbly do all combinations but perhaps ill call a second query depending on input 3 n 4 being null.
Or perhaps ill do 5 querys, 1 for each input, then 1 for when all 4 inputs are supplied, that ought to speed things up.

I started going down the IF statement route and building up an SQL string to execute but from reading in the above tutorial it seems SQL cannot cache the execution plan if you do it that way so best to avoid if you can. Although im starting to wonder how an ugly query with loads of coalesce can be more efficient than a direct call with the required fields.

I gonna do some testing on the different options and see what works best.

Anyway big thanks for not behaving like youve got your head up your own arse and just answering my question without all the fuss. If you ever need any ASP or .net info just email me and ill be happy to help out.

Kristian

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-17 : 09:26:49

Speaking of having your head up your ass, you should take your own advise . . . what indexes have you created on Links? That's where you need to start tuning your query. I would only start going down rewriting paths if I was sure I couldn't get more performance out of the query I had.

<O>

Nazim
A custom title

1408 Posts

Posted - 2002-06-17 : 09:35:25

kristian as you have already discovered when you use coalesce index would work.

i have a feeling therez something wrong with your table design. but then i could be wrong.....

can you post your table schema and its use.

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

MakeYourDaddyProud

184 Posts

Posted - 2002-06-17 : 09:59:10

It's nice to see someone cut the cr@p and just answer the question the best they see fit. I *don't* think members are after bullet proofed - all singin' and dancin' solutions, since it is often difficult to give 100% correct answers to varying scenarios. What I don't understand is how some members could have found the time to post over 4000 replies? What's that all about? I mean, do these guys work???? If they do, then I feel very sorry for their employers. And what about their families? Can they remember thier wifes names?

Anyway, I would be suspicious of anyone making such large volume of posts. It must be obsessive for the "i know just about everything" guru mentality that beseiges a sad few and who probably only answer queries anway - since they know everything.

Why can't people just utilise the time more constructively. Perhaps thats the reason why IT jobs are fast dissappearing around the globe and justified management boards have a vote of no confidence in thier IT strategies...

Remember KISS: Keep It Simple Stupid Principle, it works wonders for me.

;-)

Dan
www.danielsmall.com

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-17 : 10:09:45

Gee Dan, why don't we try to keep this on topic, mkay? Do you have anything to say to add value to this thread? Do you have any suggestions for the original poster? Cutting Rob is gonna do nothing for kristian, and is gonna do nothing for you.

<O>

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-06-17 : 10:16:08

Dan

Who the F^@K do you think you are making judgements like that ?

Do you know Rob ? No you don't.
Do you know me ? No you don't.

quote:

I don't understand is how some members could have found the time to post over 4000 replies? What's that all about? I mean, do these guys work???? If they do, then I feel very sorry for their employers. And what about their families? Can they remember thier wifes names?

Anyway, I would be suspicious of anyone making such large volume of posts. It must be obsessive for the "i know just about everything" guru mentality that beseiges a sad few and who probably only answer queries anway - since they know everything.

For what it's worth, i was probably about the first "non-staff" poster on this site. I came here just after it was launched and started to post as a way to learn more.

As a result I learned a whole bunch in helping people find answers to their problems, after that I started to write some articles for the site as well.

My employer loved me spending time on this site, it doesn't take up much time each day and it did wonders for their "training budget". Our products and services benefited greatly as a result (both directly and indirectly) from information gained by participating in this forum.

Along the way I have helped hundreds of people solve their problems and constantly get emails from people thanking me for articles and posts.

I don't want to speak for Rob, but I think you will find his story is pretty much the same.

I don't pretend to know everything, far from it. But I am always prepared to jump in and help someone, provided they put a little effort in themselves. It's one way of giving a little bit back to the programming community, for the greater good. It is certainly more honourable than pushing Multi Level Marketing scams designed to help your friends and family lose their money so you can get a bigger bonus.

quote:

And what about their families?

What about your family when you rip them off ?

quote:

I *don't* think members are after bullet proofed - all singin' and dancin' solutions, since it is often difficult to give 100% correct answers to varying scenarios.

Unfortunately you are very wrong. We do get posters who do want exact solutions while providing very little background information. These posters tend to be the most rude and pushy as they feel they are owed something.

Forgive us if we get frustrated at this. We have helped build this site from very early on and don't like people being rude here.

quote:

Remember KISS: Keep It Simple Stupid Principle, it works wonders for me.

Then go apply that principle to that bloody awful excuse for a website you are peddling. I'm sorry, but DHTML cursor following scripts are SO Q4 '99.

Now unless you have anything useful to add, go back and play under your bridge, Troll.

Damian

Edited by - merkin on 06/17/2002 10:37:24

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-06-17 : 10:45:37

Hehe just have to agree with Merkin about that DHTML script... Looks like a cheap porn page to me (don't ask me how I know

)

MakeYourDaddyProud

184 Posts

Posted - 2002-06-17 : 11:36:11

DHTML Script? Absolutely! But then I forgot that you guys can always do it that much better. Not Just SQL, but Web, VB, DHTML (of course!), Active-X, SOAP, BizTalk and Gates++. Is there any end to your revered knowledge sir? This is the problem with programmers I have worked with these days, always know everything, can't accept criticism but have minimal substance and personality as usual.

Yes I am involved with an "MLM" system. More than one. Nice to see you have taken great pains to publish one for me as a "scam". I do this because I hate being exploited by bosses who are making a fortune. There is nothing wrong with that. I am not forcing you to join my club. It's quite sad to see you deconstructing my comments in that manner when you do not know anything about the scheme. Perhaps you should be a lawyer....

I am mainly a computer reseller and rentor now. I subcontract others to do my IT work now. I used to do a lot of freelance SQL on both unix and windows platforms but I have now seen the "light". I now prefer golf matches, fast cars and time off - these seem more interesting than glaring at a desktop 1/2 of the working week, posting 1900+ SQL queries and curse members all day long.

Remember the The "MLM" turnkey providers would not appreciate you branding their system in that manner so can you all refrain from unprovoked comments.

Thankyou for your treatment of me, and hope you feel justified on what you have done, my comments weren't designed to cause harm.

Daniel Small MIAP

www.danielsmall.com - In case you forgot.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources