INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

to_number PL/SQL

to_number PL/SQL

(OP)
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;  

RE: to_number PL/SQL

AWood,

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

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

RE: to_number PL/SQL

(OP)
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

RE: to_number PL/SQL

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

CODE

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

RE: to_number PL/SQL

(OP)
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
    

RE: to_number PL/SQL

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

RE: to_number PL/SQL

(OP)
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.

RE: to_number PL/SQL

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.

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

RE: to_number PL/SQL

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.

RE: to_number PL/SQL

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

RE: to_number PL/SQL

Quote (Bill):

CODE

to_sumber(substr(comment,1,2))
I think you wanted to us the "TO_SLUMBER" function, which puts the substring to sleep, right? <grin>

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

RE: to_number PL/SQL

LOL.... Good one dave

Bill
Lead Application Developer
New York State, USA

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close