January 27, 2009 at 2:23 pm
Hello experts, I am new to these forums so please take it easy on me ;}
SQL Server 2000
I have a question regarding a piece of code I inherited from a former colleague,In which there is a UNION ALL that for one of the fields selected has this piece of code.
ISNULL((SELECT TOP 1 m.strLineID FROM #tbl_tmp_FullMap m WHERE m.strNCRAccount = LEFT(p.gl_exp_acct,4)), '15e') AS strLineID
Within this logic I need to incorporate, and this is in my minds eye, something similar to this.
CASE strLineID
WHEN '05a' THEN '5a'
ELSE strLineID
END AS strLineID
I have been attempting various ways for the last couple hours, but figured I would post(since I have been lurking here for a few months now anyways :]) and see how others would accomplish this.
Thanks for any help offered.
January 27, 2009 at 2:34 pm
For some reason my edit isn't displaying. What I meant to say was the closest I have come is this
,(SELECT TOP 1 CASE m.strLineID
WHEN NULL THEN '15e'
WHEN '05a' THEN '5a'
ELSE m.strLineID
END AS strLineID
FROM #tbl_tmp_FullMap m WHERE m.strNCRAccount = LEFT(p.gl_exp_acct,4)
How can I check for a NULL value there?
January 27, 2009 at 2:45 pm
What you need to do is use the "complex" case statement, instead of the "simple" one.
The one you're using, where you have the column name right after the word "case", can only test for equality. Since you can't "= null", what you need to do is test for "is null" instead.
You can do that with this kind of syntax:
SELECT TOP 1
CASE
WHEN m.strLineID is NULL THEN '15e'
WHEN m.strLineID = '05a' THEN '5a'
ELSE m.strLineID
END AS strLineID
FROM #tbl_tmp_FullMap m
WHERE m.strNCRAccount = LEFT(p.gl_exp_acct,4)
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2009 at 3:02 pm
Works great,Thanks for the input!
January 27, 2009 at 4:37 pm
Just a note for the future. Please post SQL 2000-related questions in the SQL 2000 forums
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply