Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Expression: locate funtion returning incorrect result

Status
Not open for further replies.

morechocolate

Technical User
Apr 5, 2001
225
US
I have a SQL Expression that was not returning the result that I had expected so I broke it (the expression) down to test the locate function. When I did the test it was returning 0 for all values and that, I know for sure, is not correct. Anyone have any ideas what I may be doing wrong?

FYI I am using Crystal 8.5

(select {fn LOCATE(p.loan_name, PHIST."LOAN")}
from loanhist p
where p.accounting_date = PHIST."ACCOUNTING_DATE" and
p.loan = PHIST."LOAN")

p.loan_name for example is Pottery 123456 (Note 1) and
loan = 123456

My understanding of the locate function is that it should look for the loan, 123456, within the loan_name and return the position of the first character of once located.

Thanks
 
Well...partially solved.

Where the loan_name field is like Boxes 123456, a value is returned, but for something like the example given above, Pottery 123456 (Note 1), 0 is returned. That has me baffled.
 
Dear Morechocolate,

The locate function is funky. You cannot use it more than once.

You might try using a real sql function instead of the odbc function Locate, look at CharIndex or PatIndex if this is a SQL Server database.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762
 
Hi Rosemary,

I tried using charindex instead

(select charindex(p.loan_name, PHIST."LOAN")
from loanhist p
where p.accounting_date = PHIST."ACCOUNTING_DATE" and
p.loan = PHIST."LOAN")

but received the same results - for some reason anything like

loan_name = property name 787888 (xxxxxx) where the loan that is used in the search is in the loan_name is returning 0, but if it is property name 787888 it returns the correct value.

using the below expression yielded 0 for all records

(select patindex(p.loan_name, PHIST."LOAN")
from loanhist p
where p.accounting_date = PHIST."ACCOUNTING_DATE" and
p.loan = PHIST."LOAN")


 
Dear Morechocolate,

Hmmm, it seems that anything with the parens in it is throwing you off.

What is the upshot here? Do you just need to get a value returned if the loan name is in the loan?

I think what you will have to do is use a charindex to test the occurrance of the '(' in the string and then only search a substringed value without the end of the string with the (text) in it.

I can't test this, so you may have to fuss with it a little, but try this:


(Case when (select charindex(p.loan_name, ')')
from loanhist p
where p.accounting_date = PHIST."ACCOUNTING_DATE" and
p.loan = PHIST."LOAN")> 0
then
(select charindex(substring(p.loan_name,charindex(p.loan_name,')')-1),PHIST."LOAN")
from loanhist p
where p.accounting_date = PHIST."ACCOUNTING_DATE" and
p.loan = PHIST."LOAN")
else

(select patindex(p.loan_name, PHIST."LOAN")
from loanhist p
where p.accounting_date = PHIST."ACCOUNTING_DATE" and
p.loan = PHIST."LOAN")

end
)

regards,

ro


Rosemary Lieberman
rosemary-at-microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top