Setting AutoSearch criteria after Editing SQL. (Again)

rbuser

Hi,
I'm asking the same question again.(It seems my previous post has been
deleted) How can End-User use AutoSearch criterias after he manually edited
SQL?
Yes, I downloaded "EditSQLAndSearch" program which was supposed to explain
this to me, but unfortunately in my case it was not enough.

You see, the main reason why I need to use workaround methods is because the
reports that are going to be created using ReportBuilder will have VERY
large and complicated SQL queries. It is normal that queries up to 50(!)
lines lenght are not created using your SQL Designer, but rather using
professional tools for SQL creation like TOAD ot TORA. Thus it is only
natural to create complex SQL query and copy-paste it to the report Designer
in the Data Tab. Certainly, another end-user that will use this report with
complex SQL wants to get only the desired data - not the half of database
data for one report. This is where I, as a software developer should help
the end user, providing him with the means to filter resulting data
(received from SQL) thus obtaining only necessary information. But, I don't
know how can I do it, because everything is connected with SQL
representation in the Report Builder.
The example that you provided me with - "EditSQLAndSearch". It uses the fact
that an SQL query has already been inputted using standard means and you
just change several properties of the SQL object and that is that. But what
do I need to do in a case when user makes copy-paste with another - much
more complex SQL query and still wants to be able to filter the reports
using provided AutoSearch means?

I would appreciate it very much i you were able to answer this to me.

Thanks in advance,
MB

digitalmetaphors

It should be possible. You'll have to parse the SQL text looking for the
WHERE clause and update it manually in your code. Then you'll have to have
AutosearchFields created by the end user, so a RAP pass through function is
needed to do this from RAP. I started working on an example last week but
didn't get a chance to finish it. I'll try to get something together for you
this week.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Thank you,
I'm looking forward for an exampe.

Also I would like to note that SQL parsing is a very hard task.
Let's see about Oracle conventions. First of all WHERE clause might not be
presented in the query at all. Then we would have to include it on our own.
But how do we know where to put it? Also consider select with several
subqueries some of which may have and may not have WHERE clause.
Also after WHERE clause might come anythig: ORDER BY, CONNECT BY, SORT BY,
GROUP BY, UNION, INTERSECT, MINUS and so on...

Be careful about this because parsing SQL MANUALLY may not be such a good
idea after all.

digitalmetaphors

RB was never intended to work this way. Either use the query tools to
generate SQL or enter it by hand and lose the query tools support as well as
lose autosearch support. Yes, the workaround will get very complicated if
parsing the SQL, and any SQL that could be entered by your users. I was
thinking that if you want to edit the SQL and use autosearch, then it is
possible, but you'll have to code for it since RB doesn't natively support
it.


Cheers,

Jim Bennett
Digital Metaphors

digitalmetaphors

Check out this updated example. It creates autosearch fields even after the
user edits the SQL manually. You'll have to add more code to handle the SQL
parsing, but this can be accomplished.

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


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Ok, thanks for the help.

rbuser

I can?t download from this link.

Bernd

digitalmetaphors

Sorry, the website was down a little while last night. Try it again.


Cheers,

Jim Bennett
Digital Metaphors