Re: case statement in where

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 15 Oct 2009 09:54:19 -0700
Message-ID: <4ad753bb$1_at_news.victoria.tc.ca>

quez (rmartin.martin_at_gmail.com) wrote:
: On Oct 14, 11:47=A0am, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones)
: wrote:
: > quez (rmartin.mar..._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
: > : =A0 =A0 =A0 THEN the_date =3D :p_date AND the_type =3D :p_type
: > : =A0 =A0 =A0 WHEN :p_date IS NULL AND :p_type IS NOT NULL
: > : =A0 =A0 =A0 THEN the_type =3D :p_type
: > : =A0 =A0 =A0.....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
: >
: > =A0 =A0 =A0 =A0 SELECT ....
: > =A0 =A0 =A0 =A0 FROM table_name
: > =A0 =A0 =A0 =A0 WHERE
: > =A0 =A0 =A0 =A0 ( :p_date IS NOT NULL and :p_type IS NOT NULL and
: > =A0 =A0 =A0 =A0 =A0 the_date =3D :p_date AND the_type =3D :p_type
: > =A0 =A0 =A0 =A0 )
: > =A0 =A0 =A0 =A0 OR
: > =A0 =A0 =A0 =A0 ( =A0:p_date IS NULL AND :p_type IS NOT NULL and
: > =A0 =A0 =A0 =A0 =A0 =A0the_type =3D :p_type
: > =A0 =A0 =A0 =A0 )
: > =A0 =A0 =A0 =A0 -- what about :p_date IS NOT NULL AND :p_type is null ??

: Yes, I added the third case as you pointed out (when :p_date is not
: null and :p_type is null).

: I got no results when I ran my tests. The syntax is correct....I'm
: just not getting any output; and I'm certain that my test cases
: should give some outputs. I'm thinking it may be the OR statements
: that joins the separate AND statements are being evaluated ?
: thnx for your response.

Most bugs are caused by a mismatch between what the programmer thinks is happening and what is really happening, and most debugging consists of the programmer trying to find out what it is they misunderstand.

I would run the select with no where clause (maybe restrict it based on rownum) and display all the items used in the where clause and anything else that's useful, and then examine each row and determine why the where clause is filtering out that row.

	select ...your columns ...
	, :p_date ":P_DATE"
	, :p_type ":P_TYPE"
	from table_name
	where rownum < 200 

Displaying the bind variable values helps to confirm they are what you think they should be. You may want to wrap :p_date with to_date to ensure you are seeing the entire value ( to_date(:p_date,'YYYY-MM-DD HH24:MI:SS' )

After examining the data then add each part of the where clause (one section at a time) to see what effect it has and to confirm it selects or filters the rows you are expecting it to select or filter.

I often include the where clause columns in the select list of any complex query (whether I need them or not) precisely so I can easy check the statement later. If they are large then I leave them in but commented out.

I normally write the where clause from most general to most specific. That way I can cut and paste the query and automatically include just a portion of the where clause so I can see more of the data than the query normally shows.

$0.10 Received on Thu Oct 15 2009 - 11:54:19 CDT