using CAST on a case expression
Hi there,
The following SQL fails to execute, the reason being once I try to CAST it to varchar2, I get the error message
ORA-00905; missing keyword. if the cast function is taken out the SQL runs successfully. Can someone spot where I am getting the wrong end of the stick and help?
Thanks.
*****
create table tmp_test2 as
select cast(case trim(nvl(b_service_company,'x'))
when 'x' then trim(trim(b_service_fname)||trim(b_service_lname))
when '.' then trim(trim(b_service_fname)||trim(b_service_lname))
else b_service_company
END b_service_company as varchar2(168)), b_service_fname, b_service_lname,
cast(case trim(nvl(b_service_company,'x'))
when 'x' then trim(trim(service_fname)||trim(service_lname))
when '.' then trim(trim(service_fname)||trim(service_lname))
else service_company
END service_company as varchar2(168)), service_fname, service_lname
from tmp_test
****