OraFAQ Forum: SQL & PL/SQL » Optional parameter in Where clause

Home » SQL & PL/SQL » SQL & PL/SQL » Optional parameter in Where clause (11g, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Optional parameter in Where clause [message #687338]

Fri, 24 February 2023 17:54

RM33
Messages: 11
Registered: December 2013
Location: New York City

Junior Member

I am using 11g

Imagine this SQL

Select * From Customers Where State = "Texas"

Suppose the user wants the option to filter the data further. Maybe they don't want every customer from the state of Texas. They want to narrow down the data by city. So the new SQL will look like

Select * From Customers Where State = "Texas" and City = < optional parameter here >

Some users want the whole state. Others want state and city. So I need to pass two parameters. One mandatory the other optional for the above SQL.

How do I do this.

Report message to a moderator

Re: Optional parameter in Where clause [message #687339 is a reply to message #687338]

Sat, 25 February 2023 00:29

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As I think "Texas" is not fixed but comes from a parameter this can be:

Select * From Customers Where State = :p1 and City = nvl(:p2,' ')

or

Select * From Customers Where State = :p1 and lnnvl(City != :p2)

Queries are slightly different: the results are not the same ones if "City" can be NULL.

Report message to a moderator

Re: Optional parameter in Where clause [message #687340 is a reply to message #687339]

Sat, 25 February 2023 09:25

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel,

None of it will work:

Code: [Select all] [Show/ hide]

SQL> variable p1 varchar2(20)
SQL> variable p2 varchar2(20)
SQL> -- This should return all rows where state is Texas, but it doesn't
SQL> exec :p1 := 'Texas'

PL/SQL procedure successfully completed.

SQL> with customers as (
  2                     select 'Texas' state,'San Antonio' city from dual union all
  3                     select 'Texas',null from dual
  4                    )
  5  select  *
  6    from  customers
  7    where state = :p1
  8      and city = nvl(:p2,' ')
  9  /

no rows selected

SQL> -- This should return rows where state is Texas and city is San Antonio, but it doesn't
SQL> exec :p2 := 'San Antonio'

PL/SQL procedure successfully completed.

SQL> with customers as (
  2                     select 'Texas' state,'San Antonio' city from dual union all
  3                     select 'Texas',null from dual
  4                    )
  5  select  *
  6    from  customers
  7    where :p1 = 'Texas'
  8      and lnnvl(city != :p2)
  9  /

STATE CITY
----- -----------
Texas San Antonio
Texas

SQL> -- This should return no rows since there is no city New York in state of Texas, but it doesn't
SQL> exec :p2 := 'New York'

PL/SQL procedure successfully completed.

SQL> with customers as (
  2                     select 'Texas' state,'San Antonio' city from dual union all
  3                     select 'Texas',null from dual
  4                    )
  5  select  *
  6    from  customers
  7    where :p1 = 'Texas'
  8      and lnnvl(city != :p2)
  9  /

STATE CITY
----- -----------
Texas

SQL>

One solution would be:

Code: [Select all] [Show/ hide]

SQL> variable p1 varchar2(20)
SQL> variable p2 varchar2(20)
SQL> -- This returns all rows where state is Texas
SQL> exec :p1 := 'Texas'

PL/SQL procedure successfully completed.

SQL> with customers as (
  2                     select 'Texas' state,'San Antonio' city from dual union all
  3                     select 'Texas',null from dual
  4                    )
  5  select  *
  6    from  customers
  7    where state = :p1
  8      and (
  9              city = :p2
 10           or
 11              :p2 is null
 12          )
 13  /

STATE CITY
----- -----------
Texas San Antonio
Texas

SQL> -- This returns rows where state is Texas and city is San Antonio
SQL> exec :p2 := 'San Antonio'

PL/SQL procedure successfully completed.

SQL> with customers as (
  2                     select 'Texas' state,'San Antonio' city from dual union all
  3                     select 'Texas',null from dual
  4                    )
  5  select  *
  6    from  customers
  7    where :p1 = 'Texas'
  8      and (
  9              city = :p2
 10           or
 11              :p2 is null
 12          )
 13  /

STATE CITY
----- -----------
Texas San Antonio

SQL> -- This should return no rows since there is no city New York in state of Texas
SQL> exec :p2 := 'New York'

PL/SQL procedure successfully completed.

SQL> with customers as (
  2                     select 'Texas' state,'San Antonio' city from dual union all
  3                     select 'Texas',null from dual
  4                    )
  5  select  *
  6    from  customers
  7    where :p1 = 'Texas'
  8      and (
  9              city = :p2
 10           or
 11              :p2 is null
 12          )
 13  /

no rows selected

SQL>

SY.

[Updated on: Sat, 25 February 2023 09:28]

Report message to a moderator

Re: Optional parameter in Where clause [message #687341 is a reply to message #687340]

Sat, 25 February 2023 11:19

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

None of it will work:

Yes, replied to fast without thinking a little bit. /forum/fa/1606/0/

This one should:

Code: [Select all] [Show/ hide]

Select * From Customers Where State = :p1 and nvl(City,' ') = coalesce(:p2,City,' ')

In addition, as a side effect, it allows to get the rows with no cities and only them setting p2 to a space.

Report message to a moderator

Previous Topic:	Cast(Multiset())
Next Topic:	Need Help with Table Type

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 05 23:41:44 CDT 2025