Compare part of string to field

35 pts.
Tags:
iSeries SQL
iseries v5r4
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BigKat
    is it always the first two or last two characters you need to join to?
    7,870 pointsBadges:
    report
  • BigKat
    that is either the first two or last two characters, but may vary on each record
    7,870 pointsBadges:
    report
  • Keithbrc
    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.
    35 pointsBadges:
    report
  • deepu9321
    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.
    3,650 pointsBadges:
    report
  • philpl1jb
    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) || '%'
    49,510 pointsBadges:
    report
  • Keithbrc
    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
    35 pointsBadges:
    report
  • philpl1jb
    The Join you've choosen is an inner join, it will only show rows from F1 and F2 where there are matches in both files. Phil
    49,510 pointsBadges:
    report
  • Keithbrc
    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.
    35 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following