Invalid Number

I am trying to query a table in a sequence and I'm using DTREEANCESTORS, but the number (which exists in the table) is being rejected in the query as being a:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

If I shorten the value by one digit, it executes (and returns nothing from the invalid value). The column ANCESTORID is described as:

ANCESTORID NUMBER(10,0). I don't know if this is a problem within SQL Developer or what. The query, if placed in a LiveReport fails also with the same Oracle code. What function do I use to get it accepted? I tried TO_NUMBER on both the column and the value. I tried TO_CHAR to render them into a different type. No workenzee. The value is a full 10 digits long. It's a DataID to a Project. It's in there twice, once as a positive value and the other as a negative value. Using either fails.

Colin Schmidt

Find more posts tagged with

Comments

CBancroft

Hey Colin, the negative dataid generally represents a volume. Have you tried using sqlplus command line as another check on the results?

What is the exact query you are trying to execute?

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, March 03, 2017 11:50 AM
To: eLink Recipient
Subject: Invalid Number

Invalid Number

Posted by Schmidt, Colin On 03/03/2017 11:46 AM

I am trying to query a table in a sequence and I'm using DTREEANCESTORS, but the number (which exists in the table) is being rejected in the query as being a:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

If I shorten the value by one digit, it executes (and returns nothing from the invalid value). The column ANCESTORID is described as:

Colin Schmidt

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

Lindsay_Davies

Hi Colin,

Oracle datatype number (10,0) is the number of bytes and precision that a packed number can occupy, not the number of digits.

Post your query if you can, it is likely that you have got some syntax wrong rather than a genuine long number.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 03 March 2017 16:50
To: eLink Recipient
Subject: Invalid Number

Invalid Number

Posted by Schmidt, Colin On 03/03/2017 11:46 AM

I am trying to query a table in a sequence and I'm using DTREEANCESTORS, but the number (which exists in the table) is being rejected in the query as being a:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

If I shorten the value by one digit, it executes (and returns nothing from the invalid value). The column ANCESTORID is described as:

Colin Schmidt

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

NOTICE TO RECIPIENT: This e-mail is meant for only the intended recipient of the transmission, and may be a communication privileged by law. This e-mail, including any attachments, contains information that may be confidential, and is protected by copyright. If you received this e-mail in error, any review, use, dissemination, distribution, or copying of this e-mail is strictly prohibited. Please notify us immediately of the error by return e-mail and please delete this message from your system. Any communication of a personal nature in this e-mail is not made by or on behalf of any RES group company. E-mails sent or received may be monitored to ensure compliance with the law, regulation and/or our policies. Thank you in advance for your cooperation.

Colin_Schmidt

It’s a Project and their DataIDs are Negative. But they’re also in DTREEANCESTORS as a Positive number and neither work. No, I hadn’t tried SQL*Plus. I’ll try to get to that this afternoon.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, March 03, 2017 12:06 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE Invalid Number

RE Invalid Number

Posted by Bancroft, Chris On 03/03/2017 12:05 PM

Hey Colin, the negative dataid generally represents a volume. Have you tried using sqlplus command line as another check on the results?

What is the exact query you are trying to execute?

Invalid Number

Posted by Schmidt, Colin On 03/03/2017 11:46 AM

I am trying to query a table in a sequence and I'm using DTREEANCESTORS, but the number (which exists in the table) is being rejected in the query as being a:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

If I shorten the value by one digit, it executes (and returns nothing from the invalid value). The column ANCESTORID is described as:

Colin Schmidt

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Invalid Number
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.

Colin_Schmidt

The thing is, the query runs with a shorter number.

select dt.name "Doc/Folder",

dt.versionnum "CurrVer",

case

when dt.subtype > 0 then (select to_char(count(dv.version), '999,999') from dversdata dv where dt.dataid = dv.docid) end VerCnt,

to_char(dt.createdate, 'yyyy-Mon-dd') "Created",

get_fullname(dt.createdby) "Created By",

case

when rmc.rimsrsi is NULL then 'No Class'

else rmc.rimsrsi||' ['||dtc.name||']' end Classification,

full_path(dt.name) "Path"

from dtreeancestors dtan

join dtree dt on dtan.dataid = dt.dataid

left outer join rimsnodeClassification rmc on dt.dataid = rmc.nodeid

left outer join dtree dtc on rmc.classid = dtc.dataid

where dtan.ancestorid = to_number((%1) *-1)

Order by "Path"

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, March 03, 2017 1:01 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: [EXTERNAL] - RE Invalid Number

[EXTERNAL] - RE Invalid Number

Posted by Davies, Lindsay On 03/03/2017 01:01 PM

Hi Colin,

Oracle datatype number (10,0) is the number of bytes and precision that a packed number can occupy, not the number of digits.

Post your query if you can, it is likely that you have got some syntax wrong rather than a genuine long number.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 03 March 2017 16:50
To: eLink Recipient
Subject: Invalid Number

Invalid Number

Posted by Schmidt, Colin On 03/03/2017 11:46 AM

I am trying to query a table in a sequence and I'm using DTREEANCESTORS, but the number (which exists in the table) is being rejected in the query as being a:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

If I shorten the value by one digit, it executes (and returns nothing from the invalid value). The column ANCESTORID is described as:

Colin Schmidt

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Invalid Number
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

Lindsay_Davies

Hi Colin,

Then I would suspect the parameter handling is the cause of the problem.

If you are specifying Number, that is probably limited to a shorter length,
–> signed integer (4 bytes) = -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).

So you might try using String as a parameter and then convert it to the number format as you use it.

CAST(%1 AS Number)

Good luck

Lindsay

Lindsay Davies
Information Systems Administrator

D+44 1923 299 378 | M+44 7881 108 447
lindsay.davies@res-group.com | www.res-group.com

Home Safe - Every Day

Renewable Energy Systems Limited, registered in England and Wales with Company Number 1589961
Registered Office: Beaufort Court, Egg Farm Lane, Kings Langley, Hertfordshire WD4 8LR

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 03 March 2017 18:16
To: eLink Recipient
Subject: RE [EXTERNAL] - RE Invalid Number

RE [EXTERNAL] - RE Invalid Number

Posted by Schmidt, Colin On 03/03/2017 01:15 PM

The thing is, the query runs with a shorter number.

select dt.name "Doc/Folder",

dt.versionnum "CurrVer",

case

when dt.subtype > 0 then (select to_char(count(dv.version), '999,999') from dversdata dv where dt.dataid = dv.docid) end VerCnt,

to_char(dt.createdate, 'yyyy-Mon-dd') "Created",

get_fullname(dt.createdby) "Created By",

case

when rmc.rimsrsi is NULL then 'No Class'

else rmc.rimsrsi||' ['||dtc.name||']' end Classification,

full_path(dt.name) "Path"

from dtreeancestors dtan

join dtree dt on dtan.dataid = dt.dataid

left outer join rimsnodeClassification rmc on dt.dataid = rmc.nodeid

left outer join dtree dtc on rmc.classid = dtc.dataid

where dtan.ancestorid = to_number((%1) *-1)

Order by "Path"

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

[EXTERNAL] - RE Invalid Number

Posted by Davies, Lindsay On 03/03/2017 01:01 PM

Hi Colin,

Oracle datatype number (10,0) is the number of bytes and precision that a packed number can occupy, not the number of digits.

Post your query if you can, it is likely that you have got some syntax wrong rather than a genuine long number.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 03 March 2017 16:50
To: eLink Recipient
Subject: Invalid Number

Invalid Number

Posted by Schmidt, Colin On 03/03/2017 11:46 AM

I am trying to query a table in a sequence and I'm using DTREEANCESTORS, but the number (which exists in the table) is being rejected in the query as being a:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

If I shorten the value by one digit, it executes (and returns nothing from the invalid value). The column ANCESTORID is described as:

Colin Schmidt

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Invalid Number
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Invalid Number
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

Colin_Schmidt

In Oracle, I tried “TO_CHAR(dtan.ancestorid) = ‘-1110213047’” and I’ve tried on the 2^nd half: to_char(1131313332 *-1).

CAST works in SQL Server but I’ll check if Oracle allows it.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

Knowledgelink@capitalone.com

Internal: 433-8910

External: 804-284-8910

Cell: 918-904-9223

Don’t waste space with attachments – follow thesestepsto e-mail links to documents stored in Knowledgelink

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Saturday, March 04, 2017 5:14 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE RE [EXTERNAL] - RE Invalid Number

RE RE [EXTERNAL] - RE Invalid Number

Posted by Davies, Lindsay On 03/04/2017 05:13 AM

Hi Colin,

Then I would suspect the parameter handling is the cause of the problem.

If you are specifying Number, that is probably limited to a shorter length,
–> signed integer (4 bytes) = -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).

So you might try using String as a parameter and then convert it to the number format as you use it.

CAST(%1 AS Number)

Good luck

Lindsay

RE [EXTERNAL] - RE Invalid Number

Posted by Schmidt, Colin On 03/03/2017 01:15 PM

The thing is, the query runs with a shorter number.

select dt.name "Doc/Folder",

dt.versionnum "CurrVer",

case

when dt.subtype > 0 then (select to_char(count(dv.version), '999,999') from dversdata dv where dt.dataid = dv.docid) end VerCnt,

to_char(dt.createdate, 'yyyy-Mon-dd') "Created",

get_fullname(dt.createdby) "Created By",

case

when rmc.rimsrsi is NULL then 'No Class'

else rmc.rimsrsi||' ['||dtc.name||']' end Classification,

full_path(dt.name) "Path"

from dtreeancestors dtan

join dtree dt on dtan.dataid = dt.dataid

left outer join rimsnodeClassification rmc on dt.dataid = rmc.nodeid

left outer join dtree dtc on rmc.classid = dtc.dataid

where dtan.ancestorid = to_number((%1) *-1)

Order by "Path"

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

[EXTERNAL] - RE Invalid Number

Posted by Davies, Lindsay On 03/03/2017 01:01 PM

Hi Colin,

Oracle datatype number (10,0) is the number of bytes and precision that a packed number can occupy, not the number of digits.

Post your query if you can, it is likely that you have got some syntax wrong rather than a genuine long number.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 03 March 2017 16:50
To: eLink Recipient
Subject: Invalid Number

Invalid Number

Posted by Schmidt, Colin On 03/03/2017 11:46 AM

I am trying to query a table in a sequence and I'm using DTREEANCESTORS, but the number (which exists in the table) is being rejected in the query as being a:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

If I shorten the value by one digit, it executes (and returns nothing from the invalid value). The column ANCESTORID is described as:

Colin Schmidt

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Invalid Number
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Invalid Number
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Invalid Number
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

Colin_Schmidt

I tried SQL*Plus and get the same error returned.

I’d tried TO_CHAR and other functions on the table column, but nothing changed. This time I tried the “TO_CHAR” function on the table column with a format:

To_char(dtan.ancestorid, ‘S9999999999’) = ‘-1234567890’

and it executed, so I’ll see what it’ll bring back. The key to make it execute was the “S” in the format.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

[EXTERNAL] - RE Invalid Number

Posted by Davies, Lindsay On 03/03/2017 01:01 PM

Hi Colin,

Oracle datatype number (10,0) is the number of bytes and precision that a packed number can occupy, not the number of digits.

Post your query if you can, it is likely that you have got some syntax wrong rather than a genuine long number.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 03 March 2017 16:50
To: eLink Recipient
Subject: Invalid Number

Invalid Number

Posted by Schmidt, Colin On 03/03/2017 11:46 AM

I am trying to query a table in a sequence and I'm using DTREEANCESTORS, but the number (which exists in the table) is being rejected in the query as being a:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

If I shorten the value by one digit, it executes (and returns nothing from the invalid value). The column ANCESTORID is described as:

Colin Schmidt

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Invalid Number
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

Colin_Schmidt

It seems I misinterpreted where the “invalid number” was coming from. I had inadvertently used the dtree.name where I should have the dtree.dataid. DOH! So, nothing functionally malfunctioning. Just a Homer moment.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Sunday, March 05, 2017 7:19 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE RE RE [EXTERNAL] - RE Invalid Number

RE RE RE [EXTERNAL] - RE Invalid Number

Posted by Schmidt, Colin On 03/05/2017 07:18 PM

In Oracle, I tried “TO_CHAR(dtan.ancestorid) = ‘-1110213047’” and I’ve tried on the 2^nd half: to_char(1131313332 *-1).

CAST works in SQL Server but I’ll check if Oracle allows it.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

Knowledgelink@capitalone.com

Internal: 433-8910

External: 804-284-8910

Cell: 918-904-9223

Don’t waste space with attachments – follow thesestepsto e-mail links to documents stored in Knowledgelink

RE RE [EXTERNAL] - RE Invalid Number

Posted by Davies, Lindsay On 03/04/2017 05:13 AM

Hi Colin,

Then I would suspect the parameter handling is the cause of the problem.

If you are specifying Number, that is probably limited to a shorter length,
–> signed integer (4 bytes) = -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).

So you might try using String as a parameter and then convert it to the number format as you use it.

CAST(%1 AS Number)

Good luck

Lindsay

RE [EXTERNAL] - RE Invalid Number

Posted by Schmidt, Colin On 03/03/2017 01:15 PM

The thing is, the query runs with a shorter number.

select dt.name "Doc/Folder",

dt.versionnum "CurrVer",

case

when dt.subtype > 0 then (select to_char(count(dv.version), '999,999') from dversdata dv where dt.dataid = dv.docid) end VerCnt,

to_char(dt.createdate, 'yyyy-Mon-dd') "Created",

get_fullname(dt.createdby) "Created By",

case

when rmc.rimsrsi is NULL then 'No Class'

else rmc.rimsrsi||' ['||dtc.name||']' end Classification,

full_path(dt.name) "Path"

from dtreeancestors dtan

join dtree dt on dtan.dataid = dt.dataid

left outer join rimsnodeClassification rmc on dt.dataid = rmc.nodeid

left outer join dtree dtc on rmc.classid = dtc.dataid

where dtan.ancestorid = to_number((%1) *-1)

Order by "Path"

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

[EXTERNAL] - RE Invalid Number

Posted by Davies, Lindsay On 03/03/2017 01:01 PM

Hi Colin,

Oracle datatype number (10,0) is the number of bytes and precision that a packed number can occupy, not the number of digits.

Post your query if you can, it is likely that you have got some syntax wrong rather than a genuine long number.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 03 March 2017 16:50
To: eLink Recipient
Subject: Invalid Number

Invalid Number

Posted by Schmidt, Colin On 03/03/2017 11:46 AM

I am trying to query a table in a sequence and I'm using DTREEANCESTORS, but the number (which exists in the table) is being rejected in the query as being a:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

If I shorten the value by one digit, it executes (and returns nothing from the invalid value). The column ANCESTORID is described as:

Colin Schmidt

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Invalid Number
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Invalid Number
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Invalid Number
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

[To post a comment, use the normal reply function]
Topic:	Invalid Number
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16