Issue with CASE

Question

Post reply

Issue with CASE

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
May 24, 2011 at 4:30 am

#96203

I have the following TSQL script -
SELECT dbo.DW_SALESORDERS_F.Reference AS [Soh.Reference]
,dbo.DW_USERS_D.UserShortName AS [Usr.UserShortName]
,dbo.DW_SALESORDERS_F.CreatedDate AS [Soh.CreatedDate]
,DATEPART(YYYY, dbo.DW_SALESORDERS_F.CreatedDate) AS [Soh.CreatedYear]
,DATEPART(WK, dbo.DW_SALESORDERS_F.CreatedDate) AS [Soh.CreatedWeek]
,dbo.DW_SALESORDERS_F.Note AS [Soh.Note]
,CASE WHEN Note LIKE '%%-%' THEN 'Contact Added'
ELSE 'No Valid Contact'
END AS [Cal.Contact]
,CASE WHEN LEFT (Note,1) = 'C' THEN 'TEST'
ELSE 'TEST1' END AS 'SQL'
,CASE WHEN dbo.DW_CUSTOMERS_D.ParentCustomerID = '55' THEN 'Hyde Housing'
WHEN dbo.DW_CUSTOMERS_D.ParentCustomerID = '23452' THEN 'Hyde In Touch'
ELSE 'Hyde Vale' END AS ParentClient
FROM dbo.DW_SALESORDERS_F
INNER JOIN
dbo.DW_USERS_D
ON dbo.DW_SALESORDERS_F.CreatedByID = dbo.DW_USERS_D.UserUID
INNER JOIN
dbo.DW_CUSTOMERS_D
ON dbo.DW_SALESORDERS_F.CustomerID = dbo.DW_CUSTOMERS_D.CustomerUID
WHERE (dbo.DW_CUSTOMERS_D.ParentCustomerID IN ('55','23452','24430' ))
AND (dbo.DW_SALESORDERS_F.CreatedDate >= CONVERT(DATETIME, '2011-04-01 00:00:00', 102))
AND (dbo.DW_SALESORDERS_F.CreatedDate <= CONVERT(DATETIME, '2012-03-31 00:00:00', 102))
AND (dbo.DW_SALESORDERS_F.SalesOrderCategory = N'RR1')
The issue I am having is with the following part of the SELECT statement
,CASE WHEN LEFT (Note,1) = 'C' THEN 'TEST'
ELSE 'TEST1' END AS 'SQL'
At present when the note field begins with C it comes back with TEST.
However I want to know when the third character is a - ? I have therefore changed the CASE statement to the following -
,CASE WHEN LEFT (Note,3) = '-' THEN 'TEST'
ELSE 'TEST1' END AS 'SQL'
but all my results come back as TEST1 when i can see that there are instances when a - is the third character.
Please advise.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Change LEFT to SUBSTRING.

LEFT(expression, 3) returns the first 3 characters of the expression, not the third character.

Use SUBSTRING(expression, 3, 1) for the third character.

Hope this helps

Gianluca

-- Gianluca Sartori

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

Fantastic - Never come across the SUBSTRING expression.

Therefore if I wanted to say if the - was the third or forth character, how would you express that?

Thanks ever so much

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You could use CHARINDEX(sought_char, expression, start_index):

WITH TestStrings (s) AS (

SELECT 'aaa-'

UNION ALL

SELECT 'bb-'

UNION ALL

SELECT 'ccccc-'

)

SELECT *

FROM TestStrings

WHERE CHARINDEX('-',s,3) IN (3,4)

This code returns 'aaa-' (fourth character) and 'bb-' (third character).

-- Gianluca Sartori