FiveTech Support Forums

To All

I am writing a MS Sql Server Select statement and I need to use the Sql Substring() function .. Consifer this code as the first statement works, however the second does not due to the variable substitution ..
Any advice would be greatly appreciated.

Rick Lipkin

Rick,

In reference to the (non working) second sample, how do you insert the nLen value (3) in cSQL?.

May be using str with just 1 parameter can led to ambiguos results, so let me suggest to set them for ensuring the result.

An alternative approach could be to use LIKE instead of Substring

Rick Lipkin wrote:To All

I am writing a MS Sql Server Select statement and I need to use the Sql Substring() function .. Consifer this code as the first statement works, however the second does not due to the variable substitution ..
Any advice would be greatly appreciated.

Rick Lipkin

Code (fw): Select all Collapse

cSQL := "Select * from [Coproposal] where Substring(CINumber,1,3 ) = '"+ltrim(str(nProj))+".' Order by CINumber" // works  cinumber = '27.'

Code (fw): Select all Collapse

nLen := 3
cSQL := "Select * from [Coproposal] where Substring(CINumber,1,nLen ) = '"+ltrim(str(nProj))+".' Order by CINumber" // fails '27.' cinumber = '27.'

Intenta con SUBSTR(str FROM pos FOR len), saludos...

Carlos

I originally used the Like % but, Like does a full table scan and can be brutal for our site people on VPN .. I was actually working from VPN last weekend and noticed the lag when the program got to that statement .. actually trying to do some Sql Tuning to improve performance.

Rick Lipkin

To All

Using the same logic to concatenate in the Substring parameters as I did the nproj .. here is the working Sql Statement.

Thanks
Rick Lipkin

Hi Rick,

I'm not an MS SQL Expert, i don't know if using functions in the WHERE part (like substring()) will be optimized by the server, i think it won't.
A different approch: Let's say CINumber values are <nproj>.<fix_len_number> like '12.00356', then the '12' project related records in coproposal will be those with CINumber >= '12.00000' and less than '12.99999', so


If there is an index on CINumber, this query will be absolutely optimizable, it should fly.
If the code is not numeric you can change the where limits to sth according to.

Carlos

Thank you for your good suggestion .. unfortunitly I have inherited a system that has multiple relational tables and it goes something like this

Project_Number is the primary key and BINumber is the foreign key .. where I got into trouble was using the Like% operator :
cSQL := "Select * from BRevision where BRNumber like '"+ltrim(str(nProj))+"%' Order by BINumber"

As you can see .. the like% operator would have returned both records and there are additional relational tree's from Brevision on other foreign key's in tables Corposal and the Citem table which uses the same root foreign key concatenated rule as root+.01,.02,.03, etc ..

The results I got did not seem different from the original program I am replacing .. all the totals seemed to add up, only when I looked at the oRs:RecordCount() did I realize I was returning a ton of rows at a horrible performance price. After reviewing the recordsets did I realize my error using Like% on the root and the wisdom of the origional designer by using a . to delineate the foreign key.

At that point I had no choice but to Substring() out my query and add the . to the Primary key in searching for the relational BINumber foreign key.. and the result was this :


As far as performance, and the full table scan ... I have not tested it on a slow or VPN connection and I do not know if I will gain any better tuning, but I definitely will return less rows and an accurate relational result in the case of a primary key overlap.

Thanks
Rick Lipkin