Issues with STUFF

Transact-SQL
1

Hi, all! I've got the following script:

Insert into Manual.dbo.nsc
 select SSN,
 FName,
 LName , 
 
 STUFF((select ',' + n1.SchoolCode from Manual.dbo.New_Clr n1 where
 n1.SSN=n2.SSN for XML path('')),1,1,'') as school_code,
 
 STUFF((select ',' + n1.FrmDate  from Manual.dbo.New_Clr n1 where
 n1.SSN=n2.SSN for XML path('')),1,1,'') as FromDate,
 
 STUFF((select ',' + n1.ToDate  from Manual.dbo.New_Clr n1 where
 n1.SSN=n2.SSN for XML path('')),1,1,'') as ToDate
 
 from Manual.dbo.New_Clr n2 group by ssn, fname, lname

What I'm needing is (I think!) a CASE statement for each of the STUFF to do what I need it to do.

For example, the second STUFF would need to be something like this:

CASE when n1.FrmDate < 1 then n1.FrmDate='        '
else
STUFF((select ',' + n1.SchoolCode from Manual.dbo.New_Clr n1 where
 n1.SSN=n2.SSN for XML path('')),1,1,'') as school_code

So, basically, if the field in the STUFF statement is less than 1, it needs to return 8 blank spaces, but if the field is greater than 1, it needs to run that STUFF statement to be inserted into the table.

2

Something like this for your second STUFF?

STUFF(( SELECT  ',' + CASE WHEN n1.FrmDate < 1 THEN SPACE(8) ELSE n1.FrmDate END
FROM    Manual.dbo.New_Clr n1
WHERE   n1.SSN = n2.SSN
FOR XML PATH('')

A few things to consider in the above:

1.what is the data type of n1.FrmDate? Is it a numeric type? If so you will need to cast it to varchar(N).
2. If it is character type, the comparison operator will do a string sort to determine the result, and it may not always be what you expect.
3. Can n1.FrmDate be null? If so, you might end up with a null result unless you program for it.

1 Like
3

@JamesK,

Thanks for your input! Worked like a charm. I knew it was simple, I just couldn't get my CASE statement in the right spot! Thank you!

4

Is this going to implicitly CAST OK? I'm presuming that n1.FrmDate is numeric? if so then I wonder if this would be "better"?

SELECT  ',' + CASE WHEN n1.FrmDate < 1 THEN SPACE(8) ELSE 
    CAST n1.FrmDate AS varchar(20)
    END

and if not! then this perhaps?

SELECT  ',' + CASE 
    WHEN CASE n1.FrmDate AS int < 1 
    THEN SPACE(8) ELSE n1.FrmDate
    END

I'm a bit bothered that the column name FrmDate seems to imply that the column contains a date ... ?? which would presumably tie-in with wanting 8 spaces - corresponding to yyyymmdd. If it is a date, stored in an int, or a varchar, that would concern me. But I always worry about such things, outside of the actual question being asked ...

5

Also, what if n1.FrmDate is less than 8 digits (given that you are space padding with SPACE(8)) ?

6

I am thinking that n1.FrmDate is char/varchar that holds numeric values. Otherwisie the ',' + n1.FrmDate would throw a conversion error. If n1.FrmDate then contains anything that cannot be implicitly converted to INT (etc), it will again throw an error.

7

Actually

SELECT CASE WHEN 0 < 1 THEN SPACE(8) ELSE 123 END

will work because SPACE(8) will be implicitly cast to INT ... however, you'd get "0" rather that 8 spaces!!

1 Like
8

good point, K! Worth pointing out that CASE determines type in accordance with the rules of precedence

Case

Precedence

9

Well ... only provided you don't try to concatenate anything to the CASE statement!! This will break:

SELECT CASE WHEN 0 < 1 THEN SPACE(8) ELSE 123 END + 'Some Text String'
10

This whole process is extremely tedious and difficult to explain. We receive a file in which one person may have 20-30 rows of information (schools, in particular) with different dates of enrollment for the same school. For example Sally Sue attended Pacific Coast College once from 08/01/2010 to 08/01/2011 and then came back and attended from 08/01/2012 to 10/01/2012. So I run script against that file which then provides one row for Sally Sue with all of her school codes and the dates of enrollment for each school (which in her case would be 08/01/2010 - 10/01/2012).

The purpose for the SPACE(8) is because further down in the script, as it's formatting the info in the table to a fixed-width flat file for importing into our system, those spaces are necessary so that dates don't jump into the wrong places, since the delimiter it looks at is a ','.

The type is not set to date because I don't need it to be. I don't use this information in any way other than to prepare a fixed-width file.

11

Thanks for the explanation. RealWorld is often different to TheoreticalWOrld of course!

I'm sure you are aware of this, but one reason for using a DATE datatype is that it would validate that Year/Month/Day combinations were valid dates.

12

Oh, yes, of course. It's worth noting that the source of the information has already done the validation checks. Additionally, if this information were going directly into my database as live data, I absolutely would be cautious of the datatype.

1 Like