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.
1 2 3 4 | 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))) |
Posted In Sage Line 500 ERP | SQL Server 2008