I don't think, there is a way to optimize "inclusion"...

>If you have a SQL like this:
>
>select * from job where po_num like "%123%" or order_num like "%123%"
>
>is it optimized (or, is there a better way)? My point is, I have a searh form where users can search for a string sequence in the PO_num, or the order_num, or the desciption of any line item on the order, and it runs kinda slow, and I was just wondering if the above query method is the fastest way or if there is a better way to do this kind of search.
>
>Basically, I have to allow subtring searches on many different fields, and I am looking for any match.
>
>In case you are interested, here is the actual code:
>
>
>
>*--- Trim inputs -----
>cJob_num=alltrim(upper(thisform.job_num))
>cPO_num=alltrim(upper(thisform.po_num))
>cDesc=alltrim(upper(thisform.srchtext))
>cVendor=alltrim(upper(thisform.vend_num))
>cCustomer=alltrim(upper(thisform.cust_num))
>
>do case
> case thisform.optionStatus.value=1
> cStatus='A'
> case thisform.optionStatus.value=2
> cStatus='C'
> case thisform.optionStatus.value=3
> cStatus=''
>endcase
>
>do case
> case thisform.optOrder.value=1
> cOrder="Vendor,Order_Date"
> case thisform.optOrder.value=2
> cOrder="Order_Date,Vendor"
> case thisform.optOrder.value=3
> cOrder="PO,Order_Date"
> case thisform.optOrder.value=4
> cOrder="Cust_num,Order_Date"
>endcase
>
>
>lcSQL="SELECT mat_code as Job_Num,"+;
> "mat_po as PO,"+;
> "company as Company,"+;
> "mat_qty as Qty,"+;
> "mat_des1 as Desc,"+;
> "material.mat_cost as Cost,"+;
> "material.mat_cost*mat_qty AS Total,"+;
> "ord_date as Order_Date,"+;
> "mat_inv as Invoice,"+;
> "mat_quote as Quote,"+;
> "cust_num as Cust_Num,"+;
> "status as Status,"+;
> "vendor "+;
> "FROM material, vendor, job_info, Purchase "+;
> "WHERE Purchase.vendor=vendor.vendno"+;
> " and material.mat_po=Purchase.po_num"+;
> " and material.mat_code=job_info.job_num "+;
> "HAVING "
>
> lcAnd = ""
>
> if not empty(cJob_num)
> lcSQL = lcSQL + lcAnd + "job_num like '%" + cJob_num + "%' "
> lcAnd = "AND "
> endif
> if not empty(cPO_num)
> lcSQL = lcSQL + lcAnd + "po_num like '%" + cPO_num + "%' "
> lcAnd = "AND "
> endif
> if not empty(cDesc)
> lcSQL = lcSQL + lcAnd+ "desc like '%" + cDesc + "%' "
> lcAnd = "AND "
> endif
> if not empty(cVendor)
> lcSQL = lcSQL + lcAnd + "Vendor = '" + cVendor + "'"
> lcAnd = "AND "
> endif
> if not empty(cCustomer)
> lcSQL = lcSQL + lcAnd + "Cust_num = '" + cCustomer + "'"
> lcAnd = "AND "
> endif
> if not empty(cStatus)
> lcSQL = lcSQL + lcAnd + "Status = '" + cStatus + "'"
> endif
>
> lcSQL = lcSQL + " INTO cursor mat_srch ORDER by " + cOrder
>
>
> &lcSQL
>