Like Search Question

[Adein]

I have some embedded SQL that is returning some not-so-correct answers.

SELECT DISTINCT cfcode,cfdsc1
FROM items AS itm INNER JOIN confil AS con ON cftype= 'COLR' AND cfcode = SUBSTR(ititm#, 7, 2)
WHERE ititm# LIKE TRANSLATE(:s5bmcmporg,'%','X') AND
                           itact = 'A';

:S5bmcmporg = 'SC09XX00'

When I run the query I get nothing.

In the database SC090400 defiantly exists and should be coming up as an option. Also, the color 04 exists in confil so the inner join shouldn't be failing either.

Does anyone have any ideas on why the query is returning nothing?

SQLCOD=000000
SQLSTATE=2000

Moreover, does a SC09%%00 work with a like? It seems like the two zeros at the end might cause an issue (even using the wrkqry doesn't seem to want to be able to use the %% with zeros after. But If I use SC11%%%%SA (for example) it works just fine)

[B.Hauser]

Re: Like Search Question

It makes no difference if you insert a single or multiple % signs into your String.
You are always looking for values that start with SC09 and ends with 00 in the last 2 postions of the string. If your field itim# is for example fixed format 10A, position 9 and 10 always must be 0.
If you only want to select all values starting with SC09 followed by 2 undefined characters followed by 00 and followed by any character, you need to specify: 'SC09__00%'.

_ is the replacement for a exactly a single character
% is the replacement for an undefined number of characters
(same as in Query/400)

Birgitta

[Adein]

Re: Like Search Question

Thanks B.Hauser,
I've never looked at the _ as a single char replacement wildcard. Interesting.

Table1 data

ITITM#
SC11XXXXS
SC110405SA
SC110T01SB
SC11XX00
SC110200
SC110400
SC110401SC

If I run a query in Query/400 using ITITM# LIKE 'SC11%00' I get nothing. ITITM# LIKE 'SC11_00' I get nothing.

In iSeries Navigator running straight sql queries

SELECT *
FROM ITMAS
WHERE ITITM# LIKE 'SC11%00'

or

SELECT *
FROM  ITMAS
WHERE ITITM# LIKE 'SC11__00'

I get noting in either query.

If I eliminate the zeros at the end of the search string to

...
ITITM# LIKE 'SC11%'

I get too much information (all of the SC11%).

What is wrong with the sql in

SELECT *
FROM 
ITMAS
WHERE ITITM# LIKE 'SC11%00'

I would expect to get
SC11XX00
SC110200
SC110400

based on the table from above.

Thanks for the help, I'm pretty sure I missing something easy here.

[B.Hauser]

Re: Like Search Question

What is wrong with the sql in

SELECT *
FROM 
ITMAS
WHERE ITITM# LIKE 'SC11%00'

I would expect to get
SC11XX00
SC110200
SC110400

based on the table from above.

No you won't, because your field content does not end with double 0 but with *BLANKS.
To get all information you want, you need to add a % sign at the end of your search string.

LIKE 'SC11%00%'

Birgitta

[Adein]

Re: Like Search Question

No you won't, because your field content does not end with double 0 but with *BLANKS.
To get all information you want, you need to add a % sign at the end of your search string.

LIKE 'SC11%00%'

Birgitta

Alright, it's finally hit me like a pile of bricks.

I have ten characters. LIKE 'SC11%00%' actually gives me too much. What I really need is

...
LIKE 'SC11%00  '

with spaces at the end so that if fills the rest of the area of the field.

That is a RGP/SQL gotcha in my book. I think perhaps mysql/mssql would have played a little nicer and done the trim and found the result like I was expecting.

Thank you Birgitta.

[FaStOnE]

Re: Like Search Question

Gotta love it!! Another satisfied Customer!!

[MichaelCatalani]

Re: Like Search Question

Alright, it's finally hit me like a pile of bricks.

I have ten characters. LIKE 'SC11%00%' actually gives me too much. What I really need is

...
LIKE 'SC11%00  '

I think you need to specify 'SC09__00%' like Birgitta said in a previous post. In your response to that post, I didnt see where you tried that specific one out. That would tell SQL to select any item# that starts with SC09, followed by any two characters, followed by '00', followed by anything (or nothing).

That is a RGP/SQL gotcha in my book. I think perhaps mysql/mssql would have played a little nicer and done the trim and found the result like I was expecting.

If you are referring to the original :S5bmcmporg = 'SC09XX00' statement, I would hope mysql/mssql wouldn't act any differently, because that would be wrong. If those products pulled any item#'s that did not end in '00', they would not be utilizing the "%" wildcard selector correctly.

I have ten characters. LIKE 'SC11%00%' actually gives me too much. What I really need is

This one can catch a person off guard, because it says to select 'SC11' followed by anything in any number of characters, or even no characters, that is followed by '00', followed by any number of characters or nothing. That means it would select an item# that has potentially any number of characters between the 'SC11' & '00' characters, such as SC11KJDKJHDKHD00' but would also select 'SC1100'.

Since you specifically want any TWO characters following 'SC11', you want to use a '_' for each character. (ie SC11__00%) This would pull items numbers that consisted of 'SC11??00', followed by anything or nothing.

[Adein]

Re: Like Search Question

Michael

I need to explain myself a little better.

I want to search for SC11__00 with nothing after the zeros.

In the table, there are lots of other combinations of SC11__00% that for this instance wouldn't be valid values for the return set.

I agree that the the results I want are included in the set of SC11__00%, but there are also other results that I don't want, which forced me to add the spaces after the zeros to get the data set I needed.

Here is the difference between the queries.
LIKE 'SC11__00%'

SC11XX00  	
SC110200  	
SC110200P2	
SC110200SA	
SC110400  	
SC110400P2	
SC110400P4	
SC110400SA	
SC110400SB	
SC110400SF	
SC110400ST

and

LIKE 'SC11__00 ' (with two spaces to fill out the 10)

SC11XX00  	
SC110200  	
SC110400

What I wasn't taking into consideration was that the whole string wasn't actually 10 long.

[MichaelCatalani]

Re: Like Search Question

Hi Aiden,

Yes, that makes sense now with the way you are coding it. You are basically saying that you only want to select item numbers that


a. Start with "SC11"

b. The next two characters can be anything

c. The next 4 characters must be zero / zero / blank / blank

d. it assumes that your item number is 10 charcters long. (If it were longer, it would fail, even if the field positons beyond 10 contained blanks.)

[Adein]

Re: Like Search Question

This has been a very good post for the explanation of the wild card characters, brought to you by Birgitta, Michael Catalani and the Code400 forums.

Please stay tuned for the next exciting episode where we talk about working with data in SQL queries or prepping the data before hand!