Constructing a Where Clause for a View 2

[stanlyn]

Hi,

I need help with writing where clauses for a view. Here is a sample...

lcWord1 = 'Chris'
lcWord2 = 'Friley'
lcWord3 = 'Stanley'

Create Sql View 'rv_MailingList' Connection AppData ;
	AS Select company_name, full_name, address1, address2, city, state, postal_code, county_name, pk ;
	From MailingList ;
	WHERE full_name ????????

How do I construct a where clause for a view like this to select,

1. all rows where full_name contains 'Chris' or full_name contains 'Mike' full_name contains 'Andy' and in any order? I see the =, ==, <>, like, and other operators, but not a contains ($) operator. I also saw the "field IN (value1, value2, and value3)" form, but that stops evaluating after 1st hit, so that won't work.

2. should the view be created above the dowhile loop while iterating a table? If so, what part of the view do I need to change while iterating (changing the parameter's values) then requery().

3. Are the syntax and commands used for the where construction SQL, VFP or mixed?

4. Is it acceptable and efficient to delete the view and recreate everytime something changes about it, like any of the where clause stuff?

Thanks,
Stanley

 

[Mike Lewis]

Stanley,

The first thing that you should know is that the syntax of the query must follow the rules specified for the back-end server, which are not necessarily the same as for VFP. So whenever you ask a question about accessing data on a back-end server, you have to specify whether the server is SQL Server or MySQL or whatever else.

That said, in this case, the syntax is fairly uniform. Your WHERE clause will look something like this:
[tt]
... WHERE Full_Name IN ('Chris', 'Mike', 'Andy')[/tt]

Re your other questions:

should the view be created above the dowhile loop while iterating a table

I can't see any reason why you need a DO WHILE loop in this case. The view, as it stands, will retrieve the specified records in one go.

Are the syntax and commands used for the where construction SQL, VFP or mixed?

As explained above, the syntax is that of the back-end.

Is it acceptable and efficient to delete the view and recreate everytime something changes about it

As I explained in your other thread, you can't dynamically change the view's SQL code. If you want to do that, you have to delete and recreate the view. However, I can't see any reason to do that in this case. By using the WHERE clause shown above, you will be able to retrieve all the desired records.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

If you're creating a local view, then you can use all VFP syntax and in this case $ is probably what you need:

WHERE (?lcWord1 $ FullName) AND (?lcWord2 $ FullName) AND (?lcWord3 $ FullName)

If it's for a backend other than VFP, you need to use the appropriate syntax for that backend. They all have a way of searching in strings.

Tamar

 

[stanlyn]

Hi Mike,

I can't see any reason to do that in this case. By using the WHERE clause shown above, you will be able to retrieve all the desired records.

The reason for the dowhile loop is to iterate thru rows in a table not related to the view itself. For each row I iterate, I need to change the parameters for a view that needs to be run to get the rows relevant to the current row As I process

MyNames.dbf is my list of people where I have split their names into single words whereas full_name for row one is "Mike Fox Smith". I split the name and now have (3) word fields. MyNames.word1 = 'Mike', word2=Fox and word3=Smith
row two contains "Sarah Jane Miller", and so-on.

mailinglist is a 2 million row SQL table of people with their names and addresses.

print.dbf is the output table that contains the results of searching the mailing list table for my names

Flow...
I start by selecting MyNames table and setup a dowhile loop to process all the names within it,

Next, while processing each name in MyNames, I need to either create or modify a remote view to the MailingList table by changing the values of word1, word2 and word3 to match the MyNames current record.

Then search the full_name field of Mailinglist to see if the 3 words are contained anywhere within it.

Now add all of the returned rows from MailingList to the print.dbf

Next, do a skip 1 in MyNames and start the whole lookup process over.

My original question was where to put this remote view definition. Should it go BEFORE the dowhile loop or WITHIN? Or should only the assignment of values to the parameters go within the dowhile? I think it would be more efficient to define the view once, before the dowhile, and only change parameters and requery() within the dowhile.

How expensive in terms of performance and processing power is it to delete a view and recreate it? To me it looks like a resource hog and should only be used sparingly, and I may be wrong, and why I'm asking.

And, please do confirm whether I'm understanding this correctly.

Thanks,
Stanley

 

[stanlyn]

Hi Mike,

Your view where clause should look like

lcWord1 = 'Chris'
lcWord2 = 'Friley'
lcWord3 = 'Stanley'

WHERE full_name like ?m.lcWord1 or full_name like ?m.lcWord2 or full_name like ?m.lcWord3

How do I create the where statement when wildcarding using parameters? To better explain, I'll use VFP code
VFP = where lcWord1 $ full_name .and. lcWord2 $ full_name .and. lcWord3 $ full_name

I also got this hardcoded version to work.
WHERE Mailinglist.full_name like '%Mike%' AND Mailinglist.full_name like '%Yearwood%'

QUESTION, what does this hardcoded values line look like after being changed to use parameters assuming the parameters are named word1, word2 and word3?

Thanks, Stanley

 

[Mike Lewis]

what does this hardcoded values line look like after being changed to use parameters

I explained that to you in some detail in thread184-1817991. At that point, I was under the impression that you understood what I had said. Is that not the case?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Mike,

Can you be more specific about where in the thread you answered this. There is a lot of a little-piece here and a little piece there with no real how-tos from end to end.

I know this is trivial stuff for anyone that has been doing it for a long while, but I'm a real newbie with remote views, having made several attempts, get frustrated and move on to other things I can make headway with. And eventually I come back for more pain...

I may have a small handle on it for the time being with still a lot of questions in my threads unanswered. It would be very helpfull to get a yes or no feedback to questions and how I'm doing things, as I am posting some code. Without it, I keep pecking in the dark.

Anyway, thanks to everyone for your suggestions, as I'm making some progress now.

Stanley

 

[Mike Lewis]

Stanley,

It was mainly in my first post in the above thread, where I explained about using a parameterised filter. To recap, if you want to vary the WHERE clause in your query, you use syntax similar to following:

[tt]Sales > ?lnSales[/tt]

This will retrieve all rows where the sales field is greater than a defined figure. The question mark in front of lnSales indicates that lnSales is a variable in your program. So by varying the value of that variable, you can apply a different criterion each time you requery the view. The rest of my post will explain that in a bit more detail.

I might have confused you into thinking that a parameterised filter is something to do with the SET FILTER command. It's not. A parameterised filter is what you enter in the Filter tab of the view designer. SET FILTER is used to filter an existing local VFP table or cursor, and has nothing to do with views.

I hope this makes sense.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads