1722 error

[LaurenM]

Hi Group, I have this payroll table that I am just wanting to get the total number of records from, which is done using select count(*) from payroll, now I need to format the number so it is easier to read the number, the table has several million records in it. I have tried using to_number using the format '99,999,999' but I keep getting a 01722 error, I have tried casting the count using to_char then casting that back to a number still the 01722 error. Any pointers as to why almost every time I try using to_number I keep getting the 01722 error?

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[Thargy]

Lauren,

try the following:-

CREATE TABLE LAUREN_TEST (DUMMY_COLUMN VARCHAR2(50));

INSERT INTO LAUREN_TEST(DUMMY_COLUMN) VALUES ('ROW 1');
INSERT INTO LAUREN_TEST(DUMMY_COLUMN) VALUES ('ROW 2');
INSERT INTO LAUREN_TEST(DUMMY_COLUMN) VALUES ('ROW 3');
INSERT INTO LAUREN_TEST(DUMMY_COLUMN) VALUES ('ROW 4');
INSERT INTO LAUREN_TEST(DUMMY_COLUMN) VALUES ('ROW 5');
INSERT INTO LAUREN_TEST(DUMMY_COLUMN) VALUES ('ROW 6');
INSERT INTO LAUREN_TEST(DUMMY_COLUMN) VALUES ('ROW 7');
INSERT INTO LAUREN_TEST(DUMMY_COLUMN) VALUES ('ROW 8');
INSERT INTO LAUREN_TEST(DUMMY_COLUMN) VALUES ('ROW 9');

COMMIT;

SELECT TO_CHAR(COUNT(*), '99.99') FROM LAUREN_TEST;

This yields 9.00. You should use TO_CHAR of the number and specify the format as shown.

Regards

T

 

[SantaMufasa]

I have tried using to_number using the format '99,999,999'...almost every time I try using to_number I keep getting the 01722 error?

The problem appears to be that you are converting data in the wrong direction: saying "TO_NUMBER('nnnnnnn','99,999,999')" means that you are attempting to convert a character-string expression value that looks like this: "84,333,205" (including commas), into a number whose contents will be "84333205".


I believe you want to go the other direction: You have several million rows (e.g. 84333205) that you want to "pretty up" with commas. Therefore you would use the function that Tharg proposed: "TO_CHAR(COUNT(*), '999,999,999.99')", which transforms "84333205" into "84,333,205".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LaurenM]

Thanks Tharg & Mufasa

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[Beilstwh]

Thank them with a star

Bill
Lead Application Developer
New York State, USA

 

[LaurenM]

Just a follow up, it seems to me that the 1722 error will be thrown whenever you try to use the to_number on a numeric, am I right in assuming this?

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

the 1722 error will be thrown whenever you try to use the to_number on a numeric

No, Lauren, you should not get an error when doing a TO_NUMBER on a numeric. What Oracle does, in such a case, is that Oracle converts the NUMBER to VARCHAR2, then converts the VARCHAR2 back to a number via the TO_NUMBER function. In other words, it works but is wholly inefficient:

SQL> select to_number(17) from dual;

TO_NUMBER(17)
-------------
           17

Let us know if you encounter any exception to this behavior.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LaurenM]

I just tried it on a table the column is defined as number(9,2) and got the 1722 error.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[LaurenM]

Correction, only when I try to use a format mask with the to_number function on a numeric column do I get the 1722 error.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

only when I try to use a format mask with the to_number function on a numeric column do I get the 1722 error.

...And that is understandable...your format mask contains punctuation that the source expression cannot possibly contain, right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LaurenM]

Correct, it's only when I need to format the source data, or pretty it up that I even try to use the to_number function. I have been using the to_char function to get the formatting that I have needed. I've always assumed that the format mask was exactly that a mask, that was placed on the data to make it look a certain way, not that the data already had to have all the necessary information in it. I think I'm less confused now.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

I've always assumed that the format mask was exactly that a mask, that was placed on the data to make it look a certain way

That is correct for TO_CHAR ("it makes a number look a certain way.")

not that the data already had to have all the necessary information in it.

I emphasize that TO_NUMBER goes the opposite direction from TO_CHAR...TO_NUMBER takes character data (that may have numeric punctuation) and transforms the character data (which must contain only numerals and valid numeric punctuation) into a valid (non-edited) numeric value. (Remember, Oracle stores NUMBERs internally with no non-numeric punctuation.) The format mask for TO_NUMBER shows how the character-string argument (that you wish to transform) appears with numeric punctuation prior to transformation into a NUMBER. As an example:

select to_number('  9,187,939.99','999,999,999.99') from dual;

TO_NUMBER('9,187,939.99','999,999,999.99')
------------------------------------------
                                9187939.99

Notice that the format mask, above, tells Oracle how to strip away the original punctuation.



Let us know if this makes sense or if you have additional questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Beilstwh]

You can only display characters. even when you type

select 123 from dual;

a conversion to char is done. To format a number you MUST use TO_CHAR with a format mask, otherwise it will perform the same conversion as if you did

select to_char(123) from dual;

while

Bill
Lead Application Developer
New York State, USA