Home » SQL & PL/SQL » SQL & PL/SQL » conditional where clause question
conditional where clause question [message #188775] Mon, 21 August 2006 10:52 Go to next message
rbrickne
Messages: 3
Registered: August 2006
Junior Member
My server OS is Windows Server 2003 Standard Edition

My DB is Oracle9i Release 9.2.0.1.0

I am requesting guidance on the best way to proceed with altering several select statements in a stored procedure so that they operate conditionally. I have populated a variable, v_status (CHAR) with either a 'Y' or 'N' value as my condition trigger. Here's a simplified sample of one of the select statement's original code:

[code - original]
Select Key_id from Product_plan
where comit_flag = 'Y'
and Pokemon = 'COOL'
and key_id >0
and capacity <4000;

I want to make the 'and key_id >0' conditional based upon the value of the v_status variable.

[code - doesn't work]
SELECT Key_id FROM Product_plan
WHERE comit_flag = 'Y'
AND Pokemon = 'COOL'
AND
IF v_status = 'N'
THEN key_id >0;
ELSE key_id in (select key_id from Product_plan where key_id * -1 in (select key_id from Product_plan where pokemon_clause = 'Y'));
END IF;
AND capacity <4000;

Would this ever work? What am I missing?

Alternatively, Every time that there is a select statement which needs this conditional where clause, I could create an If/THEN/ELSE/END IF statement and copy the entire select statements in the THEN and ELSE sections, one with key_id >0 and the other selection criteria. This seems a bit ugly, but if it's the only way...

I also tried changing v_status to a VARCHAR(130) and populating it with 'and key_id >0' or 'key_id in (select key_id from Product_plan where key_id * -1 in (select key_id from Product_plan where pokemon_clause = 'Y'))' but I couldn't get the where clause to read and process the v_status value.

Any ideas?

Thanks!

[Updated on: Mon, 21 August 2006 11:32]

Report message to a moderator

Re: conditional where clause question [message #188779 is a reply to message #188775] Mon, 21 August 2006 11:57 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You could do something like:

select key_id
  from product_plan
 where comit_flag = 'Y'
   and pokemon = 'COOL'
   and (   (    v_status = 'N'
            and key_id > 0)
        or (    v_status = 'Y'
            and key_id in (select key_id
                             from product_plan
                            where key_id * -1 in (select key_id
                                                    from product_plan
                                                   where pokemon_clause = 'Y')))
       )
   and capacity < 4000;


This approach though has the potential of causing the CBO to generate sub-optimal execution plans (not sure if key_id factors in the execution plan here or not).

Alternatively, you can look at dynamic SQL, where you build the WHERE clause conditionally as needed.

Also, I would strongly suggest updating to 9.2.0.7 or later.

[Updated on: Mon, 21 August 2006 11:57]

Report message to a moderator

Re: conditional where clause question [message #188812 is a reply to message #188775] Mon, 21 August 2006 16:01 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Sql looks suspiciously circuitous.

Can you give table structure , sample data and expected output ?

Srini
Re: conditional where clause question [message #188813 is a reply to message #188779] Mon, 21 August 2006 16:07 Go to previous message
rbrickne
Messages: 3
Registered: August 2006
Junior Member
Todd's solution did the trick. I will need to study it more closely as I had overlooked encapsulation as an option, DOOOH!

For anyone who was confused by my logic, the second condition is basically pulling negative key_id's, where they exist (because they might not), that have a matching positive key_id with a POKEMON_CLAUSE field of 'Y'. The condition might have been written better, but I'm learning.

The solution is the insight that I needed. I should be able to apply the solution's principle to other stored procedures and eliminate many duplicates.

As for the DB upgrade; I must follow corporate standards. But I'll put a bug in someone's ear.

Thanks!
Rob
Previous Topic: Getting LONG columns out of Oracle 8i and into Access (eek!)
Next Topic: Retreiving domain name from SID?
Goto Forum:
  


Current Time: Thu Dec 08 16:32:07 CST 2016

Total time taken to generate the page: 0.10978 seconds