Home » SQL & PL/SQL » SQL & PL/SQL » NOT NULL check in function (Oracle 9i)
NOT NULL check in function [message #315635] Tue, 22 April 2008 04:50 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I am creating a function where I am getting a value in the variable v_nextv_param which can be NULL or a number.

CREATE OR REPLACE FUNCTION ATLASADMIN_REACH.fn_visit_cnt(
           v_user_id IN VARCHAR2,
           v_visits_escParam IN VARCHAR2,
           v_nextv_escParam IN VARCHAR2)
 RETURN NUMBER
 IS v_doc_visit_tot NUMBER;
    v_anhfsc_roleid NUMBER(9) := '181';
BEGIN
  SELECT COUNT(doctor_id) INTO v_doc_visit_tot
    FROM(
      SELECT b.user_id
     FROM adm_profile_master a, adm_users b
    WHERE a.user_Id=b.user_id
    AND a.IS_ACTIVE=1
    AND B.IS_ACTIVE=1
    AND a.role_id = '181'
  START WITH a.user_Id= v_user_id 
CONNECT BY PRIOR a.user_id = a.reporting_to)
    AND doc.doctor_id=bpt.doctor_id
    AND doc.doctor_flag=1
    AND bpt.ANHFSC_ID=usr.USER_ID
    AND SYSDATE >= (bpt.visit_Date + v_visits_escParam)
    AND SYSDATE < (bpt.visit_Date + v_nextv_escParam)
  GROUP BY bpt.doctor_id,bpt.anhfsc_id,
           usr.first_name,usr.last_name,doc.doctor_name,
           doc.doctor_mobile,doc.DOCTOR_CL_PHONE,bpt.doctor_id
  ORDER BY bpt.anhfsc_id);
RETURN (v_doc_visit_tot);
END fn_visit_cnt; 


I have to make a change in the function to limit the DATE range when the number is null.
AND SYSDATE >= (bpt.visit_Date + v_visits_escParam)
    AND SYSDATE < (bpt.visit_Date + v_nextv_escParam)

It works fine if v_nextv_escParam is NOT NULL.

I am not getting a way to write in the WHERE conditions to limit the UPPER date if "v_nextv_escParam" IS NULL as in that case I won't need the second line for 'less than' check.

I can simply write two SELECT statements ...one carrying two lines and other only one line.
But is there any other way where I can write the condition in only one SELECT clause.
Please advice me a way to do it.



Thanks,
Mahi

[Updated on: Tue, 22 April 2008 05:04]

Report message to a moderator

Re: NOT NULL check in function [message #315645 is a reply to message #315635] Tue, 22 April 2008 05:18 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could change the statement to be always true when v_nextv_escParam is null, like :

 AND SYSDATE >= (bpt.visit_Date + v_visits_escParam)
 AND SYSDATE < nvl( v_nextv_escParam,
                    ( bpt.visit_Date + v_nextv_escParam ),
                    sysdate + 1)


that way, when v_nextv_escParam is null, then the check sysdate < sysdate + 1 is always true.
Re: NOT NULL check in function [message #315649 is a reply to message #315645] Tue, 22 April 2008 05:28 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Thomas,
If v_nextv_escParam IS NULL then I don't need the second line.
Your NVL function is taking 3 arguements. Does it work that way?

What if I use DECODE similar to what you suggested.
Is this correct way to do the same task?

AND SYSDATE >= (bpt.visit_Date + v_visits_escParam)
AND SYSDATE < DECODE(v_nextv_escParam, NULL, (sysdate + 1),
                    ( bpt.visit_Date + v_nextv_escParam ))


Thanks,
Mahi
Re: NOT NULL check in function [message #315652 is a reply to message #315649] Tue, 22 April 2008 05:45 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah, yes. I mixed up nvl and decode somehow. Wink

Or maybe nvl and nvl2

The decode should work, though.

You could also use an or like

 AND SYSDATE >= (bpt.visit_Date + v_visits_escParam
 AND (     SYSDATE < (bpt.visit_Date + v_nextv_escParam) 
        or v_nextv_escParam is null)


which might be readable for someone else.


[Updated on: Tue, 22 April 2008 05:49]

Report message to a moderator

Re: NOT NULL check in function [message #315660 is a reply to message #315652] Tue, 22 April 2008 06:07 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks for your help Thomas. Smile
Re: NOT NULL check in function [message #315673 is a reply to message #315635] Tue, 22 April 2008 06:35 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Just to add to the many valid options here, you should also be able to do something along the lines of:

sysdate < nvl(bpt.visit_Date + v_nextv_escParam, sysdate + 1)
Re: NOT NULL check in function [message #315692 is a reply to message #315635] Tue, 22 April 2008 07:48 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
virmahi wrote on Tue, 22 April 2008 05:50

v_anhfsc_roleid NUMBER(9) := '181';



Makes no sense.
Re: NOT NULL check in function [message #315693 is a reply to message #315635] Tue, 22 April 2008 07:52 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Upon further review, I see more invalid coding.

virmahi wrote on Tue, 22 April 2008 05:50
           v_visits_escParam IN VARCHAR2,
           v_nextv_escParam IN VARCHAR2)
.
.
.
    AND SYSDATE >= (bpt.visit_Date + v_visits_escParam)
    AND SYSDATE < (bpt.visit_Date + v_nextv_escParam)



Re: NOT NULL check in function [message #315701 is a reply to message #315693] Tue, 22 April 2008 08:22 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks Joy,
I have removed the code
v_anhfsc_roleid NUMBER(9) := '181';


and changed the datatype from varchar2 to number for the following :

 v_visits_escParam NUMBER,
           v_nextv_escParam NUMBER)
.
.
.
    AND SYSDATE >= (bpt.visit_Date + v_visits_escParam)
    AND SYSDATE < (bpt.visit_Date + v_nextv_escParam)



Mahi
Re: NOT NULL check in function [message #315703 is a reply to message #315701] Tue, 22 April 2008 08:27 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Excellent. Proper use of data types will prevent headaches.

I noted in the first message that the declaration and setting of v_anhfsc_roleid made no sense not because it wasn't being used, but because you declared at as a number and were putting a character string as it's value.
Previous Topic: group by range
Next Topic: DBLINK and materialized views
Goto Forum:
  


Current Time: Fri Dec 09 01:50:53 CST 2016

Total time taken to generate the page: 0.05228 seconds