[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


invalid number error

Posted on 2011-10-22
Medium Priority
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
  • 2
LVL 23

Accepted Solution

Rajkumar Gs earned 1332 total points
ID: 37011684
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

OP_Zaharin earned 668 total points
ID: 37011688
- 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
Rajkumar Gs earned 1332 total points
ID: 37011692
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

ID: 37013152
Thank you, nice suggestion from both of you.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month19 days, 10 hours left to enroll

872 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