CBRF23 CBRF23 - 3 months ago 8
SQL Question

Why does my LIKE filter only match on one value?

I have this excel file which is giving me unexpected results on an SQL query with a join on

LIKE
criteria.

Here is the SQL statement:

SELECT CO.MATERIALS, CO.SIZES, CO.TOOLS, IR.PN, IR.BODYJAW

FROM `CROSSJOIN$` CO
LEFT JOIN `INSERTS$` IR

ON CO.TOOLS=IR.TOOL
AND ((IR.MATERIAL Like '%'+CO.MATERIALS+'%'))
AND ((IR.SIZE Like '%'+CO.SIZES+'%'))


And the issue is, that the criteria for
IR.MATERIAL
will only match on one value "333". I can't figure it out. Here's a visual explanation of the what's happening (in case you don't want to download file):

enter image description here

The other two filters work just fine, separately or in combination, and all matches come back as expected. It's just this
MATERIAL
field that is giving me heartburn. If I delete the other two filters out of the SQL statement, I still only get matches on records with material "333" and if I delete that material from the left table, I get no matches at all.

Does anyone know what would cause this behavior?
As a sanity check, I tried vlookup in excel on these tables and it matched material without problem, so the data is right and does match.

Answer

It's interesting that only the 333 matches.

I think what's happening is that the first values Excel is seeing in your Material columns is 333, so it's inferring the column type is an Integer value, which would limit the potential matches to other Integer values.

You could test that theory by fudging other material codes -- make a "444" or something in each worksheet and see if that matches. If that's the case, ensure that the datatypes of the columns are what you want them to be (all text, I assume).

Alternatively, adding IMEX=1 to your connection string might be easier -- it will make the Excel data reader treat all data as strings.