Re: CASE substitution in WHERE clause
- From: "Michael McMullen" <ganstadba@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Jul 2004 11:36:43 -0400
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@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxx QuadTech, Sussex, WI USA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
