numeric test in SQL

85 pts.
Tags:
SQL error messages
SQL Testing
WINSQL Numeric Function
I have a complex select query and must now add an additonal condition. One of the elements must be numeric. The 'isnumeric' function keeps erroring out on me.

select FWVCCOA_FMS_CENTER cfmsctr,        fwvccoa_fund_code  cfundcde,        fwvccoa_orgn_code  corgncde,        ftvfund_orgn_code_def forgncde,        fwvccoa_prog_code  cprogcde,        ftvfund_prog_code_def fprogcde   from temple_finance.fwvccoa, ftvfund [strong]where ISNUMERIC(FWVCCOA_ACCI_CODE)= 1[/strong]  and fwvccoa_fund_code = ftvfund_fund_code and (fwvccoa_orgn_code <> ftvfund_orgn_code_def or fwvccoa_prog_code <> ftvfund_prog_code_def) and fwvccoa_fund_code <> '100000';

ASKED: December 14, 2009  2:35 PM
UPDATED: December 15, 2009  2:41 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi Sherry. Welcome back to ITKE.

ISNUMERIC is a T-SQL function, and Oracle doesn’t have an equivalent.

However, you can easily build your own.

Something like this:

<pre>CREATE OR REPLACE FUNCTION isnumeric(p_value IN VARCHAR) RETURN NUMBER IS
l_value NUMBER;
BEGIN
l_value := p_value;
RETURN 1;
EXCEPTION
WHEN value_error THEN
RETURN 0;
END;
/
</pre>
After creating this function, your query should run without errors.

P.S. DBMS = Database management system (i.e. Oracle, Sql Server, etc.)

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    1) What is the error message you are getting ? 2) What DBMS are you using ?
    65,110 pointsBadges:
    report
  • Sherryborden
    Carlos, I get 'invalid identifier'. I'm not sure what you mean by What DBMS am I using? If it's what version of Oracl Sql Developer, then that's 1.5.4 If that's not what you meant could you explain #2 better. Thanks for helping. Sherry
    85 pointsBadges:
    report
  • Sherryborden
    Carlos, I am so very new to SQL so please forgive me for asking dumb questions. The snippet of code I sent you was 1 of 25 simple select statements that come 1 after another. There's no procedure. The entire script is written in SQL and not pl/sql. Can I take what you offered above and include it or do I have to rework the the entire script to put it into pl/sql? Thank you so much for all your help. Sherry
    85 pointsBadges:
    report
  • carlosdl
    No problem Sherry. There is no need to change your script. You would only need to create this function in your Oracle database, and you can leave your script intact. The function should be created with the same user you run your script with, or the EXECUTE privilege would need to be granted to that user.
    65,110 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following