Re: case statement in where

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 14 Oct 2009 08:47:40 -0700
Message-ID: <4ad5f29c$1_at_news.victoria.tc.ca>

quez (rmartin.martin_at_gmail.com) wrote:
: 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.

you could simply write that as

	SELECT .... 
	FROM table_name 
	WHERE 
	( :p_date IS NOT NULL and :p_type IS NOT NULL and
	  the_date = :p_date AND the_type = :p_type
	)
	OR
	(  :p_date IS NULL AND :p_type IS NOT NULL and
	   the_type = :p_type
	)
	-- what about :p_date IS NOT NULL AND :p_type is null ??

Received on Wed Oct 14 2009 - 10:47:40 CDT