Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

Passing a parameter with Like

Posted on 2012-12-21

1 solution

Medium Priority

426 Views

Last Modified: 2012-12-21

I have a query that displays voided transactions, and I am passing a parameter value to the WHERE clause with like but it is not returning any rows, I know I am missing something but I can't seem to figure out what.

declare @ITEM char(1000)
set @ITEM = 'ATIGM9130'
SELECT 
[Company]='NAME',
[Source]=cast('History' as varchar(7)),
[Doc_Type]=CASE SOP30200.SOPTYPE when 1 then 'QUOTE' when 2 then 'ORDER' when 3 then 'INVOICE' when 4 then 'RETURN' when 5 then 'BACKORDER' else '' end,
[Doc_Number]=SOP30200.SOPNUMBE,
SOP30300.ITEMNMBR,
SOP30300.ITEMDESC,
SOP30300.QUANTITY,
SOP30200.ORIGNUMB,
[Doc_Date]=SOP30200.DOCDATE,
[GL_Posting_Date]=SOP30200.GLPOSTDT, 
[Invoice_Date]=SOP30200.INVODATE,
SOP30200.FUFILDAT,
SOP30200.ACTLSHIP,
SOP30200.LOCNCODE,
SOP30200.BCHSOURC,
[Batch_No]=SOP30200.BACHNUMB,
SOP30200.CUSTNMBR, 
SOP30200.CUSTNAME,
SOP30200.ADDRESS1,
SOP30200.ADDRESS2,
SOP30200.CITY,
SOP30200.[STATE],
SOP30200.CNTCPRSN,
SOP30200.PHNUMBR1, 
SOP30200.PHNUMBR2,
SOP30200.SHIPMTHD,
[Posting_Status]=CASE SOP30200.PSTGSTUS when 0 then 'NOT POSTED' when 2 then 'POSTED' else 'UNKNOWN' end,
[Void_Status]=CASE SOP30200.VOIDSTTS when 1 then 'VOIDED' else '' end,
--[Message] = CASE SOP30200.VOIDSTTS when 1 then 'Voided By will be the Batch_No for the transaction.' else '' end,
[Voided By]= CASE SOP30200.VOIDSTTS when 0 then '' else SOP30200.BACHNUMB end,
IV00101.ITMCLSCD
FROM         SOP30200 WITH(nolock) INNER JOIN
                      SOP30300 WITH(nolock)  ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE INNER JOIN
                      IV00101 WITH(nolock)  ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR
WHERE     (IV00101.ITMTRKOP = 2) AND (NOT (SOP30200.SOPTYPE = 1)) AND (SOP30300.ITEMNMBR  LIKE '%'+@ITEM+'%' )--AND SOP30300.ITEMNMBR in (select Value from dbo.parmstolist(@ITEM, ','))
ORDER BY 8 DESC

Open in new window

Comment

Question by:skull52

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

8 Comments

LVL 11

Expert Comment

by:David Kroll

ID: 387133822012-12-21

If you take out the like condition, do results that match that item get returned?

LVL 12

Expert Comment

by:Habib Pourfard

ID: 387133972012-12-21

the query syntax is correct. may be there is something wrong with the logic.

WHERE   ( IV00101.ITMTRKOP = 2 )
        AND ( SOP30200.SOPTYPE <> 1 )
        AND ( SOP30300.ITEMNMBR LIKE '%' + @ITEM + '%' )

Open in new window

for example:
- may be you need to replace AND with OR.
- make sure ITEMNMBR is varchar...

LVL 71

Expert Comment

by:Qlemo

ID: 387134002012-12-21

That (commented) subquery of select Value from dbo.parmstolist(@ITEM, ',') points to that @item contains more than one value?

Author Comment

by:skull52

ID: 387134512012-12-21

If I just use the @ITEM param without the LIKE and enter the full item number value it works just fine. the (select Value from dbo.parmstolist(@ITEM, ',') is a stored proc that allows me to pass several values separated by a comma, which also works if I enter multiple complete Item numbers which I don't want do do. Adding an OR does not filter on the parameter value.

LVL 27

Expert Comment

by:Zberteoc

ID: 387134682012-12-21

The cause could be multiple and not necessarily the LIKE clause condition. It is possible that the INNER JOIN conditions filter out the results or maybe in a different WHERE clause condition.

My advice is to run a query only with count to see which condition is actually causing the filtering out of the rows:

declare @ITEM char(1000)
set @ITEM = 'ATIGM9130'

SELECT 
	count(*) cnt
FROM         
	SOP30200 WITH(nolock) 
	
	INNER JOIN SOP30300 WITH(nolock) ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE 
	
	INNER JOIN IV00101 WITH(nolock)  ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR

WHERE    
	(IV00101.ITMTRKOP = 2) 
	
	AND (NOT (SOP30200.SOPTYPE = 1)) 
	
	AND (SOP30300.ITEMNMBR  LIKE '%'+@ITEM+'%' )

Open in new window

Start from bottom up and comment one condition at a time and then one join at a time and execute the query after each commenting action. Stop when you will get any count > 0

If you will get rows aftec commenting the LIKE condition it means that there are no ITEMNUMBERs with that format: %ATIGM9130%

LVL 12

Accepted Solution

by:Habib Pourfard

Habib Pourfard earned 2000 total points

ID: 387134922012-12-21

instead of declaring @item as char(1000) declare it as varchar(1000).

declare @ITEM varchar(1000)
set @ITEM = 'ATIGM9130'

Open in new window

LVL 27

Expert Comment

by:Zberteoc

ID: 387135282012-12-21

Correct, pourfard is right. Declaring the variable as CHAR the comparition value will actually be:

%ATIGM9130 %

where the trailing spaces will count hence the lack of results.

Author Comment

by:skull52

ID: 387135602012-12-21

Yep it was the varchar, I normally don't use char, I copied that declare statement from another query I did a while ago which was a char, missed that one, thanks pourfard and thanks to all who responded. "Happy Holidays"

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to implement Parallel Executions in SSIS?

Article by: Dung

I have a large data set and a SSIS package. How can I load this file in multi threading?

Export Table Data in SQL Server Management Studio

Article by: Dirk

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.

Creating and Executing a SQL Pass-thru Query

Video by: TechMommy

Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…