fresh eyes on simple case problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

fresh eyes on simple case problem

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-08-15 : 11:50:38

Hi,
can anybody see why this query is producing NULLS for The Case statement when p3 and p2 are NULL ?

SELECT dbo.opencall.callref,
dbo.opencall.probcode,
p1.descx, p2.descx
AS p2,
p3.descx AS p3,
p4.descx AS p4,
CASE
WHEN p4.descx IS NOT NULL THEN p1.descx + '-' + p2.descx + '-' + p3.descx + '-' + p4.descx
WHEN p4.descx IS NULL THEN p1.descx + '-' + p2.descx + '-' + p3.descx
WHEN p3.descx IS NULL THEN p1.descx + '-' + p2.descx
WHEN p2.descx IS NULL THEN p1.descx
END AS ProblemCode

FROM dbo.opencall
LEFT OUTER JOIN dbo.probcode p1 ON SUBSTRING(dbo.opencall.probcode, 1, 4) = p1.code

LEFT OUTER JOIN dbo.probcode p2
ON SUBSTRING(dbo.opencall.probcode, 6, 4) = p2.code
AND SUBSTRING(dbo.opencall.probcode, 1, 4) = p2.parentcode

LEFT OUTER JOIN dbo.probcode p3
ON SUBSTRING(dbo.opencall.probcode, 11, 4) = p3.code
AND SUBSTRING(dbo.opencall.probcode, 6, 4) = p3.parentcode

LEFT OUTER JOIN dbo.probcode p4
ON SUBSTRING(dbo.opencall.probcode, 16, 4) = p4.code
AND SUBSTRING(dbo.opencall.probcode, 11, 4) = p4.parentcode

The data looks like :

3554 RQST-WB00 REQUESTS Web NULL NULL NULL
3555 FLT0-SFTW-PPLC FAULT Software Error Application Error NULL FAULT-Software Error-Application Error
3556 SCRT-DNLF SECURITY Denial Of Service NULL NULL NULL
3557 FLT0-CNNC-RSPN-VPN/ FAULT Connectivity Response/Performance Issues VPN/Dial Up FAULT-Connectivity-Response/Performance Issues-VPN/Dial Up
3558 RQST-WB00 REQUESTS Web NULL NULL NULL
3559 SGNQ USAGE ENQUIRY NULL NULL NULL NULL

Thank you for any help#.Jamie

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-15 : 11:59:16

You need to reorder your CASE statement conditions so that the p2 condition is first, the p3 condition next, etc. This is because the CASE statement is evaluated sequentially (it looks at the first condition, if false it looks at the next one, etcetera until it hits a true condition).

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-08-15 : 11:59:49

Because p4.descx is not null?

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-08-16 : 04:29:57

brillient, thank you nosepicker. I never knew that about case statements, that explains alot !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources