|
Big
Planet wrote
>-I
am writing this proc does a search in database based on these in
parameters and returns a ref cursor . Now one more of these in
parameters can be null and my query should return data neglecting null
parameters . So I use nvl in the query as shown below
Big Planet, small
print,,, coincidence??
Hi All ,
I am writing this proc does a search in
database based on these in parameters and returns a ref cursor .
Now one more of these in parameters can be null and my query should return
data neglecting null parameters . So I use nvl in the query as shown below
.
Now my problem is , the query doesnt use index
available on table since i m using a function .
Is there any way I can rewrite this query so
that it meets my requirements and use the index . I have other option is to
create a dynamic sql based on in parameters .
TIA for any help .
-BigP
PROCEDURE get_alertlog (
p_loginid varchar2 ,
p_startdate date,
p_firstname varchar2 ,
p_enddate date ,
p_status out number,
p_msg out varchar2,
p_refcursor out c_refcursor )
Begin
open p_refcursor for
select logpin , logtype , logaction , logdate , memberpin
FROM mem
WHERE loginid
=nvl(p_loginid , loginid )
AND logdate
between nvl(p_startdate,to_date('1-jan-1900','dd-mon-rrrr') )
And firstname = nvl( p_firstname , firstname
)
and nvl(p_enddate , sysdate ) ;
End
;
|
