Sitemap

Amicuk Programming Answers

Getting error as ORA-01722: invalid number

-0001-11-30   Views:0

Advertisement

Hi below is my query select count(1) from table1 where table1.rqstid = '83041' AND table1..score_name = 'Small Business Credit Risk Score' AND to_number(table1.total_score) >= '70' and rownum<2 I tried to convert '70' to 70, but still I got ame erro

Hi below is my query
select count(1) from table1 where table1.rqstid = '83041' AND table1..score_name = 'Small Business Credit Risk Score' AND
to_number(table1.total_score) >= '70' and rownum<2
I tried to convert '70' to 70, but still I got ame error.
In table1 total_score is varchar2(30) data type, but for this rqstid the data is 420, so it should convert 420 to number and compare with 70.
But why I am getting this error? Please help.
If I remove to_number conversion fot total score then this error is not coming, but this will not give correct result for me.
Thanks.

The replay answer
Advertisement
CREATE TABLE SBE_SCORE (
RQSTID VARCHAR2 (30) NOT NULL,
SCORE_NAME VARCHAR2 (300),
TOTAL_SCORE VARCHAR2 (30),
RISKLEVEL VARCHAR2 (30),
SUBSCRIBER_ID NUMBER (10))
INSERT INTO SBE_SCORE ( RQSTID, SCORE_NAME, TOTAL_SCORE, RISKLEVEL,
SUBSCRIBER_ID ) VALUES (
'83041', 'Small Business Credit Risk Score for Financial Services', '420', NULL, NULL);
My Query was
SELECT
count(1)
FROM
sbe_score
WHERE
sbe_score.rqstid = '83041' AND
sbe_score.score_name = 'Small Business Credit Risk Score for Financial Services' AND
to_number(sbe_score.total_score) >= '70' and
rownum<2
Error:ORA:01722 Invalid Number
Now I have modified as below and I am able to get correct results
SELECT
count(1)
FROM
sbe_score
WHERE
sbe_score.rqstid = '83041' AND
sbe_score.score_name = 'Small Business Credit Risk Score for Financial Services' AND
to_number(translate(lower(sbe_score.total_score),'[email protected]_','0'))>= 70 and
rownum<2
I I omit r in the translate function then I am getting same error Invalid number, So just I want to know whether the data 420 for total_score is taking "r" internally? withou our knowledge?

Go to See the other 15 answers

Error Message - ORA-01722 Invalid Number

Category:DefaultRelease time:2015-10-11Views:130

Hello: I have the following situation. When I create an organization, a record is inserted into the following table, AGREEMENTS, and populates four fields. I have an update form which selects the organization from the AGREEMENTS table so the user can[More]

Error Message (ORA-01722: invalid number) when add amount into Number field

Category:DefaultRelease time:2015-10-11Views:130

I am using VB.net, trying to insert data into a field called amt with Data Type 'Number'. my vb.net code is as follows Dim oradb As String = DatabaseConnectionString Dim AmountValue As Decimal = 123.45 'get the connection Dim conn As New OracleConnec[More]

Sdo_util error code ORA-01722: invalid number in Oracle 11g R2

Category:DefaultRelease time:-0001-11-30Views:130

Dear every one, Greetings. Hi. As mentioned in the title, I met a strange problem when I was trying to extrude the 2D objects into 3D. The error code says ORA-01722: invalid number. Then, to eliminate the possibility that the code would be wrong, I t[More]

Getting error as ORA-01722: invalid number

Category:DefaultRelease time:-0001-11-30Views:130

Hi below is my query select count(1) from table1 where table1.rqstid = '83041' AND table1..score_name = 'Small Business Credit Risk Score' AND to_number(table1.total_score) >= '70' and rownum<2 I tried to convert '70' to 70, but still I got ame erro[More]

How to determine offending column in ORA-01722: invalid number error ...

Category:DefaultRelease time:2015-10-11Views:130

When an error like ORA-01722: invalid number error occurs and you are dealing with many columns that could be the 'culprit', does anyone have a method by which you can quickly determine offending column?SQL PLus will tell you: SQL> create table t   2[More]

Error ORA-01722: invalid number

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I am using Apex 3.2 on Oracle 11g (Enterprise Edition). I am new to Apex and I am using an imported application and editing its features to match my requirements. While uploading a CSV file, I am getting error as :ORA-01722: invalid number" Can s[More]

ORA-01722: invalid number

Category:DefaultRelease time:2015-10-11Views:130

Hi,[email protected] we are working on Import tool, which takes a CSV file and inserts in Oracle DB.I am using PLSQL UTL_File.Please look into the PLSQL file and clarify my Queries. If the Last column is a Varchar field, we are able to insert R[More]

ORA-01722: invalid number while using TO_CHAR function.

Category:DefaultRelease time:2015-10-11Views:130

Hi All, I was using this query from around past 1 year and everything was working fine. select to_char ( '2012/12/23','YYYY/MM/DD') from dual; how ever from monday onwards , this is returning me with the following error. ORA-01722: invalid number The[More]

Recieving ORA-01722 invalid number error while creating a materialized view

Category:DefaultRelease time:2015-10-11Views:130

Hi, I am receiving a ORA-01722 invalid number error while creating a materialized view. when run the select statement of the view i don't get any error, but when i use the same select statement to create a materialized view i receive this error. Coul[More]

Please help: Case Statement - ORA-01722: invalid number Error

Category:DefaultRelease time:2015-10-11Views:130

Hi All, I'm trying to use Case statement to recode Cancelled statuses into two groups 'Reject' if the difference between first date and last date is less than 29 and 'Accept' if the difference between first date and last date is greater than 30 Here[More]

User Accounts and ORA-01722: invalid number error

Category:DefaultRelease time:2015-10-11Views:130

Hello, I have been using Express for a month or so now but have very limited knowledge of how it works. My Problem: I have a listing screen for user records which creates records in table QAS_USERS. When I add a record it saves the data OK and put th[More]

Hot
Three times in the last month, Pages '08 has frozen on me when trying to do a simple search-and-replace in a document. The document was imported as a Word doc, and had (scattered throughout it) some straight quotes. In the past, I have been able to d [More]
i dont remember the first apple id that link with my clash of clans apps. the problem now is i need to link my COC village and it keep saying "please log in with correct game center" is there anyway that i can retrieve my old apple id? please he [More]
My PowerBook's showing signs of problems and I think it needs to be reinitialized. The only sticking point is that its Super Drive is jammed and I can't get an audio CD out that's been stuck in there for an embarassingly long time. Last year I was ab [More]
Hi all, First time poster here so please forgive my likely ignorance of Bridge's brilliant features.  I have just started to use Bridge as my primary tool in my post-processing workflow.  As I started going through a job I completed recently I starte [More]
Hi all-- Bought an iPhone 5 off ebay. It's totally wiped, and when I go to activate, I get a "Sim Not Valid" error on the phone, and "sim card inserted in this iphone does not appear to be supported" in iTunes. It is an AT&T phone, [More]
hi all, I have exported the "frames.java" file into a jar file called "frames.jar". I have also exported "invokejar.java" file into a jar file called "invokejars.jar". On doubleclicking invokejars.jar file, its not [More]
Hi, where can I customize the requirements plan number. Thanks DedeIn MD61 you can enter a reqmt. plan number as per your need, anything preventing you from entering the same? or if the business need is something else, then can you let us know the sa [More]
Do you know a reader app in which I can search for words? I mean which can open .doc files and i can search for specific words in the full text! Thanks!I am not aware of any such fix.  If you are concerned, apply a screen protector.Read other 2 answe [More]
How to maintain/configure a check for 3 way match(PO, GR, Invoice) in SRMHi Sam, Please use a Limit cart and select the radiobutton "Confirmation and Invoice" which will need a GR/Confirmation before Invoice on a PO. Please reward points if the [More]
Hi! Does anyone are aware on how the Heart Bleed Vulnerability affects the Cisco SPA Phones? The product is not listed on the Heart Bleed Vulnerability Announcement by Cisco. However, knowing the version of OpenSSL used on the code makes everyone mor [More]