Re: SQL statement and Oracle Reports

From: Fred Kuhne <fred_kuhne_at_vonrollisola-usa.com>
Date: Thu, 21 Oct 1999 14:17:29 -0700
Message-ID: <000b8d9b.bbdb9963_at_usw-ex0102-014.remarq.com>


An alternate method:

Rather than giving the user a set list of values for the parameter (as I assume you are doing) do the following:

  1. Make the parameter values a select statement.
  2. In the select statement do something like this: select '%','All' from dual union all select 'option1','Option 1 Description' from dual union all select 'option2','Option 2 Description' from dual;
  3. Set hide first column to yes. Make the default value for the parameter = %
  4. In your sql where clause where columnname like &parametername

As a reminder: However you decide to do it, you have to use 'like' rather than '=' in your where clause for '%' wildcarding.

Fred

In article <19991020221253.28609.00000252_at_ng-fx1.aol.com>, dmlvol_at_aol.com (Dmlvol) wrote:
> Hi all,
> I need your assistance with a SQL statement to be used with
> parameters in
> Oracle reports.
> From our application, I want users to be able to optionally pass
> the report
> bind parameters to restrict the output. If the user elects to
> omit the
> parameter, the report should select all records. I have attempted
> to use both
> NVL and DECODE without success. I hope somebody can clue me in.
> My SQL statement is something like:
> Select * from table where :Event = eventnumber;
> I have tried NVL(:Event, '%') and similar syntax with DECODE
> without success.
> Any ideas??
> David
> David.latham_at_onizuka.af.mil

  • Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!
Received on Thu Oct 21 1999 - 23:17:29 CEST

Original text of this message