Re: CASE substitution in WHERE clause

From: Michael McMullen <ganstadba_at_hotmail.com>
Date: Fri, 23 Jul 2004 11:36:43 -0400
Message-ID: <BAY9-DAV12GEJ9yFI4L000811e3@hotmail.com>

Oracle 9.2.0.4
We do alot of the same here but using decode. There's just too many options for the end user to select from so coding all of the possible combinations was too difficult. From what I've read the use of indexes isn't guaranteed but I haven't had a problem yet. Here's an example SELECT status_group, group_data_count, status,data_count,

     sum(1) over (partition by status_group) num_status_recs,
     sum(1) over () num_group_recs FROM
     (SELECT  status_group,
      count(*) over (partition by status_group) group_data_count,
      status,
      count(*) over (partition by status) data_count,
      row_number() over (partition by status_group,status order by
status_group) row_number
     FROM    ipded.ipded_load
     WHERE billing_phone_null =
DECODE(sBTN_IN,'*NULL*',billing_phone_null,sBTN_IN)
     AND  customer_null =
DECODE(UPPER(sCustomer_IN),'*NULL*',customer_null,UPPER(sCustomer_IN))
     AND  crdddst_null =
DECODE(sCRDDDST_IN,'*NULL*',crdddst_null,sCRDDDST_IN)
     AND     tracking_number_null =

DECODE(sOrderNo_IN,'*NULL*',tracking_number_null,sOrderNo_IN))

WHERE row_number = 1
----- Original Message -----
From: "Jesse, Rich" <Rich.Jesse_at_quadtechworld.com> To: <oracle-l_at_freelists.org>
Sent: Friday, July 23, 2004 11:23 AM
Subject: CASE substitution in WHERE clause

> Hey all,
>
> Now that we're up to 9.2.0.5 on HP/UX 11i, I'm trying to make use of =
> some of the new features. Some devs want to be able to select something =
> like this:
>
> SELECT *
> FROM my_warehouse
> WHERE warehouse IN=20
> CASE :in_whse WHEN '00' THEN ''' '',''00''' ELSE :in_whse END
>
> If the parameter supplied is '00', have the select filter based on (' =
> ','00'). If not, filter on what parameter was passed in.
>
> In order to take advantage of indexing, I don't want to apply functions =
> to the filter. Or would it be "better" to use dynamic SQL?
>
> Thoughts anyone?
>
> TIA,
> Rich
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_quadtechworld.com QuadTech, Sussex, WI USA

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jul 23 2004 - 10:38:06 CDT