0

I have following query

SELECT T1.col2
FROM T1
  LEFT OUTER JOIN T2 
  ON ',' + T2.col2 + ','  LIKE '%,' + T1.col1 + ',%'

Fiddle

There are no numbers involved in it. Still it is showing following error

ORA-01722: invalid number : SELECT T1.col2 FROM T1 LEFT OUTER JOIN T2 ON ',' + T2.col2 + ',' LIKE '%,' + T1.col1 + ',%'

How can we fix it?

REFERENCES

  1. Oracle faq - ORA-01722
LCJ
  • 22,196
  • 67
  • 260
  • 418

2 Answers2

2

I am new to Oracle and it was an incorrect syntax. We need to use || instead of +. The concatenation operator is different than SQL Server

  1. Concatenation Operator
  2. Joining tables with LIKE (SQL)

However, the message ORA-01722: invalid number seems to be misleading

Community
  • 1
  • 1
LCJ
  • 22,196
  • 67
  • 260
  • 418
  • 1
    The `invalid number` error comes from the fact that you were using the operator to add *numbers*. Therefor Oracle tried to convert the all involved arguments into numbers but failed doing that for apparent reasons. –  Feb 17 '14 at 16:17
1

The + operator expects numbers as it's operands. If you give it strings then they are invalid numbers. So it is technically correct, the best kind of correct.

Welcome to Oracle. :D

Andrew Brennan
  • 333
  • 1
  • 9