I am converting SQL from Teradata to Oracle and I have a query something like the one below which works in Teradata. How can I handle a query like this in Oracle.
The first column aliased as TEST1 is being used in the case statement for the second column. This is just a small subset of my full query. There are many such instances in the query. How can something similar be done in Oracle, without having to write a stored procedure ?
Any help will be appreciated.
select CASE WHEN POV_TYPE = 'MAGICIAN' THEN POV_TYPE else POV_ABBR END as TEST1,
CASE WHEN instr(TEST1, "CIAN") > 0 THEN substr(TEST1,1,5) else 'ABC' END as TEST2
from TABLENAME