Custom Macro - check SQL

[mrsnow]

Hello,

I'm wondering if it is possible to write a custom macro than could be run prior to the query in Business Objects that checks the SQL for certain criteria: ie -

SELECT
*
FROM
TBL_GENERAL_INFO
WHERE
TBL_GENERAL_INFO.DATE = "14/10/06"
AND TBL_GENERAL_INFO.CITY = "LONDON"

- run the macro to check that the query contains specific date criteria (must have TBL_GENERAL_INFO.DATE = "xx/xx/xx") - if the query does not (mssge box pop up) - then you could edit it prior to submission.

Any tips would be greatly appreciated.

 

[satinsilhouette]

Why would you need to do this, the lov's will take care of that.

Thanks so much!
satinsilhouette

 

[mrsnow]

Our Universe is large and is partioned in many ways. In order to write the most effective and efficient queries, users are urged to include the partion key objects in their queries, so that rather than the query scanning the whole table, it scans just the part with the most relevant data. Many users for what ever reason forget to include these partion key objects putting a great deal of strain on the server, and slowing query run time down for everyone else.

For this reason I want the macro to scan the SQL code so that its possible for the user to check that their query is correct prior to submitting it.

I hope this makes sence!!

 

[BrianTyler]

Is there any way that you can define the objects with a requirement for a prompt.

I was thinking that TBL_GENERAL_INFO.DATE could be defined as an object with a WHERE condition containing a prompt for a date.

Alternatively, create a number of VIEWS of the tables, and do not allow users to query directly against the base tables.


Brian

 

[xlbo]

Have a look at the DataProvider.SQL property

The DataProvider object is a member of the application class so you can loop like:

for each dp in Thisdocument.DataProviders
msgbox dp.SQL
next

You may run this in the Document Before_Refresh event

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question