AutoSearch Field / SQL

rbuser

I want to get to an SQL statement that is in effect:

where MyDateField>'10/01/2003' OR MyMileageField>=5000

The key here being the OR in the where clause. In using AutoSearch, for
each field, I end up with an SQL statement that is:

where MyDateField>'10/01/2003' AND MyMileageField>=5000

Most of the time, I would want the AND but there are cases where I need the
OR. I am sure I am overlooking something. Please point me in the right
directions.

Thanks,
Bob

nicocizik

Hi Bob,

You should be able to use an OR statement with your autosearch criteria.
This however will show up as an "and" in the Autosearch dialog that pops up
before the report loads. This is a limitation of the Autosearch dialog even
though the OR will be executed. To correct this you will need to create a
custom Autosearch dialog like the example located in the \RBuilder\Demos\5.
AutoSearch\4. Custom AutoSearch Dialog.

--

rbuser

I don't use the auto search dialog. I prompt for everything in my own
dialog and then pass the auto search criteria to the report.

However, I don't see how to create the OR using the
CreateAutoSearchCriteria. The function takes the TableName, FieldName,
Operator, and the Mandatory Flag as you know. I create the two auto search
fields that I described before. However, they are created with the AND in
the SQL statement. Can you please tell me how to create them with the OR?

For example:
CreateAutoSearchCriteria(MyTableName, MyDateField, soGreaterThanOrEqualTo,
'10/01/2003');
CreateAutoSearchCriteria(MyTableName, MyMileageField,
soGreaterThanOrEqualTo, 5000);

The two above produce an SQL statement like:
where MyDateField>='10/01/2003' AND MyMileageField>=5000

How can I get the following using autosearch???
where MyDateField>='10/01/2003' OR MyMileageField>=5000

Thanks,
Bob

nicocizik

Hi Bob,

Check out the example in the \RBuilder\Demos\5. AutoSearch\4. Custom
AutoSearch Dialog . This example uses the OnGetAutoSearchValues event to
add SQL to your criteria. This example adds an "AND" rather than an "OR"
but it should get you on the right track on getting the results you need.

--

rbuser

Ok, I see what has to be done.

Thanks,
Bob Tucker

rbuser

The example you referenced uses a local sql query on the form. If I am
going to alter the sql statement of the query on the form, I can think of
many ways to do that. I am refering to using the sql dataset that is
created by Dade in the report. I see no way to use the AND in an sql
statement in RBuilder Dade.

This brings me to this question. I have the complex query built in the data
screen. I can make the report based on a datapipeline to the complex sql.
However, I would want the user to be able to make further joins and
adjustments to the report. Is there a way to make the local (on form) sql
and duplicate it or somehow make it appear as a query in the Data section of
the designer?

I can make the pipeline appear and run the report from that pipeline.
However, the user can't make adjustments at runtime. Otherwise, is there a
way I can create a query to appear in the Dade tab and then just copy the
sql lines from the on-form query to the one created in the data tab of
RBuilder? That would be perfect.

Thanks,
Bob Tucker

nicocizik

Hi Bob,

Check out the example I sent in your later post. It shows you how to
extract the sql object from a dataview in DADE.

--