Where Clause Construction

[stanlyn]

Hi,

I need some direction on how to construct a where clause for a view. The goal is to select all rows containing a word or phrase no matter where in the field it is. I have hardcoded in the test example below a first name of "Albert" and last name is "Lykins"

Results from the searched field can contain "Albert Lykins", " Lykins, Albert", "William, John Lykins and Albert Lewis".

I did NOT wish to see "Alberta Lykins", "Lykins, Mary & Albertson"

Below is my test bench where I have tried many many variations and none is returning the expected results. So simple in fox...

Thanks, Stanley

Set Ansi Off

Set Path To '.\, .\data, .\programs'
Open Database 'TestStatus'
Use In Select('rv_ML2')

If Indbc('rv_ML2', 'VIEW')
	Delete View 'rv_ML2'
Endif

lcBase = [Create Sql View 'rv_ML2' Connection AppData ] + ;
	[AS Select full_name, address1, county_name ]+ ;
	[From dbo.MailingList MailingList WHERE ]

lcFirst = 'Albert'
lcLast = 'Lykins'

lcPhrase1 = lcFirst
lcPhrase1A =  [] + lcFirst + [%]

lcPhrase2 =  [%] + lcLast  + [%]

lcWhere9 = [( MailingList.full_name Like ?lcPhrase1 or MailingList.full_name Like ?lcPhrase1A) ] 	&& ;
		+ [ and MailingList.full_name Like ?lcPhrase2 ]

lcCreateView = m.lcBase + m.lcWhere9
&lcCreateView

Use 'rv_ML2' In 0
Select 'rv_ML2'
Requery()
Go Top
Browse Last

 

[Mike Lewis]

Stanley,

Before looking at this in more detail, could I just check this line of code:

lcWhere9 = [( MailingList.full_name Like ?lcPhrase1 or MailingList.full_name Like ?lcPhrase1A) ] 	&& ;
		+ [ and MailingList.full_name Like ?lcPhrase2 ]

Did you intend to comment-out the second line in the above code? That's what you have done by putting [tt]&&[/tt] if front of the semi-colon. If that was what you intended, that fine. It's just that the effect will be to not take account of lcPhrase2.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

OK, looking at a bit more detail: You say that you DO want "Albert Lykins" but you do NOT want "Alberta Lykins". I'm afraid that's not possible by using the LIKE clause. The point is that %"Albert%" will always be like "Alberta". I can't off-hand think of any way of achieving what you want with LIKE.

In native VFP, you could probably solve the problem using GETWORDNUM(), but as far as I know there is no equivalent in standard SQL. Which brings me to the next point:

The fact that your CREATE VIEW statement has a CONNECTION clause indicates that this is a remote view. As I'm sure I have told you before, the syntax of the SQL statement in a remote view must conform to that of the back-end database. So, in order to answer this type of question, we have to know which back-end database you are using. It could be that that specific version of SQL has some functionality to do what you want, but we have no way of knowing that.

If all else fails, you might have to bring the entire table into a VFP cursor, and find the required records by looping through it and looking at each word in the relevant fields. That would be horribly slow, of course, if the table is at all large.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Hi Mike,

Did you intend to comment-out the second line in the above code? That's what you have done by putting && if front of the semi-colon.

Ye, for the time being, I was making it less complicated by removing the additional layer. Solving this first part will solve the 2nd, 3rd and 4th parts.

Thanks, Stanley

 

[stanlyn]

Hi Mike,

You say that you DO want "Albert Lykins" but you do NOT want "Alberta Lykins". I'm afraid that's not possible by using the LIKE clause. The point is that %"Albert%" will always be like "Alberta". I can't off-hand think of any way of achieving what you want with LIKE.

Yes to the first part and thanks for the 2nd part as I won't waste any more time with the like keyword. What we need here is a "contains" operator. Is there any other method that may be able to do this?

However, MS SQL does have a contains and freetext keywords within their full text index & search functionality. I do have some experience with that and will do some testing.

In native VFP, you could probably solve the problem using GETWORDNUM(), but as far as I know there is no equivalent in standard SQL.

I do not believe that getwordnum() will work as this needs to search for single and multi words (phrases). I haven't checked, but think it is for only single words. A common usage could be if "shell energy" looked for and rows that had "shell energy company" and "John Smith, shell energy" would match. Rows with "Shelly Strong", "Energy, Shell", "Energy from Shell" would NOT match and be excluded.

1. The fact that your CREATE VIEW statement has a CONNECTION clause indicates that this is a remote view.
2. which back-end database you are using. It could be that that specific version of SQL has some functionality to do what you want.

1 = YES, remote view
2 = MSSQL 2016 Standard
3. VFP9 and VFPA

If all else fails, you might have to bring the entire table into a VFP cursor, and find the required records by looping through it and looking at each word in the relevant fields. That would be horribly slow, of course, if the table is at all large.

As said in my original post, this sort of functionality is easy in fox. A simple search of "if 'Albert ' $ field would do it. Notice the appended space at the end of Albert. To contend with the possibility that Albert is the last word in the field, maybe the best approach is to copy the field to a var and add a single space to the end of the all trimmed field would allow my if statement above see it. (just thinking out loud)

And as always, speed is extremely important.

Thanks,
Stanley

 

[stanlyn]

Hi Mike,

The point is that %"Albert%" will always be like "Alberta".

What if var lcFirst had a space appended to it, making it "Albert ". I have tried some variations of that but probably not do it right. Do you know if vfp or sql implies an alltrim to whats inside %"Albert%"?

I also see that changing the value of lcFirst from "Albert" to "Albert " DOES produce different results, but not what I need. One way produces no results.

Thanks, Stanley