August 14, 2008 at 2:25 pm
If i run the following query on a NULL value column, it does not add any values.
UPDATE PRO
SET Prokw2=
CASE
WHEN Len(Prokw2 + ', HL7') <=240
THEN Prokw2 + ', HL7'
END
FROM PRO
If Prokw2 already has a value it will add more data.
Why is this?
Thanks
August 14, 2008 at 2:47 pm
There's a setting that deals with the concatenation of NULL with any other value.
SET CONCAT_NULL_YIELDS_NULL { ON | OFF }
Setting this differently will, I believe, yield the results you desire.
August 14, 2008 at 2:59 pm
Thanks !
August 14, 2008 at 3:23 pm
IsNull or Coalesce are the commands for that kind of thing:
UPDATE PRO
SET Prokw2=
CASE
WHEN Len(Prokw2 + ', HL7') <=240
THEN Prokw2 + ', HL7'
END
FROM PRO
becomes:
UPDATE PRO
SET Prokw2=
CASE
WHEN Len(Prokw2 + ', HL7') <=240
THEN isnull(Prokw2 + ', HL7', 'HL7')
END
FROM PRO
That will also make it so you don't have a comma at the beginning of the field.
- 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply