trouble with Coalesce - SQL Server Forums

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

SQL Server Development (2000)

trouble with Coalesce

Author

Topic

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-02-15 : 12:05:46

I'm having trouble with Coalesce in building a comma, delimited string. Basically, I'm building a table with 2 key columns, a major_class and the string of minor classes associated with each major_class.

Here's the code:

 

SET NOCOUNT ON

TRUNCATE TABLE TBL_UMHS_FGP_DRUG_INDEX --must truncate to reset counter
INSERT INTO TBL_UMHS_FGP_DRUG_INDEX (major_ctg, major_ctg_code)
	SELECT major_class_desc, major_class FROM outpt_drug_costs_majorclasses
	ORDER BY major_class_sortorder, major_class_desc

DECLARE @minorctgstring varchar(7000)
DECLARE @iRow2 int
DECLARE @iMax2 int
SELECT @iMax2 = count(*) from TBL_UMHS_FGP_DRUG_INDEX
SELECT @iRow2 = 1
While @iRow2 < @iMax2
BEGIN
--build the csv string
SELECT @minorctgstring = null
Select @minorctgstring = Coalesce(@minorctgstring + ', ','')+ minor_class_desc
FROM outpt_drug_costs_minorclasses
WHERE 
major_class_code = (SELECT major_ctg_code FROM TBL_UMHS_FGP_DRUG_INDEX WHERE sys_id = @iRow2)
SELECT @minorctgstring
--UPDATE THE TABLE
UPDATE TBL_UMHS_FGP_DRUG_INDEX
	SET minor_ctg_string = @minorctgstring
	WHERE sys_id = @iRow2
SELECT @iRow2 = @iRow2 + 1
END

This works great except the last value for the minor_ctg_string is always Null and not the string it's supposed to be. Any ideas how to make this code finish the last row?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-15 : 12:17:09

Change this line:

SELECT @minorctgstring = null

...to:

SELECT @minorctgstring = ''

The problem isn't with COALESCE, it's with the + operator. Any concatenation of a Null value will give a null result.

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-02-15 : 12:21:13

Thanks - but that causes the strings to look like

, Other Antidepressants, SSRI Antidepressants, Tricyclic Antidepressants

I changed the line

While @iRow2 < @iMax2

to

While @iRow2 <= @iMax2

and that seems to work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources