Re-writing SQl:help needed

From: Vikas Kawatra <VKawatra_at_innoventry.com>
Date: Wed, 22 Aug 2001 09:26:10 -0700
Message-ID: <F001.0037420A.20010822091148@fatcity.com>

sorry for the incorrect "subject"! See below :

-----Original Message-----
Sent: Wednesday, August 22, 2001 9:08 AM To: 'ORACLE-L_at_fatcity.com'

We're trying to re-write some SQL such that the index on columns in the where clause is used.Logically ,the query is as below .But this query does not use the Index .However ,if we re-write it as below(see 2nd query) - the index is used :but the query returns incorrect results .Can you suggest any changes to the original query?

note :
 1)we have a index on cmm_id,cust_id,friendly_session_id,creation_date  2)We want to return rows from the table even if all columns in where clause are NULL

Thanks

vikas

SELECT friendly_session_id,

              cmm_id,
              cust_id,
              hold_amount,
              virtual_balance_amount,
              creation_date,
              session_id
         FROM sals_session
         WHERE ( CMM_ID = '101' or  cmm_id IS NULL )
         AND ( CUST_ID =101 or cust_id IS NULL )
         AND ( FRIENDLY_SESSION_ID = 100 or friendly_session_id IS NULL )
         AND (CREATION_DATE is NULL OR loc_to_utc(creation_date) between

'21-JUN-2001' and '21-AUG-2001');

SELECT friendly_session_id,

              cmm_id,
              cust_id,
              hold_amount,
              virtual_balance_amount,
              creation_date,
              session_id
         FROM sals_session
         WHERE cmm_id = nvl(cmm_id,0)
         AND cust_id = nvl(cust_id,0)
         AND friendly_session_id =nvl(friendly_session_id ,0)
         AND (creation_date is NULL OR loc_to_utc(creation_date) between

'21-JUN-2001' and '21-AUG-2001');

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Vikas Kawatra
  INET: VKawatra_at_innoventry.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Aug 22 2001 - 11:26:10 CDT