PL/SQL optional where

Question

Hey, I have this query in PL/SQL:

--Ver todos los atributos de las OL de una OS.
SELECT attr.swspattrdataid attr_data_id,
       att.swname attribute_swname,
       attr.swvalue attr_data_swvalue
  FROM sw_sp_attr_data attr, 
       sw_sp_attribute att
 WHERE swobjectid IN (SELECT swsporderlineid
                        FROM sw_sp_order_line
                       WHERE swsporderid = 21444963 --Orden 
                       **AND swsporderlineid = a_number**
                     );
   AND att.swspattributeid = attr.swspattributeid
 --AND att.swname LIKE '%%'                          --Filtrar por nombre

I need to have the AND filter between ** to be optional, so no matter if I put a number there the query runs OK, is this posible?

Thanks!

score 7 · Accepted Answer · answered May 04 '11 at 12:56

7

Declare the parameter with a default value of NULL. So in case it is not passed to the procedure it will automatically be null.

Then change the condition to:

AND (a_number IS NULL OR swsporderlineid = a_number)

answered May 04 '11 at 12:56

The other possibility is `AND NVL(a_number, swsporderlineid) = swsporderlineid`. – angus May 04 '11 at 14:17
@angus: So long as `swsporderlineid` is never null (it's probably same to assume this). – Adam Paynter May 04 '11 at 14:18
@Adam Paynter: You are right. When `a_number is null and swsporderlineid is null`, a_horse_with_no_name's (and yours) condition returns true and mine null. – angus May 05 '11 at 19:49

score 4 · Answer 2 · answered May 04 '11 at 12:56

You can replace the swsporderlineid = a_number clause with this OR clause:

   WHERE      swobjectid IN (SELECT swsporderlineid
                             FROM sw_sp_order_line
                             WHERE swsporderid = 21444963 --Orden 
                               AND (swsporderlineid = a_number OR a_number IS NULL));

Therefore, if a_number is null, the second line evaluates to true for all records, allowing the query to continue.

PL/SQL optional where

2 Answers2

Linked

Related