Compare part of string to field
Hi. Hopefully a simple one but beyond my self-taught abilities in iSeries SQL. I have a file with one field containing customer order details including their purchase order reference such as 'A1-123'. The first part of the PO reference is a branch identifier. Unfortunately, as it is a free text field, it is sometimes expressed as '123-A1' instead. I have another file containing branch details where 'A1' = London etc. So I need to look in my first file and find any orders where part of the order reference string matches the branch code in the branch details file. I realise it will be an inefficient means of testing as I will also get erroneous matches but I can also check for the customer account number in the first file which should narrow it down. I *think* the statement should be something like SELECT * FROM FILE1 WHERE CUSTORD LIKE (SELECT * FROM FILE2) but clearly this is an oversimplification. Any ideas? Thanks, Keith
Software/Hardware used:
System i5 (V5R4), SQL
Software/Hardware used:
System i5 (V5R4), SQL
ASKED:
May 14, 2012 10:40 AM
UPDATED:
May 16, 2012 8:04 AM
Other Answers:
Latest Answer Wiki Contributors:
To see other answers submitted to the Answer Wiki: View Answer History.
is it always the first two or last two characters you need to join to?
that is either the first two or last two characters, but may vary on each record
Usually either first or last few characters – the branch id portion is normally either 2 or 3 characters. Sometimes a different delimiter i.e. ‘/’ instead of ‘-’ or no delimiter at all. I realise it’s going to be a pretty fuzzy search.
Hi Keith,
Am not sure whether it will be helpful for you if you are directly running SQL.
But, I hope it can do some help if you require this process to be done programatically(through SQLRPGLE).
Step1: You can build a curson on FILE2(which holds Branch Number) and Fetch the Branch ID.
SELECT BRNID FROM FILE2
Build Cursor, fetch from cursor(Cursor1). Then follow Step2.
Step2: Build a query on FILE1 for retrieving the details which are related to that Branch Number.
SQLSTMT = ‘SELECT * FROM FILE1 WHERE CUSTID LIKE %’ + BRNID + ‘%’
Prepare a cursor and Read the records which are matching with that branch number
Perform required operation till EOF(SQLCOD = 100).
Step3: Fetch Next from Cursor1(on FILE2) and perform Step2.
Let me know if you are not clear or if you are looking for different requirement.
Pradeep.
Perhaps something like one of these?
Select po_ref, branch, br_desc
from f1, f2
where po_ref like ‘%’ || TRIM(branch) || ‘%’
or
Select po_ref, branch, br_desc
from f1 left join f2
on po_ref like ‘%’ || TRIM(branch) || ‘%’
Genius! It worked although I had to modify it slightly as below:
select ornum,custpo,branch
from orders f1, branches f2
where custpo like ‘%’||trim(f2.branch)||’%’
and ordat between 1110101 and 1111231
and orcus = ‘AA11′
order by ornum
many thanks
Understood. That’s fine in this case as F2 is a list of branches that I want to query anyway, so my intention is only to get matches in both files. Thanks again for your solution.