Oracle ORA-00911, query with several coalesce

Question

I have this query

Select * from mytable WHERE  coalesce(f1,n' ') = ? and  coalesce(f2,n' ') = ? and  coalesce(f3,n' ') = ? and  coalesce(f4,n' ') = ?

f1, f2, f3 and f4 are nvarchar2 fields

I call the query from php with the value array(0,0,0,0)

I get:

ORA-00911: invalid character

When I reduce the query to one coalesce statement, like

Select * from mytable WHERE  coalesce(f1,n' ') = ?

called with array(0),

it works.

When I change the values in the array from 0 to whatever, it does not change anything. What is wrong with my query?

You haven't shown it above, but does your statement end with a semicolon (`;`)? If it does, [remove it](http://stackoverflow.com/questions/27987882/how-can-i-solve-ora-00911-invalid-character). — Phylogenesis, Oct 24 '16 at 14:14
i use adodb as a a database abstraction, so the real query must be hidden in the library — Lokomotywa, Oct 24 '16 at 14:21

score 0 · Answer 1 · answered Oct 24 '16 at 14:21

0

Without your php code, I can only guess that you're calling the select statement by sending the array, instead of listing each value in the array separately.

answered Oct 24 '16 at 14:21

DCookie

42,630
11
83
92

Oracle ORA-00911, query with several coalesce

1 Answers1