Using LIKE with 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

Transact-SQL (2000)

Using LIKE with COALESCE

Author

Topic

kingbc
Starting Member

3 Posts

Posted - 2003-05-30 : 11:44:49

I need a little assistance with a dynamic proc here. I would like to do the following but cannot get it to work.

SELECT cr.crid
WHERE tr.trTransactionNumber = COALESCE(@TrxId,tr.trTransactionNumber)
AND cr.crLastName LIKE COALESCE(@crLastName,cr.crLastName)

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 11:47:52

Perhaps:

SELECT cr.crid
-- How about a missing FROM clause?
FROM myTable
WHERE tr.trTransactionNumber = COALESCE(@TrxId,tr.trTransactionNumber)
-- And a Wildcard for the like?
AND cr.crLastName LIKE COALESCE(@crLastName,cr.crLastName)+'%'

What specifically is not working btw?

Brett

8-)

kingbc
Starting Member

3 Posts

Posted - 2003-05-30 : 11:57:12

Sorry, here is the entire statement from the proc.

What is not working is the last name LIKE portion of the query. I had not tried adding the + '%' at the end. i will try that now.

Thanks.

--------------------------------------------------------

SELECT DISTINCT tr.trTransactionNumber AS TrxId,
CASE tr.trTransactionTypeID
WHEN 'Z4' THEN 0
WHEN '90' THEN ca.cPersonnelID
ELSE cr.crPersonnelID
END
AS GlobalID,
CASE tr.trTransactionTypeID
WHEN 'Z4' THEN ca.cLastName + ', ' + ca.cFirstName
WHEN '90' THEN ca.cLastName + ', ' + ca.cFirstName
ELSE cr.crLastName + ', ' + cr.crFirstName
END
AS Contractor,
tr.trTransactionTypeID AS TrxType,
tr.trFailureReason AS ErrorDescr
FROM tblTransactions tr
LEFT OUTER JOIN vw_AllPoData po ON tr.AssignmentId = po.AssignmentID
LEFT OUTER JOIN vw_AllPoData tpo ON tr.AssignmentId = tpo.taTempPOAssignmentId
LEFT OUTER JOIN tblContractor cr ON po.ContractorID = cr.ContractorID
LEFT OUTER JOIN tblCandidate ca ON tpo.CandidateId = ca.CandidateID
WHERE tr.trReceiptStatusID = 0
AND tr.trTransactionNumber = COALESCE(@TrxId,tr.trTransactionNumber)
AND cr.crPersonnelID = COALESCE(@GlobalID,cr.crPersonnelId)
AND cr.crLastName LIKE COALESCE(@crLastName,cr.crLastName)
AND tr.trTransactionTypeID = COALESCE(@TrxType,tr.trTransactionTypeID)
ORDER BY tr.trTransactionNumber DESC

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 12:00:43

Give it a shot and let us know...

Brett

8-)

kingbc
Starting Member

3 Posts

Posted - 2003-05-30 : 12:13:19

Ok, it now looks like this:
-----------------------------------
SELECT DISTINCT tr.trTransactionNumber AS TrxId,
CASE tr.trTransactionTypeID
WHEN 'Z4' THEN 0
WHEN '90' THEN ca.cPersonnelID
ELSE cr.crPersonnelID
END
AS GlobalID,
CASE tr.trTransactionTypeID
WHEN 'Z4' THEN ca.cLastName + ', ' + ca.cFirstName
WHEN '90' THEN ca.cLastName + ', ' + ca.cFirstName
ELSE cr.crLastName + ', ' + cr.crFirstName
END
AS Contractor,
tr.trTransactionTypeID AS TrxType,
tr.trFailureReason AS ErrorDescr
FROM tblTransactions tr
LEFT OUTER JOIN vw_AllPoData po ON tr.AssignmentId = po.AssignmentID
LEFT OUTER JOIN vw_AllPoData tpo ON tr.AssignmentId = tpo.taTempPOAssignmentId
LEFT OUTER JOIN tblContractor cr ON po.ContractorID = cr.ContractorID
LEFT OUTER JOIN tblCandidate ca ON tpo.CandidateId = ca.CandidateID
WHERE tr.trReceiptStatusID = 0
AND tr.trTransactionNumber = COALESCE(@TrxId,tr.trTransactionNumber)
AND cr.crPersonnelID = COALESCE(@GlobalID,cr.crPersonnelId)
AND cr.crLastName LIKE COALESCE(@crLastName,cr.crLastName) + '%'
AND ca.cLastName LIKE COALESCE(@crLastName,ca.cLastName) + '%'
AND tr.trTransactionTypeID = COALESCE(@TrxType,tr.trTransactionTypeID)
ORDER BY tr.trTransactionNumber DESC

(there is a plus in front of the % on both lines, the forum is stipping those out)

--------------------------------------------

Still not working. It will pick up some records, but not all that it should. Example, enter just H, should pick up 40 records, returns only 1.

?

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 13:54:45

I must be misunderstanding COALESCE..Try this though:

USE NorthWind
GO
DECLARE @x varchar(10)
SELECT @x = 'H'
SELECT * FROM Orders WHERE CustomerId LIKE ISNULL(@x,CustomerId)+'%'
SELECT * FROM Orders WHERE CustomerId LIKE 'H%'
SELECT * FROM Orders WHERE CustomerId LIKE COALESCE(@x,CustomerId)+'%'

ISNULL Works

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-31 : 03:53:00

quote:
(there is a plus in front of the % on both lines, the forum is stipping those out)

It's some sort of bug in the Preview...they show up just fine in the thread.

quote:

Still not working. It will pick up some records, but not all that it should. Example, enter just H, should pick up 40 records, returns only 1.

I hope you realise that this "AND cr.crLastName LIKE COALESCE(@crLastName,cr.crLastName) + '%' " will return only records where the last name begins with @crLastName. If you want to able to match any portion of crLastName you need to add the '%' to the beginning of the expression as well (note: not really recommended, it can't use any indexes)

SELECT * FROM Customers WHERE CompanyName LIKE '%' + ISNULL(@CustomerID,CustomerId) + '%'

Owais

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-31 : 06:23:53

FYI --

replace:

AND cr.crLastName LIKE COALESCE(@crLastName,cr.crLastName) + '%'
AND ca.cLastName LIKE COALESCE(@crLastName,ca.cLastName) + '%'

with

AND cr.crLastName LIKE COALESCE(@crLastName + '%','%')
AND ca.cLastName LIKE COALESCE(@crLastName + '%','%')

should be a little more efficient .... now the right-hand part of the criteria is a constant and doesn't have to be evaluated for all rows in the table.

It is also logically more sound to replace

WHERE Field1 = COALESCE(@FilterField1,Field1)

with

WHERE ((@FilterField1 Is null) or (Field1 = @FilterField1))

Also, if Field1 is Null the entire row will NEVER be returned, even if you leave the @filterField1 argument Null, if the code it the first way.

That may be part of the problem you are having with not enough records being returned.

- Jeff

Edited by - jsmith8858 on 05/31/2003 08:52:55

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-06-02 : 09:02:58

if crlastname has datatype char()
watch out for.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=22265

Voted best SQL forum nickname...."Tutorial-D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources