Mixing
invalid number, when no invalid number
having an issue with Oracle throwing the error
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
all locations follow the same rules, aside from a few abnormalities which have been excluded.
below is sample output:
Column A: being the row must be less than 39
Column B: being the height must be less than 17
Location_23_23 being the isle
some locations have a different design so ive added a '-' check at position 6 to eliminate those. ex (C0S1-0101)
ive exported the data and no abnormalities are found, but when scrolling through the results the error occurs.
code below:
select location_id,to_number(ltrim(rtrim(substr(location_id,7,2)))) asa,to_number(ltrim(rtrim(substr(location_id,9,2)))) as b,to_number(ltrim(rtrim(substr(location_id,4,2)))) as Location_23_23
from location
where location_id like '%C0S%'
and location_id not in ('C0SKRUSAGE','C0SKYUSAGE','C0SRBUSAGE')
and to_number(ltrim(rtrim(substr(location_id,7,2)))) < 39
and current_volume = 0
and to_number(ltrim(rtrim(substr(location_id,9,2)))) < 17
and to_number(ltrim(rtrim(substr(location_id,4,2)))) between 23 and 23
and substr(location_id,6,1) = '-'
numbers substring oracle-sqldeveloper
asked Nov 19 '18 at 20:56
Nicholas RichardsonNicholas Richardson
257
add a comment |
having an issue with Oracle throwing the error
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
all locations follow the same rules, aside from a few abnormalities which have been excluded.
below is sample output:
Column A: being the row must be less than 39
Column B: being the height must be less than 17
Location_23_23 being the isle
some locations have a different design so ive added a '-' check at position 6 to eliminate those. ex (C0S1-0101)
ive exported the data and no abnormalities are found, but when scrolling through the results the error occurs.
code below:
select location_id,to_number(ltrim(rtrim(substr(location_id,7,2)))) asa,to_number(ltrim(rtrim(substr(location_id,9,2)))) as b,to_number(ltrim(rtrim(substr(location_id,4,2)))) as Location_23_23
from location
where location_id like '%C0S%'
and location_id not in ('C0SKRUSAGE','C0SKYUSAGE','C0SRBUSAGE')
and to_number(ltrim(rtrim(substr(location_id,7,2)))) < 39
and current_volume = 0
and to_number(ltrim(rtrim(substr(location_id,9,2)))) < 17
and to_number(ltrim(rtrim(substr(location_id,4,2)))) between 23 and 23
and substr(location_id,6,1) = '-'
numbers substring oracle-sqldeveloper
asked Nov 19 '18 at 20:56
Nicholas RichardsonNicholas Richardson
257
please share the DDL for locations, insert statements for the rows that cause the problem to surface, and your version of Oracle db and sql developer
– thatjeffsmith
Nov 20 '18 at 0:51
Look att the following example SELECT TO_NUMBER(LTRIM(RTRIM(SUBSTR('1234567a9',7,2)))) from dual This will give you the same error as you have but SELECT TO_NUMBER(LTRIM(RTRIM(SUBSTR(' 1234567a9',7,2)))) from dual will not. Could it be that you need to make your LTRIM and RTRIM before SUBSTR. like SELECT TO_NUMBER(SUBSTR(LTRIM(RTRIM(' 12345678a9')),7,2)) from dual.
– W_O_L_F
Nov 20 '18 at 11:50
add a comment |
having an issue with Oracle throwing the error
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
all locations follow the same rules, aside from a few abnormalities which have been excluded.
below is sample output:
Column A: being the row must be less than 39
Column B: being the height must be less than 17
Location_23_23 being the isle
some locations have a different design so ive added a '-' check at position 6 to eliminate those. ex (C0S1-0101)
ive exported the data and no abnormalities are found, but when scrolling through the results the error occurs.
code below:
select location_id,to_number(ltrim(rtrim(substr(location_id,7,2)))) asa,to_number(ltrim(rtrim(substr(location_id,9,2)))) as b,to_number(ltrim(rtrim(substr(location_id,4,2)))) as Location_23_23
from location
where location_id like '%C0S%'
and location_id not in ('C0SKRUSAGE','C0SKYUSAGE','C0SRBUSAGE')
and to_number(ltrim(rtrim(substr(location_id,7,2)))) < 39
and current_volume = 0
and to_number(ltrim(rtrim(substr(location_id,9,2)))) < 17
and to_number(ltrim(rtrim(substr(location_id,4,2)))) between 23 and 23
and substr(location_id,6,1) = '-'
numbers substring oracle-sqldeveloper
asked Nov 19 '18 at 20:56
Nicholas RichardsonNicholas Richardson
257
having an issue with Oracle throwing the error
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
all locations follow the same rules, aside from a few abnormalities which have been excluded.
below is sample output:
Column A: being the row must be less than 39
Column B: being the height must be less than 17
Location_23_23 being the isle
some locations have a different design so ive added a '-' check at position 6 to eliminate those. ex (C0S1-0101)
ive exported the data and no abnormalities are found, but when scrolling through the results the error occurs.
code below:
select location_id,to_number(ltrim(rtrim(substr(location_id,7,2)))) asa,to_number(ltrim(rtrim(substr(location_id,9,2)))) as b,to_number(ltrim(rtrim(substr(location_id,4,2)))) as Location_23_23
from location
where location_id like '%C0S%'
and location_id not in ('C0SKRUSAGE','C0SKYUSAGE','C0SRBUSAGE')
and to_number(ltrim(rtrim(substr(location_id,7,2)))) < 39
and current_volume = 0
and to_number(ltrim(rtrim(substr(location_id,9,2)))) < 17
and to_number(ltrim(rtrim(substr(location_id,4,2)))) between 23 and 23
and substr(location_id,6,1) = '-'
numbers substring oracle-sqldeveloper
numbers substring oracle-sqldeveloper
asked Nov 19 '18 at 20:56
Nicholas RichardsonNicholas Richardson
257
asked Nov 19 '18 at 20:56
Nicholas RichardsonNicholas Richardson
257
edited Nov 19 '18 at 21:18
Nicholas Richardson
asked Nov 19 '18 at 20:56
Nicholas RichardsonNicholas Richardson
257
asked Nov 19 '18 at 20:56
Nicholas RichardsonNicholas Richardson
257
asked Nov 19 '18 at 20:56
Nicholas RichardsonNicholas Richardson
257
257
please share the DDL for locations, insert statements for the rows that cause the problem to surface, and your version of Oracle db and sql developer
– thatjeffsmith
Nov 20 '18 at 0:51
Look att the following example SELECT TO_NUMBER(LTRIM(RTRIM(SUBSTR('1234567a9',7,2)))) from dual This will give you the same error as you have but SELECT TO_NUMBER(LTRIM(RTRIM(SUBSTR(' 1234567a9',7,2)))) from dual will not. Could it be that you need to make your LTRIM and RTRIM before SUBSTR. like SELECT TO_NUMBER(SUBSTR(LTRIM(RTRIM(' 12345678a9')),7,2)) from dual.
– W_O_L_F
Nov 20 '18 at 11:50
add a comment |
please share the DDL for locations, insert statements for the rows that cause the problem to surface, and your version of Oracle db and sql developer
– thatjeffsmith
Nov 20 '18 at 0:51
Look att the following example SELECT TO_NUMBER(LTRIM(RTRIM(SUBSTR('1234567a9',7,2)))) from dual This will give you the same error as you have but SELECT TO_NUMBER(LTRIM(RTRIM(SUBSTR(' 1234567a9',7,2)))) from dual will not. Could it be that you need to make your LTRIM and RTRIM before SUBSTR. like SELECT TO_NUMBER(SUBSTR(LTRIM(RTRIM(' 12345678a9')),7,2)) from dual.
– W_O_L_F
Nov 20 '18 at 11:50
please share the DDL for locations, insert statements for the rows that cause the problem to surface, and your version of Oracle db and sql developer
– thatjeffsmith
Nov 20 '18 at 0:51
please share the DDL for locations, insert statements for the rows that cause the problem to surface, and your version of Oracle db and sql developer
– thatjeffsmith
Nov 20 '18 at 0:51
Look att the following example SELECT TO_NUMBER(LTRIM(RTRIM(SUBSTR('1234567a9',7,2)))) from dual This will give you the same error as you have but SELECT TO_NUMBER(LTRIM(RTRIM(SUBSTR(' 1234567a9',7,2)))) from dual will not. Could it be that you need to make your LTRIM and RTRIM before SUBSTR. like SELECT TO_NUMBER(SUBSTR(LTRIM(RTRIM(' 12345678a9')),7,2)) from dual.
– W_O_L_F
Nov 20 '18 at 11:50
Look att the following example SELECT TO_NUMBER(LTRIM(RTRIM(SUBSTR('1234567a9',7,2)))) from dual This will give you the same error as you have but SELECT TO_NUMBER(LTRIM(RTRIM(SUBSTR(' 1234567a9',7,2)))) from dual will not. Could it be that you need to make your LTRIM and RTRIM before SUBSTR. like SELECT TO_NUMBER(SUBSTR(LTRIM(RTRIM(' 12345678a9')),7,2)) from dual.
– W_O_L_F
Nov 20 '18 at 11:50
add a comment |
1 Answer
1
active
oldest
votes
this ended up solving my issue after toying with it for hours!
select location_id as Location_id
from location
where location_id like '%C0S%'
and location_id not in ('C0SKRUSAGE','C0SKYUSAGE','C0SRBUSAGE')
and ltrim(rtrim(substr(location_id,7,2))) < '48'
and current_volume = 0
and ltrim(rtrim(substr(location_id,9,2))) < '15'
and ltrim(rtrim(substr(location_id,4,2))) between '65' and '86'
and substr(location_id,6,1) = '-'
removing TO_NUMBER and adding '#' around the numbers!
answered Nov 20 '18 at 15:22
Nicholas RichardsonNicholas Richardson
257
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382506%2finvalid-number-when-no-invalid-number%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
this ended up solving my issue after toying with it for hours!
select location_id as Location_id
from location
where location_id like '%C0S%'
and location_id not in ('C0SKRUSAGE','C0SKYUSAGE','C0SRBUSAGE')
and ltrim(rtrim(substr(location_id,7,2))) < '48'
and current_volume = 0
and ltrim(rtrim(substr(location_id,9,2))) < '15'
and ltrim(rtrim(substr(location_id,4,2))) between '65' and '86'
and substr(location_id,6,1) = '-'
removing TO_NUMBER and adding '#' around the numbers!
answered Nov 20 '18 at 15:22
Nicholas RichardsonNicholas Richardson
257
add a comment |
this ended up solving my issue after toying with it for hours!
select location_id as Location_id
from location
where location_id like '%C0S%'
and location_id not in ('C0SKRUSAGE','C0SKYUSAGE','C0SRBUSAGE')
and ltrim(rtrim(substr(location_id,7,2))) < '48'
and current_volume = 0
and ltrim(rtrim(substr(location_id,9,2))) < '15'
and ltrim(rtrim(substr(location_id,4,2))) between '65' and '86'
and substr(location_id,6,1) = '-'
removing TO_NUMBER and adding '#' around the numbers!
answered Nov 20 '18 at 15:22
Nicholas RichardsonNicholas Richardson
257
add a comment |
this ended up solving my issue after toying with it for hours!
select location_id as Location_id
from location
where location_id like '%C0S%'
and location_id not in ('C0SKRUSAGE','C0SKYUSAGE','C0SRBUSAGE')
and ltrim(rtrim(substr(location_id,7,2))) < '48'
and current_volume = 0
and ltrim(rtrim(substr(location_id,9,2))) < '15'
and ltrim(rtrim(substr(location_id,4,2))) between '65' and '86'
and substr(location_id,6,1) = '-'
removing TO_NUMBER and adding '#' around the numbers!
answered Nov 20 '18 at 15:22
Nicholas RichardsonNicholas Richardson
257
this ended up solving my issue after toying with it for hours!
select location_id as Location_id
from location
where location_id like '%C0S%'
and location_id not in ('C0SKRUSAGE','C0SKYUSAGE','C0SRBUSAGE')
and ltrim(rtrim(substr(location_id,7,2))) < '48'
and current_volume = 0
and ltrim(rtrim(substr(location_id,9,2))) < '15'
and ltrim(rtrim(substr(location_id,4,2))) between '65' and '86'
and substr(location_id,6,1) = '-'
removing TO_NUMBER and adding '#' around the numbers!
answered Nov 20 '18 at 15:22
Nicholas RichardsonNicholas Richardson
257
answered Nov 20 '18 at 15:22
Nicholas RichardsonNicholas Richardson
257
answered Nov 20 '18 at 15:22
Nicholas RichardsonNicholas Richardson
257
answered Nov 20 '18 at 15:22
Nicholas RichardsonNicholas Richardson
257
257
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382506%2finvalid-number-when-no-invalid-number%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
please share the DDL for locations, insert statements for the rows that cause the problem to surface, and your version of Oracle db and sql developer
– thatjeffsmith
Nov 20 '18 at 0:51
Look att the following example SELECT TO_NUMBER(LTRIM(RTRIM(SUBSTR('1234567a9',7,2)))) from dual This will give you the same error as you have but SELECT TO_NUMBER(LTRIM(RTRIM(SUBSTR(' 1234567a9',7,2)))) from dual will not. Could it be that you need to make your LTRIM and RTRIM before SUBSTR. like SELECT TO_NUMBER(SUBSTR(LTRIM(RTRIM(' 12345678a9')),7,2)) from dual.
– W_O_L_F
Nov 20 '18 at 11:50