CODEWITHSUNDEEP
sqloracle-database
leora
leora

Reputation: 196429

Why am I getting Oracle "invalid number" on my query when I am doing no type conversion?

I have a website that creates this SQL for one of the backend database queries (simplifying it for the question)

 Select * 
   from People 
   where Status = 'Active'
     and GroupId = 103403 
     and  (TeamName in ('TEAM1', 'TEAM2')) 
     and DELETED is NULL

and this works fine. I now need to add a few new team names, something like this

Select * 
  from People 
   where Status = 'Active'
   and GroupId = 103403 
   and  (TeamName in ('TEAM1', 'TEAM2','TEAM3', 'TEAM4')) 
   and DELETED is NULL

and i get this error:

ORA-01722: invalid number

what is weird is that it doesn't seem to be anything wrong with "TEAM3" or "TEAM4" as if i do this

Select * 
  from People 
   where Status = 'Active'
   and GroupId = 103403 
   and  (TeamName in ('TEAM3', 'TEAM4')) 
   and DELETED is NULL

it also works fine. So only when i have a certain number of teams in the "IN" condition is when it breaks.

Any suggestions for what the issue could be. When I google that Oracle error all I see if people that have some conversion logic in their SQL as the root cause due to data type mismatching but I don't have any conversion logic here.

Upvotes: 0

Views: 901

Answers (1)

Codo
Codo

Reputation: 78795

I suspect that GroupId isn't a numeric column. And depending on your other conditions, the condition GroupId = 103403 is evaluated before or after the other conditions. If it's evaluated for a row that contains an invalid number in the GroupId column, then your query fails.

If my assumption is correct, the change the condition to:

GroupId = '10340'

BTW: Please post the real SQL code, the code you have run in your own tests. Your current code contains double quotes, which hardly works.

Upvotes: 3

Related Questions