Unanswered: Change results in column to display different
I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.
SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC
I am sorry, I should have started off that I am a beginner in Oracle PL\SQL. Is functions sort of like stored procs in SQL server. How do I create one? Is this syntax good, can I just copy it over to sql plus and run it?
I don't think this last bit will work for me, I am actually calling this code from vbscript. So I don't want to have to call functions. I think, we this be done with a case statement or an if then statement?
A function is a Stored Procedure that returns a value. This allows you to use them in SQL statements. The Function is stored in User_Source table and is compiled in the database. So you can use it from whatever language you want as long as you have execute privs to the function.
So first you would create the function in the database under the correct schema.
Create Or Replace Function Is_Numeric(p_in varchar2)
v_num number; -- local variable of type Number
v_num := p_in; -- store input parameter in local Number variable
return 'Y'; -- return Y for yes it is a number
exception -- error handling routine
when others then -- if any errors are encountered
return 'N'; -- return N.
If the input parameter is not Numeric it will raise an exception, thus returning N.
so now that you deployed your function grant it to the user / roles that will be executing the sql
Grant Execute on Is_Numeric to USER/ROLE;
Now when that user executes the select statement use