case statement in where

From: quez <rmartin.martin_at_gmail.com>
Date: Wed, 14 Oct 2009 08:17:20 -0700 (PDT)
Message-ID: <1115f00a-6832-44a4-959d-4f553e6a1a7f_at_a32g2000yqm.googlegroups.com>

Hi, I have a form with 2 fields. one is a date, the other a varchar2. Now a user can either select 1 of the 2 or both of the 2. I want to submit the users' selection as a query. If only 1 filed is selected, then the proper column/field in the db will be compared against that field. If both fields are selected, I need to use an AND statement so that both fields from the database will be compared against what the user submitted.

I was thinking of using a case statement in the where clause, but unsure if this is the best approach.

Eg. SELECT .... FROM table_name WHERE CASE WHEN :p_date IS NOT NULL and :p_type IS NOT NULL

      THEN the_date = :p_date AND the_type = :p_type
      WHEN :p_date IS NULL AND :p_type IS NOT NULL
      THEN the_type = :p_type
     .....etc....

Is there a way to do this without using CASE. I thought about NVL, but I need to assign a value to two different LVALUEs. Please help. Thnx. Received on Wed Oct 14 2009 - 10:17:20 CDT