Join Using LIKE

[ShadowMage]

Hey guys,

I have the following SQL statement:

 SELECT OrderHed.OrderNum, OrderHed.SalesRepList, SalesRep.SalesRepCode, SalesRep.Name FROM OrderHed INNER JOIN SalesRep ON OrderHed.Company = SalesRep.Company AND LCASE(OrderHed.SalesRepList) LIKE CONCAT('%', LCASE(SalesRep.SalesRepCode), '%') WHERE (SalesRep.SalesRepCode = 'ABC') 

Where OrderHed.SalesRepList is a delimited list of codes in the format "ABC~DEF~GHI" and order is not always the same.  I realize this is bad design, but I have no control over the database design (it's Epicor) so I just have to work with what I've got.

I need to be able to join the OrderHed table and the SalesRep table on that list of codes.  My Google searches suggest that the syntax above should work, but when I run that I get the following error:

Quote

[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Encountered internal error in SQL ENGINE

Is this a limitation on the Progress engine or am I doing something wrong?

 

EDIT:
Actually, now that I think about it, I wonder if it's failing because it's trying to create multiple joins in one.  Ie, in the example rep list I gave, it would be trying to join the rep codes ABC, DEF, and GHI all at the same time.  Are there any other ways around this to accomplish what I'm trying to do?  Maybe a nested query of some kind (those really confuse the heck out of me...)?

Edited August 27, 2018 by ShadowMage

[justsomeguy]

I would contact Epicor and ask them how they expect you to do anything productive with a database structure that isn't normalized. Are the codes always the same length?  Could you ever have a code that is a subset of another code, like "LL" vs "LLX"?

[ShadowMage]

28 minutes ago, justsomeguy said:

I would contact Epicor and ask them how they expect you to do anything productive with a database structure that isn't normalized.

lol.  They'll tell me to go to their Yahoo user group.  Been there done that.

Anyways, no the codes are not the same length and yes there could be codes that are subsets of others.  They are user generated codes and typically some abbreviated form of a company name.

[ShadowMage]

The best I've been able to come up with so far is something like this:

 SELECT OrderHed.OrderNum, OrderHed.SalesRepList, SalesRep.SalesRepCode, SalesRep.Name FROM OrderHed INNER JOIN SalesRep ON OrderHed.Company = SalesRep.Company AND LCASE(LEFT(LTRIM(OrderHed.SalesRepList, '~'), (INSTR(LTRIM(OrderHed.SalesRepList, '~'), '~')-1))) = LCASE(SalesRep.SalesRepCode) WHERE (SalesRep.SalesRepCode = 'ABC') 

which works, but only returns records if the rep is the first one in the list.  Ie, it'll return an order where rep list is "ABC~DEF" but not one where rep list is "DEF~ABC".

[justsomeguy]

Yeah, with that kind of thing you have to search for this:

field='val' OR field LIKE 'val~%' OR field LIKE '%~val~%' OR field LIKE '%~val'

So, where the field is only the value, or starts with it, or has it in the middle, or ends with it.  A regular expression could probably shorten that a little bit, but that's the joy of working with data that isn't normalized and avoiding false-positives.

[ShadowMage]

Well, adjusting my join to use syntax like that I now get this error:

Quote

[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Too many values specified (7531), SQL state 21S01

I suspect that this is due to what I mentioned previously about there being multiple possibilities to join on and it doesn't know which to use.  It looks like I might just have to find a different way to get the information I need (multiple queries and looping through them in PHP perhaps).  Thanks, JSG!