Re: case statement in where
From: quez <rmartin.martin_at_gmail.com>
Date: Wed, 14 Oct 2009 10:03:33 -0700 (PDT)
Message-ID: <15fc00a0-5ed4-4f93-9544-a32eeed91b59_at_j4g2000yqa.googlegroups.com>
On Oct 14, 11:47 am, 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
> : 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 ??
Date: Wed, 14 Oct 2009 10:03:33 -0700 (PDT)
Message-ID: <15fc00a0-5ed4-4f93-9544-a32eeed91b59_at_j4g2000yqa.googlegroups.com>
On Oct 14, 11:47 am, 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
> : 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 ??
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. Received on Wed Oct 14 2009 - 12:03:33 CDT