invalid number error

Posted on 2011-10-22
Last Modified: 2012-05-12
Table1 has values 12345 and 1234
and table2 has one value 12345,1234

When i try to use the following query: am getting invalid number.
select * from table1 a, table2 b
where a.tablecol1 in (b.tablecol)

any other way to execute this.
Question by:sakthikumar
    LVL 23

    Accepted Solution

    Try this query

    select * from #table1 a
    inner join #table2 b on b.ID like '%' + a.ID + '%'

    Open in new window

    LVL 23

    Assisted Solution

    - you cannot have a data in a column with comma (,) work for the IN function. you need to save the data as follows in table2:

    - then try again use the same query to execute:
    select * from table1 a, table2 b
    where a.tablecol1 in (b.tablecol)
    LVL 23

    Assisted Solution

    by:Rajkumar Gs
    This query is not advicable, since it may take much more time for a table with very huge data.
    I suggest you to store the data - '12345,1234', seperated into 12345 and 1234 and store as numbers (separate rows). By this way, you can take advantage of numeric column's speed on index and your queries could perform faster.

    Author Closing Comment

    Thank you, nice suggestion from both of you.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This video shows how to recover a database from a user managed backup

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now