当前正在执行Oracle到T-SQL语句的转换。下面是我想要转换的内容:
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
我最大的努力。我知道这很离谱,但我在写case语句方面没有太多经验:
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
发布于 2018-06-02 04:33:43
您仍然有一些特定于Oracle的项目,并且缺少一些有用的SQL Server函数。下面是我的转换尝试:
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
特定项: COALESCE()接受一组参数并返回第一个非空值。如果任何ADDR.STREET_x字段为null,则该字段和CHAR(10)的连接也将为null (除非更改ANSI_NULL默认值)。
表名中的双点:我假设DSS_ERS_STAGE是一个数据库名。如果在SQL Server中使用数据库架构名称,则需要指定第三个级别- Name.Table名称。在本例中,我假定使用默认的模式名称(dbo),虽然应该包括分隔符,但可以省略该名称。如果您使用的是不同的模式,比如'db2data',那么您的表引用将如下所示:
FROM DSS_ERS_STAGE.db2data.ES_W_USER_CONT_INF USC
希望这对你有帮助。
发布于 2018-06-02 04:24:55
您应该能够将NVL2替换为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
您可能需要连接MAX(CHAR(10)),我不确定。这不是功能问题,只是GROUP BY的语法问题。
https://stackoverflow.com/questions/50650347
复制相似问题