Home » SQL & PL/SQL » SQL & PL/SQL » Optional parameter in Where clause (11g, Windows)
Optional parameter in Where clause [message #687338] Fri, 24 February 2023 17:54 Go to next message
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.
Re: Optional parameter in Where clause [message #687339 is a reply to message #687338] Sat, 25 February 2023 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Optional parameter in Where clause [message #687340 is a reply to message #687339] Sat, 25 February 2023 09:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel,

None of it will work:

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:

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 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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:

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.

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


Current Time: Thu Mar 28 17:58:00 CDT 2024