Dynamic Where Statement

[fmrock]

Is it possible to create a dynamic where statement.

I have a report that runs a query from a Crystal report.

However, sometimes they want to exclude the paramater. Are there any techniques you all use to not include part of your where statement?

 

[SantaMufasa]

I'm not a Crystal Reports aficionado, so does CR have the ability to replace strings as does SQL*Plus? Here's a script that I named "tt_509.sql":

accept my_where prompt "Enter a WHERE clause for this query (or [Enter] for none): "
select * from s_dept &my_where
/

Here are two different invocations of tt_509:

SQL> @tt_509
Enter a WHERE clause for this query (or [Enter] for none): 

        ID NAME                       REGION_ID
---------- ------------------------- ----------
        10 Whatever New Finance               1
        31 Sales                              1
        32 Sales                              2
        33 Sales                              3
        34 Sales                              4
        35 Sales                              5
        41 Operations                         1
        42 Operations                         2
        43 Operations                         3
        44 Operations                         4
        45 Operations                         5
        50 Administration                     1
SQL> @tt_509
Enter a WHERE clause for this query (or [Enter] for none): where rownum <= 5

        ID NAME                       REGION_ID
---------- ------------------------- ----------
        10 Whatever New Finance               1
        31 Sales                              1
        32 Sales                              2
        33 Sales                              3
        34 Sales                              4
SQL>

Let us know if this helps you identify a solution.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[kskid]

What are you trying to accomplish?

Changes to Crystal Report selection criteria will always generate a new sql statement that is sent to Oracle.

Perhaps you should address this in the Business Objects: Crystal Reports 1 Formulas