Function in SQL Developer

trying to create a function that if there is a value then I want to return 'X' but if no value I have to create an empty string  with a space kinda like RPAD(' ',1,' ') or something like that

create or replace FUNCTION CHECK_SOLE_PROPRIETOR(polNbr IN VARCHAR2, cov_effdt IN DATE)
   RETURN NUMBER
IS
   v_EndorseID NUMBER;
BEGIN
  --ENDORSE_ID is a NUMBER when there is No value it looks like the pic below      
   SELECT ENDORSE_ID into v_EndorseID from ENDORSE where POLNBR = polNbr AND COV_EFFDT= cov_effdt AND ENDORSE_CD_NBR = '290307';
   RETURN v_EndorseID;
   
EXCEPTION
WHEN NO_DATA_FOUND THEN
   RETURN 0;
WHEN VALUE_ERROR THEN
   RETURN 0;
WHEN OTHERS THEN
   RETURN 0;
END CHECK_SOLE_PROPRIETOR;

When there is a value from the Select statement being run by itself with variables set that I know have a EndorseID it returns but if there is no Value it doesn't have any value displayed so I don't know what Im doing?

I would accept returning varchars 2, I don't care, but I need to evaluate the empty value so I can set its value to ' ' or 'X' if there is a return value.
 Now, when I know there is a value that should be returned it is returning 0 e  verytime regardless now...Function call with code in questionSelect statement with same variables
jknj72Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
This looks like a contradiction to me: "create an empty string  with a space kinda like RPAD(' ',1,' ')".  I would say that it is easy to have a PL\SQL function return: "an empty string" *OR* "a string with a space kinda like RPAD(' ',1,' ')", but those are two different things.  One is an empty string, the other is a non-empty string that contains a space (but no visible characters).

The easy way in SQL or PL\SQL to return something when a query finds no value in a particular column is with Oracle's "NVL" operator.  That allows you to substitute any value you like when Oracle finds a null (or empty value).  Here is an example (to return a zero, instead of a null)
RETURN nvl(v_EndorseID,0);

Your function currently returns a "number", so that limits what you can return from this function, at least as it is now.  If you have the flexibility to modify the function to return a "varchar2' value instead, then you could have it return a string with a single space (or a question mark, or something else if you like) when the query finds no match.

I would also suggest that the exception-handling section of this function currently contains some wasted lines:

EXCEPTION
 WHEN NO_DATA_FOUND THEN
    RETURN 0;
 WHEN VALUE_ERROR THEN
    RETURN 0;
 WHEN OTHERS THEN
    RETURN 0;

That can (and I would say should) be simply this:
EXCEPTION
 WHEN OTHERS THEN
    RETURN 0;

That is, unless you change it to something more like this, so you can tell what the problem actually was:

EXCEPTION
 WHEN NO_DATA_FOUND THEN
    RETURN -2;
 WHEN VALUE_ERROR THEN
    RETURN -1;
 WHEN OTHERS THEN
    RETURN 0;
jknj72Author Commented:
I actually did try nvl(v_EndorseID,0); and it returns me a 0 for everytime I call the function.

create or replace FUNCTION CHECK_SOLE_PROPRIETOR(polNbr IN VARCHAR2, cov_effdt IN DATE)
   RETURN NUMBER
IS
   v_EndorseID NUMBER;
BEGIN
                           
   SELECT ENDORSE_ID into v_EndorseID from ENDORSE where POLNBR = polNbr AND COV_EFFDT= cov_effdt AND ENDORSE_CD_NBR = '290307';
   RETURN nvl(v_EndorseID,0);  
   
EXCEPTION
  WHEN OTHERS THEN
     RETURN 0;
END CHECK_SOLE_PROPRIETOR;
Mark GeerlingsDatabase AdministratorCommented:
What does this query return:

SELECT ENDORSE_ID
from ENDORSE
where POLNBR = &polNbr
AND COV_EFFDT= '&cov_effdt'
AND ENDORSE_CD_NBR = '290307';
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark GeerlingsDatabase AdministratorCommented:
Please test that query outside of PL\SQL (like in SQL Developer, or in SQL*Plus).  Substitute actual values for the "&polNbr" and "&cov_effdt" parameters.  This will help you determine if your problem is actually a data problem or a PL\SQL problem.  I suspect you have a data problem of some kind, either you aren't passing valid values for: polNbr and cov_effdt, that match actual records in your table.  Or, maybe the actual values in your COV_EFFDT column include a non-midnight time-of-day component, and you are passing date values in that do *NOT* include the time-of-day.

Try this command:
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Then run this query and post the results here:
SELECT ENDORSE_ID, POLNBR, COV_EFFDT
 from ENDORSE
 where ENDORSE_CD_NBR = '290307';

And, after running this query (and copying the results here please) close the window that you ran this in, and log in again to set your nls_date_format back to the usual value in your system.  Or, you could do another command more like one of these (if your usual date format is like one of these):

alter session set nls_date_format='MM/DD/YYYY';

alter session set nls_date_format='DD-MON-YY';
slightwv (䄆 Netminder) Commented:
I thought I had trained you to use strings as strings and dates as dates!!!!!!!  DO NOT RELY ON IMPLICIT CONVERSIONS!!!

I would pass in a varchar2 and convert it to a date with to_date inside the code.

That said:
Oracle objects are case-insensitive so POLNBR  and polNbr mean the same thing.

Try to not use parameters that are the same name as columns in the tables you are working with.  At times I've seen Oracle get confused when trying to figure out what is a variable and what is a column.

With a column called POLNBR  and a parameter with the same name Is Oracle to compare the column to itself, the variable/parameter to itself or one to the other?

I add p_ to parameters and v_ to variables.  The convention you choose it up to you...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
I cant believe what just happened. I read the last part of your answer where you said

>>Try to not use parameters that are the same name as columns in the tables you are working with.  At times I've seen Oracle get confused when trying to figure out what is a variable and what is a column.
>>

Not believing this could be the case, I tried it and changed my functions parameters with p_ and it returns different and I think correct values.

Thanks Slight

PS - I thought I was doing the Dates correctly, Comparing Dates to Dates?  I wasn't passing in a VARCHAR2, I passed in a DATE and didn't think I should have to do any kind of conversion?
jknj72Author Commented:
awesome
slightwv (䄆 Netminder) Commented:
>> thought I was doing the Dates correctly, Comparing Dates to Dates?

The select you posted in the image was a string.

The procedure looks like it does things right.  I typically don't use DATE for input parameters because developers have trouble with dates from time to time.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.