Convert decode and Instr to postgres

Solution for Convert decode and Instr to postgres
is Given Below:

I am migrating oracle to aurora postgres on rds. I came across below code snippt ,

SELECT decode(Instr(A.TECH_TYPE,'ATM'),1,Decode(y.UMAT_ID,Null,Decode(A.BANDWIDTH_FORECAST,Null,0,A.BANDWIDTH_FORECAST),0), Decode(x.Number_Forecast,Null,Decode(A.BANDWIDTH_FORECAST,Null,0,A.BANDWIDTH_FORECAST),x.Number_Forecast)) As Forecas FROM TABLE A , TABLE X.

How do i convert decode and Instr with the above query to postgres compactable form ?

The only instr is checking the string starts with ‘ATM’ so just use like instead.

decode can be replaced as case statements.

That said, this code could be made much easier to read (in both Oracle and Postgres) by something like this (not tested)

select 
  case when A.TECH_TYPE like 'ATM%' then
            case when y.UMAT_ID is null then coallesce(A.BANDWIDTH_FORECAST,0) else 0 end
       else case when x.Number_Forecast is null then coallesce(A.BANDWIDTH_FORECAST,0) else x.Number_Forecast end
  end as Forecas
FROM TABLE A , TABLE X

this won’t work though as you have a y alias in your select but this should give you the idea.

If you need instr for anything other then the start of the string then strpos is probably what you need