DB2 Query Using Where In with Wildcard

SDyke · #1 (**permalink**) 06-16-10, 08:06

I need help getting the following concept to work. I need the SQL to return all A% possibilities.

Select recseq From myFile Where partno In('851545-401',851545-401A%')

ARWinner · #2 (**permalink**) 06-16-10, 08:39

What do you mean? Also please state your DB2 version and OS.

Andy

SDyke · #3 (**permalink**) 06-16-10, 08:58

Quote:

Originally Posted by ARWinner

What do you mean? Also please state your DB2 version and OS.

Andy

DB2 ver 8.0
OS iSeries V5r3

Suppose I have records 851545-4, 851545-4E, 851545-403, 851545-403A, 851545-403AG these have a datetime stamp field.

I want to know the latest record with part number 851545-403 regardless of suffix. Using the LIKE and % wildcard will work until I want to look up part number 851545-4 which would return all the 851545-403's.

So my thought was to use this:

Select partnumber From myFile Where partno in('851545-403','851545-403A%')

But this will not work as I thought because it only will return 851545-403.

r937 · #4 (**permalink**) 06-16-10, 09:09

WHERE LEFT(partno,10) = '851545-403'

SDyke · #5 (**permalink**) 06-16-10, 09:17

Quote:

Originally Posted by r937

WHERE LEFT(partno,10) = '851545-403'

This would not work if my part number is 851545-4 as it would return all the -403's with it.

tonkuma · #6 (**permalink**) 06-16-10, 09:40

Quote:

Suppose I have records 851545-4, 851545-4E, 851545-403, 851545-403A, 851545-403AG these have a datetime stamp field.

I want to know the latest record with part number 851545-403 regardless of suffix. Using the LIKE and % wildcard will work until I want to look up part number 851545-4 which would return all the 851545-403's.

How about this?

Code:

SELECT partno
  FROM myFile
 WHERE TRANSLATE(partno , '' , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
       = '851545-403'
;

n_i · #7 (**permalink**) 06-16-10, 10:03

Quote:

Originally Posted by SDyke

So my thought was to use this:

Select partnumber From myFile Where partno in('851545-403','851545-403A%')

But this will not work as I thought because it only will return 851545-403.

Have you heard about the logical operator "OR"? It might come useful here.

dav1mo · #8 (**permalink**) 06-16-10, 10:04

or go the easy route and say:

Code:

Select partnumber From myFile Where partno = '851545-403'
union
Select partnumber From myFile Where partno like '851545-403A%'

Dave

tonkuma · #9 (**permalink**) 06-16-10, 10:18

Quote:

Select partnumber From myFile Where partno in('851545-403','851545-403A%')

Even if used like:

Code:

SELECT partno
  FROM myFile
 WHERE partno = '851545-403'
   OR  partno LIKE '851545-403A%'

How do you know the suffix 'A'?

If partno is 851545-4, you may want to use like:

Code:

SELECT partno
  FROM myFile
 WHERE partno = '851545-4'
   OR  partno LIKE '851545-4E%'

Lenny77 · #10 (**permalink**) 06-16-10, 10:36

Quote:

Originally Posted by tonkuma

Even if used like:

Code:

SELECT partno
  FROM myFile
 WHERE partno = '851545-403'
   OR  partno LIKE '851545-403A%'

How do you know the suffix 'A'?

If partno is 851545-4, you may want to use like:

Code:

SELECT partno
  FROM myFile
 WHERE partno = '851545-4'
   OR  partno LIKE '851545-4E%'

This solution is good, but not a universal.
Lenny

SDyke · #11 (**permalink**) 06-16-10, 11:47

Quote:

Originally Posted by n_i

Have you heard about the logical operator "OR"? It might come useful here.

Where or how do you propose I use the OR operator. I have tried seveal different combiations but now work.

SDyke · #12 (**permalink**) 06-16-10, 13:09

Quote:

Originally Posted by tonkuma

How about this?

Code:

SELECT partno
  FROM myFile
 WHERE TRANSLATE(partno , '' , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
       = '851545-403'
;

This produces the result I need but it is super slow. Can anything be done to speed it up?

Lenny77 · #13 (**permalink**) 06-16-10, 14:12

Quote:

Originally Posted by SDyke

This produces the result I need but it is super slow. Can anything be done to speed it up?

This one will work much faster, if partno is part of the index:

Code:

SELECT partno
  FROM myFile
 WHERE 
partno like '851545-403%'
And
TRANSLATE(partno , '' , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
       = '851545-403'
;

Lenny

SDyke · #14 (**permalink**) 06-16-10, 14:57

Quote:

Originally Posted by Lenny77

This one will work much faster, if partno is part of the index:

Code:

SELECT partno
  FROM myFile
 WHERE 
partno like '851545-403%'
And
TRANSLATE(partno , '' , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
       = '851545-403'
;

Lenny

That works some better but I just found another issue. The parts can be made at different locations. So the 15th character position can have a lettter in it like M for Mexico. Is there any way to take what you have done here and make it work if there is a character in the 15th position?

tonkuma · #15 (**permalink**) 06-16-10, 17:59

Quote:

... So the 15th character position can have a lettter in it like M for Mexico. ...

Question 1) Is "the 15th character position" fixed?

Question 2) How do you distinguish "a letter at the 15th character position" is a part of a partno and "a letter at the 15th character position" is a suffix?

Are there any partno like 851545-4M?

It will be better to redifine/restate your requirements throughly including all possible formats of partno.

After someone(including me) showed some solutions, you added another requirements and/or another possibility of partno formats.
It is very inefficient and irritate me.

Internet Services

Web Development

Digital Marketing

Consumer Tech