Parameterized queries

rbuser

Are parameterized queries supported in the end-user reports? We want
to prompt a user for a date, USERDATE, and then generate sql similar to
the following:

( select
sum(aph_01_30.balance)
from
bravo_dba.ap_inv_header aph_01_30
where
aph_01_30.supplier_id = aph.supplier_id and
aph_01_30.due_date >= :USERDATE and
aph_01_30.due_date <= :USERDATE + 30
) balance_01_30,
( select
sum(aph_31_60.balance)
from
bravo_dba.ap_inv_header aph_31_60
where
aph_31_60.supplier_id = aph.supplier_id and
aph_31_60.due_date >= :USERDATE + 31 and
aph_31_60.due_date <= :USERDATE + 60
) balance_31_60,

Thanks

digitalmetaphors

You can create autosearch fields for an end user report and then create the
SQL as you have described it using a combination of end user SQL and
autosearch fields. Here is an example that extracts the SQL object from the
report and updates its WHERE clause, similar to what is shown in the main
autosearch demos if the RBuilder\Demos\Autosearch directory.
http://www.digital-metaphors.com/tips/EditSQLAndSearch.zip


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim Poe wrote:


Hi,

I'm asking the wrong question. We are using a view, not a table, to
feed the data to the report. We need to prompt the user for a date
value, set a variable in a database stored procedure with the value,
and then query the view. Is there an event we can use to call the
stored procedure after the user has entered the search criteria and
before RB executes the query? Would CloseQuery on the search criteria
dialog work?

digitalmetaphors

Can you see views as tables in DADE when you create a dataview? Not all DADE
plugins return views as tables. To call a stored procedure, you may be able
to use the timing of the BeforeGenerateSQL event to be sure the SQL hasn't
been sent to the server, as your view is relying on the stored proc being
called before the dataset is opened, correct? Here is an example using
Delphi code that uses this event and shows how to assign the event handler
for an end user app:
http://www.digital-metaphors.com/tips/EndUser_OnBeforeGenerateSQL.zip


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Hi Jim,

Thanks for the ideas. We used OnAutoSearchDialogClose.