0

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.

CBRF23
  • 1,340
  • 1
  • 16
  • 44
  • Cory - you're a genius. If you want to make your comment an answer, I will mark it as correct. I changed all columns to be formatted as text and I'm getting the results I needed. Thanks!! – CBRF23 Aug 23 '16 at 19:15
  • I'd be curious if my edit works as well: adding `IMEX=1` to your Excel connection string. Original comment converted to an answer. – Cᴏʀʏ Aug 23 '16 at 19:16
  • I tried to change formatting back to "general" so that I could test the "IMEX" setting, but it seems once I set it to "TEXT" once it was a permanent fix, because I couldn't get it to revert back to prior behavior no matter what format I set to (even number). But - interestingly - after adding IMEX=1 to the conn string, and hitting "okay", the next time I went to edit the connection string it wasn't there. Tried it a couple times to be sure, but excel (or MS Query) seems to remove that for some reason. – CBRF23 Aug 23 '16 at 19:32

1 Answers1

1

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.

Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
  • Note - excel (or MS Query) seems to remove `IMEX=1` for some reason. I tried adding it to the connection string (just at the end of the string) and after committing it, the next time I went to edit the connection string it wasn't there. Tried it a couple times to be sure, but it just disappears - no warning or notification to the user in any way, it's just gone. Not sure why, but wanted to note it for others who find this answer later on. – CBRF23 Aug 25 '16 at 22:02