Oracle SQL to T-SQL conversion with NVL2 function to Case Statement

Question

Currently doing an Oracle to T-SQL statement conversion. Here is what I'm trying to convert:

SELECT USC.USER_ID
  ,NVL2 (MAX(STREET_1), MAX(STREET_1) || CHR (10), '')
|| NVL2 (MAX(STREET_2), MAX(STREET_2) || CHR (10), '')
|| NVL2 (MAX(STREET_3), MAX(STREET_3) || CHR (10), '')
|| NVL2 (MAX(STREET_4), MAX(STREET_4) || CHR (10), '') AS STREETS
  ,MAX(ADDR.CITY) AS CITY
  ,MAX(ADDR.POSTAL_CODE) AS POSTAL_CODE
  ,MAX(ADDR.STATE_ID) AS STATE_ID
  ,MAX(ADDR.COUNTRY_ID) AS COUNTRY_ID
FROM ES_W_USER_CONT_INF USC
LEFT JOIN DW_ERSDB_LDSJOBS_ADDRESS ADDR ON (USC.ADDRESS_ID = 
ADDR.ADDRESS_ID)
GROUP BY USC.USER_ID

My best attempt. I realize it's way off but I don't have much experience with writing case statements:

SELECT USC.USER_ID
,CASE 
 WHEN ADDR.STREET_1 IS NOT NULL THEN ADDR.STREET_1 || CHAR (10) 
 WHEN ADDR.STREET_1 IS NULL THEN '' 
 WHEN ADDR.STREET_2 IS NOT NULL THEN ADDR.STREET_2 || CHAR (10) 
 WHEN ADDR.STREET_2 IS NULL THEN '' 
 WHEN ADDR.STREET_3 IS NOT NULL THEN ADDR.STREET_3 || CHAR (10) 
 WHEN ADDR.STREET_3 IS NULL THEN '' 
 WHEN ADDR.STREET_4 IS NOT NULL THEN ADDR.STREET_4 || CHAR (10) 
 WHEN ADDR.STREET_4 IS NULL THEN '' 
 END
,MAX(ADDR.CITY) AS CITY
,MAX(ADDR.POSTAL_CODE) AS POSTAL_CODE
,MAX(ADDR.STATE_ID) AS STATE_ID
,MAX(ADDR.COUNTRY_ID) AS COUNTRY_ID
FROM DSS_ERS_STAGE.ES_W_USER_CONT_INF USC
LEFT JOIN DSS_ERS_STAGE.ES_W_ADDRESS ADDR ON (USC.ADDRESS_ID = 
ADDR.ADDRESS_ID)
GROUP BY USC.USER_ID

I'm pretty confused. Those two queries look the same, and both look like they'd work just fine in Oracle and SQL Server. I also don't see any usage of NVL2() — Error_2646, Jun 01 '18 at 20:19

score 2 · Accepted Answer · answered Jun 01 '18 at 20:33

You still have some Oracle specific items, and are missing some useful SQL Server functions. Here is my try at conversion:

 SELECT USC.USER_ID,
   COALESCE(ADDR.STREET_1 + CHAR (10), '') +
   COALESCE(ADDR.STREET_2 + CHAR (10), '') +
   COALESCE(ADDR.STREET_3 + CHAR (10), '') +
   COALESCE(ADDR.STREET_4 + CHAR (10), '') AS Streets,
  ,MAX(ADDR.CITY) AS CITY
  ,MAX(ADDR.POSTAL_CODE) AS POSTAL_CODE
  ,MAX(ADDR.STATE_ID) AS STATE_ID
  ,MAX(ADDR.COUNTRY_ID) AS COUNTRY_ID
FROM DSS_ERS_STAGE..ES_W_USER_CONT_INF USC
LEFT JOIN DSS_ERS_STAGE..ES_W_ADDRESS ADDR 
  ON USC.ADDRESS_ID = ADDR.ADDRESS_ID
GROUP BY USC.USER_ID

Specific items: COALESCE() accepts a list of parameters and returns the first non-null value. If any of the ADDR.STREET_x fields are null, the concatenation of that and the CHAR(10) will also be null (unless you change the ANSI_NULL default).

Double dots in table names: I am assuming that DSS_ERS_STAGE is a Database name. If using Database Name.Table Name in SQL Server, there is a third level - Schema name - that needs to be specified. In this case, I am assuming the default Schema name (dbo) which can be omitted, though the delimiter should be included. If you were using a different schema, say, 'db2data', your table reference would look like this:

FROM DSS_ERS_STAGE.db2data.ES_W_USER_CONT_INF USC

Hope this helps you.

This is it! Thank you. I added a few columns to the group by and it works great. — PJ.SQL, Jun 01 '18 at 21:23

Error_2646 · Answer 2 · 2018-06-01T20:39:03.200

You should just be able to swap NVL2 for COALESCE

SELECT USC.USER_ID
  ,COALESCE((MAX(STREET_1) + CHAR (10), '')
 + COALESCE(MAX(STREET_2) + CHAR (10), '')
 + COALESCE(MAX(STREET_3) + CHAR (10), '')
 + COALESCE(MAX(STREET_4) + CHAR (10), '') AS STREETS
  ,MAX(ADDR.CITY) AS CITY
  ,MAX(ADDR.POSTAL_CODE) AS POSTAL_CODE
  ,MAX(ADDR.STATE_ID) AS STATE_ID
  ,MAX(ADDR.COUNTRY_ID) AS COUNTRY_ID
FROM DSS_ERS_STAGE.ES_W_USER_CONT_INF USC
LEFT JOIN DW_ERSDB_LDSJOBS_ADDRESS ADDR ON (USC.ADDRESS_ID = 
ADDR.ADDRESS_ID)
GROUP BY USC.USER_ID

You might have to concatenate MAX(CHAR(10)) I'm not sure. It's not a matter of functionality, just syntax with the GROUP BY.

Oracle SQL to T-SQL conversion with NVL2 function to Case Statement

2 Answers2