Filter a query using the CONCAT function or similar-CodePudding

I have a query that is filtered on a list of order numbers. The actual filed for the order number is 9 characters long (char). However, occasionally the system that the end users get their order numbers from will generate an extra 0 or single alpha character to the beginning of this order number. I am trying to account for that using the existing SQL and although it is running, it takes exponentially longer (and sometimes won't even run).

Is the approach I am taking below the best way to account for these differences?

order number field example:

066005485, 066005612

example of what may be entered and I need to account for:

0066005485, A066005612

Here is what I have tried that seems to not work or at least be EXTREMELY slow:

SELECT S.order_no AS 'contract_no',
       S.SIZE_INDEX AS 'technical_index',
       S.open_qty AS 'contract_open_qty',
       S.order_qty AS 'contract_order_qty',
       E.excess,
       (S.order_qty - E.excess) AS 'new_contract_size_qty'

FROM   EXCESS E 
JOIN   SIM S ON RIGHT(E.GPS_CONTRACT_NUMBER,9) = S.order_no AND E.[AFS TECH INDEX] = S.size_index

WHERE S.order_no IN ('0066003816','0066003817','0066005485','0066005612','0066005390','0066005616','0066005617','A066005969','A066005970','0066005952','0066005798','0066006673','0066005802','0066006196','0066006197','0066006199','0066006205','0066006697')

OR   CONCAT('0',S.order_no) IN ('0066003816','0066003817','0066005485','0066005612','0066005390','0066005616','0066005617','A066005969','A066005970','0066005952','0066005798','0066006673','0066005802','0066006196','0066006197','0066006199','0066006205','0066006697')

ORDER BY S.order_no, 
         S.size_index

Any thoughts on something that may work better or I am missing?

CodePudding user response：

I can't do anything about the nasty join that requires the right function. If you have any influence over the data base designers it could be fruitful to either have that key (E.GPS_CONTRACT_NUMBER) cleaned up before it is put into the table or get them to add another field where the RIGHT(E.GPS_CONTRACT_NUMBER,9) has already been performed and an index can be created.

But there is definitely something you can do to remove the concat function calculation and take advantage of any index on S.order_no. I noticed your Where clause looks like order_no IN listofvals OR Concat('0', order_no) IN samelistofvals . So instead of adding a zero onto order_no remove a zero from everything in the IN list.

Where order_no IN ('0066003816','0066003817','0066005485','0066005612','0066005390','0066005616','0066005617','A066005969','A066005970','0066005952','0066005798','0066006673','0066005802','0066006196','0066006197','0066006199','0066006205','0066006697', 
'066003816','066003817','066005485','066005612','066005390','066005616','066005617','066005952','066005798','066006673','066005802','066006196','066006197','066006199','066006205','066006697')

Notice that the IN-list is on two lines and the second line is just the first repeated with the leading 0 removed and any entry beginning with "A" removed entirely. This simplifies the Where clause and allows use of indexes, if any exist.

CodePudding user response：

If the efficiency problem is in the WHERE clause (not considering the JOIN operation), in order to improve the situation, you can try using the "pseudo-regex" pattern matching way with LIKE:

WHERE 
    S.order_no LIKE '[A0]06600%' 
OR 
    S.order_no LIKE '06600%'

Warning: this pattern will match also strings that end with other numbers (e.g. 8648).

Does it work for you?