cancel
Showing results for 
Search instead for 
Did you mean: 

How to replace EQUAL TO by LIKE in a Query involving variables

leon_laikan
Participant
0 Kudos
47

Dear All,

I am working on a Query which is in reality 3 Queries in 1. It works perfectly.

When I run the Query, it gives the following Query - Selection Criteria


Query - Selection Criteria
-------------------------------------------------------
Import Log                        ...................
Block Number                      ...................

Document Date                     ...................
Document Date                     ...................


[OK]   [Cancel]

To use this Query, we must choose only 1 criterion and leave other fields blank. To achieve this, I

use OR inside WHERE

1ST Query

If you type S713 in the first field, it returns all records with Import Log = S713

2 ND Query

If you type G1575 in the 2ND field, it returns all records with Block Number = G1575

3rd Query

If you type '01.07.11' and '05.07.11' in the 3RD and 4TH fieldS, it returns all records with

Dates between 01.07.00 and 05.07.11

The Query won't work if you fill in all fields.

WHAT I WANT?

I want to replace the EQUAL TO in the WHERE clause by LIKE to allow the Query to search for a pattern, instead

of rendering an exact match.

I know this can be done with = '[%1]' by changing it to LIKE '%[%1]%'

But how can you do the same with @shipnum and @reqnum?

I can change the variable declarations to

set @shipnum = /* */ '%[%1]%'

but it creates problems.

How can we use LIKE in the WHERE clause, without modifying the declarations.

Thanks

Leon Lai

.

.

.

Here's my Query



/*
TABLES:
T0 = OPCH A/P Invoice - Header
T1 = PCH1 A/P Invoice - Rows
T5 = OJDT Journal Entry
*/

declare @shipnum nvarchar (30)
set @shipnum =
/*select T1.[ImportLog] from [dbo].[PCH1] T1 where T1.[ImportLog]*/ '[%1]'

declare @reqnum nvarchar (30)
set @reqnum =
/*select T1.[BlockNum] from [dbo].[PCH1] T1 where T1.[BlockNum]*/ '[%2]'

declare @taxdt1 datetime
set @taxdt1=
/*select T0.[TaxDate] from [dbo].[OPCH] T0 where T0.[TaxDate]*/ '[%3]'

declare @taxdt2 datetime
set @taxdt2=
/*select T0.[TaxDate] from [dbo].[OPCH] T0 where T0.[TaxDate]*/ '[%4]'




SELECT
T0.[TaxDate] AS 'Doc Dt',
T1.[ImportLog] AS 'Ship #',
T1.[BlockNum] AS 'Reqn #',
T0.[CardName] AS 'Supplier Name',
T0.[DocTotal] AS 'Rs'

FROM [dbo].[OPCH] T0
INNER JOIN [dbo].[PCH1] T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [dbo].[OJDT] T5 ON T0.[TransID] = T5.[TransID]

WHERE

(T1.[ImportLog]  = @shipnum  AND 
@reqnum = '  ' AND
@taxdt1  = '  ' AND
@taxdt2  = '  ' )

OR

(T1.[BlockNum] = @reqnum  AND 
@shipnum = '  ' AND
@taxdt1  = '  ' AND
@taxdt2  = '  ' )

OR

(T0.[TaxDate] >= @taxdt1 AND
T0.[TaxDate] <= @taxdt2 AND
@shipnum = ' ' AND
@reqnum = ' ' )

Accepted Solutions (1)

Accepted Solutions (1)

leon_laikan
Participant
0 Kudos

Dear Gordon,

Thanks for your reply.

Unfortunately it does not work when I use

T1.[ImportLog] LIKE '%@shipnum%'

I think I could guess the reason:

Since @shipnum means '% { %1 } %'

/* I use { } to represent Square Brackets */

Then

LIKE '%@shipnum%' would mean

LIKE '% '%{%1}%' '

and this would be incorrect syntax.

If I remove the ' it still does not work.

By numerous trial and error, I just arrived at this mixture of @variable and '{%x}' which works!

I have no idea why, and it appears rather messy.But it works seamlessly.

I tried using uniquely {%x} without the @variables. It does not work.

So, if you could analyse it and put it in neater form (I mean without a mixture of @ and { },

I would be grateful.

There are so many tricks I used to make my Queries work, without understanding

at all why they work.

Best Regards,

Leon Lai

.

.

.


/*
TABLES:
T0 = OPCH A/P Invoice - Header
T1 = PCH1 A/P Invoice - Rows
T5 = OJDT Journal Entry
*/

declare @shipnum nvarchar (30)
set @shipnum =
/*select T1.[ImportLog] from [dbo].[PCH1] T1 where T1.[ImportLog]*/ '%[%1]%'



declare @reqnum nvarchar (30)
set @reqnum =
/*select T1.[BlockNum] from [dbo].[PCH1] T1 where T1.[BlockNum]*/ '%[%2]%'


declare @taxdt1 datetime
set @taxdt1=
/*select T0.[TaxDate] from [dbo].[OPCH] T0 where T0.[TaxDate]*/ '[%3]'


declare @taxdt2 datetime
set @taxdt2=
/*select T0.[TaxDate] from [dbo].[OPCH] T0 where T0.[TaxDate]*/ '[%4]'




SELECT
T0.[TaxDate] AS 'Doc Dt',
T1.[ImportLog] AS 'Ship #',
T1.[BlockNum] AS 'Reqn #',
T0.[CardName] AS 'Supplier Name',
T0.[DocTotal] AS 'Rs'

FROM [dbo].[OPCH] T0
INNER JOIN [dbo].[PCH1] T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [dbo].[OJDT] T5 ON T0.[TransID] = T5.[TransID]

WHERE

(T1.[ImportLog]  like @shipnum  AND 
'[%2]'  = '  '  AND
@taxdt1  = '  ' AND
@taxdt2  = '  ' )

OR

(T1.[BlockNum] like @reqnum  AND 
'[%1]' = '  ' AND
@taxdt1  = '  ' AND
@taxdt2  = '  ' )

OR

(T0.[TaxDate] >= @taxdt1 AND
T0.[TaxDate] <= @taxdt2 AND
'[%1]' = ' ' AND
'[%2]'= ' ' )

former_member204969
Active Contributor
0 Kudos

Try to use the like expression (with the original @shipnum declaration) somehow like this:

T1.[ImportLog]  like '%'+@shipnum+'%'

leon_laikan
Participant
0 Kudos

Dear István Korös ,

Thanks a lot for your suggestion.

It works!

I had already closed my thread because I had arrived at a solution, which

although rather messy, worked perfectly.

Your suggestion not only works perfectly, but is very tidy, involving only @variables without

variables within square brackets and %.

Never thought concatenation could achieve such magic!

Best Regards

Leon Lai

.

.

.

Here's my WHERE clause incorporating your suggestions:

-

-



WHERE

(T1.[ImportLog]  like '%' + @shipnum +'%' AND 
@reqnum  = '  '  AND
@taxdt1  = '  ' AND
@taxdt2  = '  ' )

OR

(T1.[BlockNum] like '%' + @reqnum + '%' AND 
@shipnum = '  ' AND
@taxdt1  = '  ' AND
@taxdt2  = '  ' )

OR

(T0.[TaxDate] >= @taxdt1 AND
T0.[TaxDate] <= @taxdt2 AND
@shipnum = ' ' AND
@reqnum  = ' ' )

Answers (1)

Answers (1)

Former Member
0 Kudos

Dear Leon,

Have you tried something like:

WHERE
 
(T1.[ImportLog]  LIKE '%@shipnum%'  AND 
@reqnum = '  ' AND
@taxdt1  = '  ' AND
@taxdt2  = '  ' )

Thanks,

Gordon