Add WHERE clause at runtime

rbuser

Hello,

I'm looking for a sample to modify the SQL statement at runtime without
using the SQL.Text property.

I found this answer in this forum :

----------------------------------------------------------------------------
--
Aftert you get the values from the dialog, you'll need to extract hte SQL
object and add criteria fields via the TdaSQL methods. Here is an example
whihc extracts the sql object and adds search criteria. This way you won't
lose the query designer tabs. You'll only lose those if you edit the SQL as
text, which we aren't going to do in an example I'm going to email to you,
since our FTP server is down this morning.


Cheers,

Jim Bennett
Digital Metaphors

digitalmetaphors

Sure, here is an example which lets you modify the TdaSQL object at runtime,
such as search criteria to build the where clause. The reason for
manipulating the TdaSQL object using its methods and properties, other than
setting the SQLText directly, is that you won't lose the query tools
support.

http://www.digital-metaphors.com/tips/ExtractSQLObject.zip


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Sorry, my question was not clear.


I have 30 dynamic views of datas in my application.
Each view is dynamicly created at runtime.

I use strings to store each line of my QueryView.SQL property.

Txt_SQL := 'select * from ventes'


User can manually apply filters. Each time, filters give a different WHERE
statement.

Sample :

fist time filter, there is no WHERE line.

The user apply a filter to the order number :
Txt_WHERE := 'WHERE code_vente = 1'

On the same view, 2 minutes later, user change the filter. He remove the
filter for the order number and decide to add a filter for the customer name
:
Txt_WHERE := 'WHERE nom_client = 'DUPOND''

etc.

So, as you see, the string txt_WHERE could be different.

I want to pass this txt_WHERE into my report.
So, my report will exactly print the same data as my grid.

I can not use Criteria ... Unless you you have a function to convert SQL
statement to criteria ...

I really need to dynamicly change the SQL string of my report.
I find no good answer in this forum. Each time this question is asked, you
answer the same thing : "See
http://www.digital-metaphors.com/tips/ExtractSQLObject.zip"
This sample only show me how to extract the SQL statement. Not how to modify
it.
I can not use EditSQLAsText := True without destroying reports :-(((


Any idear ?

Thanks.

digitalmetaphors

You can add and remove criteria objects on the TdaSQL object. This is how
you can control the generated WHERE clause. If you are adding the SQL text
when you create the dataview, then you must edit the SQL text at runtime.
Set EditSQLAsText to true on the TdaSQL object and set the SQLText property.
If this breaks the reports, then you'll have to write a text parser to turn
a WHERE text string into a criteria object that you then add to the SQL
object as shown in the example. This way you can continue to use the query
tools and visual query linking.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Why ReportBuilder does not offer this function ?
Many reports designers offer the possibility to change the SQL command
without problem.
Is it so hard ?

digitalmetaphors

I'll make feature request for a new EditSQLAsTextLinking boolean property.
I can see this as being useful if any the dataviews have their SQL edited
and then you want to link them. As long as the SQL is ordered in the
details to match the masters, then it will work. Thanks for the suggestion.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Has there been any movement on this issue?

I have a very similar situation where I need to add a where clause at
report preview/print time. The clause is provided to me in text form
and may contain sub selects with an "in" clause. I too have the
common issue of making the report match the Grid.

The text SQL could be simply "customer.id = 1234 " or it could be
"customer.number in (select number from customer tab1 join city tab2
on tab1.citynumber = tab2.number where tab2.name like 'A%')"

I am using RB7.02, D6, daAsta with report explorer.

I can take responsibility for ensuring that the added sql is valid
(and to which dataviews to add it to in the case of master/detail - it
is always relative to the master).

If there has been no progress would you suggest I modify TdaSQL to
support the appending of an additional where clause or do you have an
alternate suggestion?

Many thanks,

digitalmetaphors

You can use edited SQL text in dataviews and link them at runtime. Here is a
simple example of performing the linking after a report is loaded.
http://www.digital-metaphors.com/tips/LinkEditSQLDataviews.zip

In the end user environemnt, use the Report.Template.OnLoadEnd event to
perform this operation. Do you know at runtime which dataview is the msater
and which is the detail, and the linking key field names? If so, then use
the technique used in this latest demo to find the dataviews:
http://www.digital-metaphors.com/tips/ExtractSQLObject.zip


Cheers,

Jim Bennett
Digital Metaphors

rbuser

The second one looks to be the ticket for me. The part I hadn't
twigged to was the "ContainsSubSelect" property that allows the
"inlist" criteria to pass through the subselect clause.

I will try it out. Many thanks.