Gary,
You can build up a where clause, while testing what has been filled in by the
user.
Suppose the user can fill in 2 variables:
V_var1 text
V_var2 Date
Set them to null before the procedure starts
Define a variable for the where clause, like V_where text
After filling in the criteria you can build up your query:
Set var V_where text = null
IF V_var1 is not null then
Set var V_var1 = (.v_var1 + ‘%’)
Set var V_where = ( ‘like .V_var1’)
endif
IF V_var2 is not null then
Set var V_where = (.V_where & ‘ AND <columnname> = . V_var2)
endif
and so on. Don’t forget the AND or OR
The selection will look like this:
Select all from <tablename> where &V_where
Succes
Tony
From: [email protected] [mailto:[email protected]] On Behalf Of Gary Randall
Sent: maandag 17 mei 2010 21:05
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Where Question
I am sorry guys I should be more specific. I have a form I'll call it a
'search' form it has several variable edit fields where the user can enter
their specific search criteria. If the user does not enter anything in one of
the searchable fields then I have the null variable changed to '%'. So if the
user left the Fname field empty and entered RAND in the Lname field my where
clause would look like this: WHERE Fname like '%' and Lname like 'RAND%' my
goal was to try and pull up all rows that had RAND?????????????. I have a
feeling that I am approaching this all wrong. What I am trying to do is if the
user is not sure of the correct spelling they can enter the first 3 or 4 letter
and Rbase will take it from there. If I could build the WHERE clause on the
fly that would be cool but not sure how I would go about doing that.
Hope that clears things up a little.
Thanks
Gary Randall
From: [email protected]
Sent: Monday, May 17, 2010 11:40 AM
To: RBASE-L Mailing List <mailto:[email protected]>
Subject: [RBASE-L] - Re: Where Question
Gary,
I may be misunderstanding your intention, however the where clause :
WHERE lname LIKE '%'
Is bascially the same as :
WHERE lname IS NOT NULL
If you want to return all rows in the table regardless of the LNAME column,
simply
EDIT USING FORM1
(With no where clause)
However, if your example was a more simple example of your actual code, then
this
may not work. You would need to be more specific on why and how your are using
the LIKE '%'.
-Bob
----- Original Message -----
From: "Gary Randall" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Monday, May 17, 2010 1:26:48 PM GMT -06:00 US/Canada Central
Subject: [RBASE-L] - Where Question
Hello All,
I am running into a issue that I haven't had before and I am out of ideals, I
am hoping someone could help me see what I am sure is right in front of me.
Here is a where clause I am using:
EDIT USING form1 WHERE lname LIKE '%'
If there is a row where the lname column is empty this where clause will skip
it. I have tried CONTAIN and it behaves the same. Is there a way to make the
where clause return all the rows even the ones that have no data in the lname
column?
Thanks
Gary Randall
