Generate Address Name for EIM

Many times we have to replicate the ADDR_NAME configuration/calculation for Initial load.

Below is the query to generate the exact string as it gets generated from UI. This is written in SQL Server and the inbuilt functions can be changed to replicate the same in Oracle

ADDR NAME
------------------------
SELECT
LEFT
(ADDR,
CASE
WHEN (100-CASE
WHEN STATE IS NULL
THEN 0
ELSE LEN(STATE)
END
-
CASE
WHEN CITY IS NULL
THEN 0
ELSE LEN(CITY)
END
- 4) < (LEN(ADDR))
THEN 100-CASE
WHEN STATE IS NULL
THEN 0
ELSE LEN(STATE)
END
-
CASE
WHEN CITY IS NULL
THEN 0
ELSE LEN(CITY)
END - 4
ELSE LEN(ADDR)
END)
+
CASE WHEN ADDR_LINE_2 IS NOT NULL THEN ', ' ELSE '' END
+
ISNULL(ADDR_LINE_2,'')
+
CASE
WHEN ADDR IS NOT NULL AND CITY IS NOT NULL OR STATE IS NOT NULL
THEN ', '
ELSE ''
END
+
CITY
+
CASE WHEN (STATE IS NOT NULL AND ADDR IS NOT NULL OR CITY IS NOT NULL)
THEN (CASE WHEN CITY IS NOT NULL THEN ', ' ELSE '' END)
ELSE ''
END
+
STATE
FROM dbo.S_ADDR_PER

For Oracle Replace
+ with (Double Pipe)
ISNULL with NVL
LEN with LENGTH
LEFT with SUBSTR
CASE WHEN THEN ELSE END with DECODE