SQL Expression: locate funtion returning incorrect result

[morechocolate]

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

 

[morechocolate]

Problem solved, I had to flip the arguments around.

Thanks

 

[morechocolate]

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.

 

[rosemaryl]

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,

http://www.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

 

[morechocolate]

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")

 

[rosemaryl]

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,

http://www.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