Error on case query - 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

General SQL Server Forums

New to SQL Server Programming

Error on case query

Author

Topic

wussupbuddy
Starting Member

16 Posts

Posted - 2009-04-28 : 05:51:58

I'm getting an error while trying to execute the following statement. What's wrong with it?

"Select * from master where case(isnull(firstname) when 1 then '' else firstname end +' '+ case(isnull(lastname) when 1 then '' else lastname end like '%" & Text1.Text & "%'"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 05:56:27

[code]
Select *
from master
where isnull(firstname, '') + ' ' + isnull(lastname, '') like '%' + Text1.Text + '%'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

wussupbuddy
Starting Member

16 Posts

Posted - 2009-04-28 : 06:04:45

Doesn't work! Got the following error

Incorrect parameter count in the call to native function 'isnull'.

I'm using My SQl ODBC 5.1 driver

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 06:06:48

MySQL?
This is a Microsoft SQL Server forum.

E 12°55'05.63"
N 56°04'39.26"

wussupbuddy
Starting Member

16 Posts

Posted - 2009-04-28 : 06:10:14

I know and if its totally unethical then I'm sorry to be in the wrong place. I thought all MS SQL quries was supposed to wotk in My SQL also. Also MySQL forums doesn't have these many hits...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 07:02:55

Try www.dbforums.com

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 07:12:58

Try

Select *
from master
where coalesce(firstname, '') + ' ' + coalesce(lastname, '') like '%' + Text1.Text + '%'

Madhivanan

Failing to plan is Planning to fail

wussupbuddy
Starting Member

16 Posts

Posted - 2009-04-28 : 07:26:31

Thanks madhivanan..this is what I tried but I'm not getting records back even though there are records that partially match the keyowrd entered.

strSQL = "Select * From master where COALESCE(firstname,'')+' '+COALESCE(lastname,'') like '%" & Text1.Text & "%'"

wussupbuddy
Starting Member

16 Posts

Posted - 2009-04-28 : 07:38:39

It seems like COALESCE doesn't like strings. I tried the following and got back .00000....

Select COALESCE(firstname,'')+' '+COALESCE(lastname,'') From master where empid=710809508

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-28 : 07:52:21

Use the IFNULL function.

SELECT * FROM [master] WHERE IFNULL(firstname, '') + IFNULL(lastname, '') LIKE '%" & Text1.Text & "%'";

If that doesn't work, try this:
SELECT *, IFNULL(firstname, '') + IFNULL(lastname, '') as filterCol FROM [master] WHERE filterCol LIKE '%" & Text1.Text & "%'";

Also, use command parameters.

wussupbuddy
Starting Member

16 Posts

Posted - 2009-04-28 : 08:08:36

that didn't work either..seems like using the '+' symbol automatically converts the data to numerical. I tried the following and it worked..

strSQL = "SELECT * FROM master WHERE concat(IFNULL(firstname, ''), ' ',IFNULL(lastname, '')) LIKE '%" & Text1.Text & "%'"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 08:34:39

quote:
Originally posted by wussupbuddy

that didn't work either..seems like using the '+' symbol automatically converts the data to numerical. I tried the following and it worked..

strSQL = "SELECT * FROM master WHERE concat(IFNULL(firstname, ''), ' ',IFNULL(lastname, '')) LIKE '%" & Text1.Text & "%'"

Now it would work with COALESCE too

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources