CJSmith dot me

I dump stuff I find useful here

Sage Line 500 EDI IN Intermediary tables inner join on sql query

Published / by Chris Smith / Leave a Comment

On ocassion I need to look at what is in the EDI IN modules intermediary tables, scheme.eiordtm has an annoying key_code which it combines multiple objects. It also doesn’t always have the same amount of characters. As the key_code on scheme.eiordtm starts with the key_code from scheme.eiorhdm all I do is find out how many characters are in key_code from scheme.eiorhdm and use the SQL LEFT function on the join.
Additional columns can be added but this is just the defaults I use.

SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity
 FROM "scheme"."eiorhdm" t1
  INNER JOIN "scheme"."eiordtm" t2
   ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))

Leave a Reply