CASE. Is this a legitimate SQL?

Question

Post reply

CASE. Is this a legitimate SQL?

JMI

SSChampion

Points: 11831
More actions
February 3, 2009 at 3:10 pm

#128445

Is this a legitimate SQL?
SELECT
ci.IG_SRC_SYS_KEY = CASE
WHEN LEFT(ci.IG_CD,1) = 'D' THEN (SELECT SRC_SYS_KEY FROM SOURCE_SYSTEM_REF WHERE SRC_SYS_ID = 'DDA')
WHEN LEFT(ci.IG_CD,1) = 'G' THEN (SELECT SRC_SYS_KEY FROM SOURCE_SYSTEM_REF WHERE SRC_SYS_ID = 'GRM')
WHEN LEFT(ci.IG_CD,1) = 'O' THEN (SELECT SRC_SYS_KEY FROM SOURCE_SYSTEM_REF WHERE SRC_SYS_ID = 'OLL')
END,
ci.IG_CD
FROM
stg.STG_CUSTOMER_INTEG ci

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

JMI SSChampion Points: 11831 More actions · Answer 1

Figured out myself.

It should be:

SELECT
IG_SRC_SYS_KEY = CASE
WHEN LEFT(ci.IG_CD,1) = 'D' THEN (SELECT SRC_SYS_KEY FROM SOURCE_SYSTEM_REF WHERE SRC_SYS_ID = 'DDA')
WHEN LEFT(ci.IG_CD,1) = 'G' THEN (SELECT SRC_SYS_KEY FROM SOURCE_SYSTEM_REF WHERE SRC_SYS_ID = 'GRM')
WHEN LEFT(ci.IG_CD,1) = 'O' THEN (SELECT SRC_SYS_KEY FROM SOURCE_SYSTEM_REF WHERE SRC_SYS_ID = 'OLL')
END,
ci.IG_CD
FROM
stg.STG_CUSTOMER_INTEG ci
where
LEFT(ci.IG_CD,1) IN ('D','G','O')

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Don't think so. What happens when you try running it in SSMS?

noeld SSC Guru Points: 96590 More actions · Answer 3

riga1966 (2/3/2009)
Figured out myself.
It should be:
SELECT
IG_SRC_SYS_KEY = CASE
WHEN LEFT(ci.IG_CD,1) = 'D' THEN (SELECT SRC_SYS_KEY FROM SOURCE_SYSTEM_REF WHERE SRC_SYS_ID = 'DDA')
WHEN LEFT(ci.IG_CD,1) = 'G' THEN (SELECT SRC_SYS_KEY FROM SOURCE_SYSTEM_REF WHERE SRC_SYS_ID = 'GRM')
WHEN LEFT(ci.IG_CD,1) = 'O' THEN (SELECT SRC_SYS_KEY FROM SOURCE_SYSTEM_REF WHERE SRC_SYS_ID = 'OLL')
END,
ci.IG_CD
FROM
stg.STG_CUSTOMER_INTEG ci
where
LEFT(ci.IG_CD,1) IN ('D','G','O')

You may want to use a "JOIN" instead of all those CASEs 😉

* Noel

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

[font="Verdana"]Poor SQL Server would have been getting confused over whether you wanted a select or an update there. :D[/font]

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 5

riga1966 (2/3/2009)
SELECT ci.IG_SRC_SYS_KEY = CASE WHEN LEFT(ci.IG_CD,1) = 'D' THEN (SELECT SRC_SYS_KEY FROM SOURCE_SYSTEM_REF WHERE SRC_SYS_ID = 'DDA') WHEN LEFT(ci.IG_CD,1) = 'G' THEN (SELECT SRC_SYS_KEY FROM SOURCE_SYSTEM_REF WHERE SRC_SYS_ID = 'GRM') WHEN LEFT(ci.IG_CD,1) = 'O' THEN (SELECT SRC_SYS_KEY FROM SOURCE_SYSTEM_REF WHERE SRC_SYS_ID = 'OLL') END, ci.IG_CD FROM
stg.STG_CUSTOMER_INTEG ci

[font="Verdana"]As Noel suggested, try:

SELECT

ssr.IG_SRC_SYS_KEY,

ci.IG_CD

FROM

stg.STG_CUSTOMER_INTEG ci

INNER JOIN

SOURCE_SYSTEM_REF ssr

ON

ssr.SRC_SYS_ID = CASE LEFT(ci.IG_CD, 1) WHEN 'D' THEN 'DDA' WHEN 'G' THEN 'GRM' WHEN 'O' THEN OLL END

[/font]

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 6

Bruce W Cassidy (2/3/2009)

ssr.SRC_SYS_ID = CASE LEFT(ci.IG_CD, 1) WHEN 'D' THEN 'DDA' WHEN 'G' THEN 'GRM' WHEN 'O' THEN OLL END
[/code]
[/font]

Or if the first letter in ssr.SRC_SYS_ID describes the same way as ci.IG_CD does, you may use LEFT(ssr.SRC_SYS_ID,1) = LEFT(ci.IG_CD, 1) to replace in the second part of the join. However, you might want to consider if you want to add a truly unique id in both the tables. This asks for problems in near future.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2