Parameter Manipulation – Turning ‘abc’ type string into a number

70 pts.
Tags:
Microsoft Visual Studio
ORA
I have the following code being used in a parameter: SELECT OU1.OU_CODE AS PROCESS_LEVEL , RTRIM(OU1.OU_CODE) || ' - ' || AD1.DEPARTMENT_NAME AS Description FROM ODS_OWNER.POSITION_ASSIGNMENTS PE1 JOIN ODS_OWNER.ADMIN_DEPARTMENTS AD1 ON PE1.DEPARTMENT_ID = AD1.DEPARTMENT_ID JOIN ODS_OWNER.ORGANIZATIONAL_UNITS OU1 ON AD1.OU_CODE = OU1.OU_CODE WHERE PE1.POSITION_LEVEL = 1 AND COALESCE(PE1.END_DATE, CAST('01/JAN/1753' AS DATE)) = CAST('01/JAN/1753' AS DATE) --PE1.END_DATE = '1/1/1753' AND ( (:SCHOOLORCENTRAL = 'SCHOOLS' AND OU1.OU_CODE = '700') OR (:SCHOOLORCENTRAL = 'ALL' AND OU1.OU_CODE > '1') ) GROUP BY OU1.OU_CODE , AD1.DEPARTMENT_NAME ORDER BY AD1.DEPARTMENT_NAME I am getting an ORA-01722:invalid number error. I am using Microsoft Visual Studio 2008. I know the problem is with the lines containing the :SCHOOLORCENTRAL parameter but have no clue how to correct the error. Can someone show me how to write one of these lines correctly? Thank you

Software/Hardware used:
2008 Visual Studio
ASKED: November 16, 2012  4:17 PM
UPDATED: November 16, 2012  4:51 PM

Answer Wiki

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

Discuss This Question: 2  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
    What do you mean by "...code being used used in a parameter" ? How and where exactly are you using this code ? What programming language are you using ? Where and how is the :SCHOOLORCENTRAL pareameter defined ?
    65,110 pointsBadges:
    report
  • TomLiotta
    The lines that reference :SCHOOLORCENTRAL also reference OU1.OU_CODE. By the way it looks, it's OU1.OU_CODE that would be the problem. But that might depend on what is being passed into the :SCHOOLORCENTRAL parameter. Is a numeric value being passed in? Without seeing the definitions of anything, there isn't much we can say to help. Almost any part of the lines might be wrong, or the column definitions might be wrong, or the parameter values might be passed in incorrectly. -- Tom
    125,585 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