Jump to content
Sign in to follow this  
ShadowMage

Join Using LIKE

Recommended Posts

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 by ShadowMage

Share this post


Link to post
Share on other sites

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"?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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".

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×