Coalesce in Sql to Case Statement in Oracle
I Have This Sample Temp Table. I Was Using the Coalesce in Sql Sever. I Do Not Have Access Right to Create tableS in Oracle. Is There a Case Statement in...
I have this sample temp table. I was using the COALESCE in SQL sever. I do not have access right to create tableS in Oracle. Is there a Case statement in Oracle that works as COALESCE in SQL server?
CREATE TABLE #TEMP
(
ID INT,
FIELD1 VARCHAR (5),
FIELD2 VARCHAR (5),
FIELD3 VARCHAR (5)
)
INSERT INTO #TEMP VALUES('555','CVS','E','CIA')
SELECT
ID,
COALESCE(FIELD1 + '_' + FIELD2 + '_' + FIELD3, FIELD2 + '_' + FIELD1) AS STORE
FROM #TEMP
Basically, I want to replace the COALESCE from above my query to Case Statement in Oracle.
edit: I did use this Field1 ||'_' ||Field2||'_'||Field3 AS Store in Oracle but the result gives me the extra underscore _ from Field3 which I don't want to see the underscore.
1 Answer
COALESCE(FIELD1 || '_' || FIELD2 || '_' || FIELD3, FIELD2 || '_' || FIELD1)
Will only ever return the first term since even if all 3 fields are NULL the expression will evaluate to __ which is not NULL. This is the equivalent of:
SELECT TRIM(
LEADING '_' FROM
FIELD1
|| NLV2( FIELD2, '_' || FIELD2, NULL )
|| NLV2( FIELD3, '_' || FIELD3, NULL )
)
FROM DUAL;
or
SELECT TRIM(
LEADING '_' FROM
FIELD1
|| CASE WHEN FIELD2 IS NOT NULL THEN '_' || FIELD2 END
|| CASE WHEN FIELD3 IS NOT NULL THEN '_' || FIELD3 END
)
FROM DUAL;
If you want to reverse Fields 1 & 2 when Field 3 is NULL then:
SELECT TRIM(
BOTH '_' FROM
CASE
WHEN FIELD3 IS NOT NULL
THEN FIELD1
|| CASE WHEN FIELD2 IS NOT NULL THEN '_' || FIELD2 END
|| '_' || FIELD3
ELSE FIELD2 || '_' || FIELD1
END
)
FROM DUAL;
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
