sql - Cannot search in all columns of differing types -

this looks old question it's not. couldn't find answer on or elsewhere.

i have table:

n (number) | s (varchar2) ------------------------- 200        | string 201        | other string

i can cast number n string , select without problems so:

select n t; select to_char(n) t;

but need search in columns string value. i've tried (and works generally, meaning db 11g) unpivot:

select * t unpivot (anywhere col in (...));

however, not work mixing data types (numbers , character-based types such varchar2). is:

ora-01790: expression must have same datatype corresponding expression

that's ok, understand , solution obvious. cast numbers characters. doesn't work either. immediate solution:

select * t unpivot (anywhere col in (s, to_char(n)));                                                         ^

i unexpected error:

ora-00917: missing comma

the problem here to_char not interpreted function @ all, thought column name s or n.

so, how can use unpivot mixed data types or how can cast columns varchar2 or how else can solve problem?

try using:

select *    (select to_char(n) n, s t)  unpivot (anywhere col in (...));

Brant