to_number PL/SQL 1

[awood69]

I a column names comments, the record I am working with has the following value in comments - '11, 12'

I want to use this select statement as a sub query that would replace the 11, 12 for region in the bottom statement -

SELECT to_number(comments, '99')
from report_dist
where group_type = 'PLT01'


select *
from region
where email_flag = 'Y'
and ((region in(11, 12)
and 'PLT01' like 'PLT%')
or 'PLT01' NOT LIKE 'PLT%')
order by region;

 

[SantaMufasa]

AWood,

What results are you receiving? Are you receiving any syntax, run-time, or logic errors? What, specifically, can we do to help you?


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[awood69]

I get

ORA-01722: invalid number


select *
from region
where email_flag = 'Y'
and ((region in(SELECT to_number(comments, '99')
from report_dist
where group_type = 'PLT01')
and 'PLT01' like 'PLT%')
or 'PLT01' NOT LIKE 'PLT%')
order by region

 

[carp]

My guess is that you have data in your comments column that cannot be converted to a number. Try

.
.
.
to_char(region) IN (SELECT comments 
                      FROM report_dist
.
.
.

 

[awood69]

Thanks but that didn't work still get -

Example data - '11, 12'

ORA-01722: invalid number


select *
from region
where email_flag = 'Y'
and ((to_char(region) in(SELECT to_number(comments, '99')
from report_dist
where group_type = 'PLT01')
and 'PLT01' like 'PLT%')
or 'PLT01' NOT LIKE 'PLT%')
order by region

 

[awood69]

If i change the data to a single number then it works. Maybe I need another approach?

 

[awood69]

If I change the data to - 11, 12

and use

select *
from region
where email_flag = 'Y'
and ((region in(SELECT comments
from report_dist
where group_type = 'PLT01')
and 'PLT01' like 'PLT%')
or 'PLT01' NOT LIKE 'PLT%')
order by region

I get no error but no rows returned.

 

[SantaMufasa]

Carp, as usual, is absolutely correct. (You have non-numeric data in COMMENTS, which throws the run-time error.) Oracle does the appropriate conversions, internally, to compare two operands (i.e., NUMBER and DATE to VARCHAR2). In your case, since COMMENTS is defined as VARCHAR2, Oracle converts REGION to VARCHAR2, as well, to make the comparison. So there is no need for you to do the conversions explicitly.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[carp]

Swood -

If you will look at the code snippet I provided, I removed the TO_NUMBER invocation. The reason your modified code did not work is because you did not do a similar removal.

You problem stems from trying to convert a non-numeric character string (that is, it has alphabetic characters in it) to a number. This is what is throwing your exception. For instance, if your comments value is 'This is not a number', Oracle will not be able to convert this to a number.

 

[Beilstwh]

when you have a comment of '12, 13' it will try to convert the ENTIRE comment to a number and a comma is not a valid number. If you did

to_sumber(substr(comment,1,2))

it should work.

Bill
Lead Application Developer
New York State, USA

 

[SantaMufasa]

to_sumber(substr(comment,1,2))

I think you wanted to us the "TO_SLUMBER" function, which puts the substring to sleep, right? <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Beilstwh]

LOL.... Good one dave

Bill
Lead Application Developer
New York State, USA