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 2008 Forums
 Transact-SQL (2008)
 Note is longer than field

Author  Topic 

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-24 : 11:23:09
Hey,

I am having some issues with inserting data into a certain column... I am doing a insert/select and it is going from a text type to a varchar(120). Within the insert/select I need to get it to split the column up into several notes tied to the same primary key. Here is the code I have so far but the problem is that the case picks up the first statement and then stops running... I am thinking this is something simple so I am not providing test data or expected output but if it is needed I will gladly provide some.


INSERT INTO [db2].[dbo].[tb1]
(
INCIDENTNO
,NOTE

)

SELECT
cConv.INCIDENTNO AS INCIDENTNO
, CAST(
case
when len(cast(alertinfo as varchar(max)))>1 then (replace(cast(ALERTINFO as NVARCHAR(MAX)), (CHAR(13)+CHAR(10)),' '))
when LEN(cast(alertinfo as varchar(max)))>120 then ('2: '+ SUBSTRING((replace(cast(ALERTINFO as NVARCHAR(MAX)), (CHAR(13)+CHAR(10)),' ')), 121, 117))
when LEN(cast((replace(cast(ALERTINFO as NVARCHAR(MAX)), (CHAR(13)+CHAR(10)),' ')) as varchar(max)))>238 then ('3: ' + SUBSTRING(AlertInfo, 238, 117))
else null
end

AS VARCHAR(120)) AS NOTES


FROM [db1].[DBO].[tb1] cal
INNER JOIN [db1].[dbo].[tb2] cConv ON cConv.CFSID = cal.CFSID


cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-24 : 11:46:42
Here is some sample data and expected output... I figured it might help so I might as well get it over with ;)


-- Consumable test data
--Note that tb1 should only have data inserted into it FROM the data in tb2

CREATE TABLE #tb1
(incidentno INT, note varchar(120));

CREATE TABLE #tb2 (cfsid INT, alertInfo text);

INSERT INTO #tb2 VALUES
(1256,'This text is 147 characters long adflkajdsfladsjflkajsdflkajsdlkjflaksjdflakjdsf;lkajds;lkf;jads;lkfjsdlkfjasdlkfadskfhakdsfhaslkdjhfBREAKfkajdshfa'),
(1260,'This text is 96 characters long adflkajdsfladsjflkajsdflkajsdlkjflaksjdflakjdsf;lkajds;lkf;jads;'),
(1582,'This text is 284 characters long adflkajdsfladsjflkajsdflkajsdlkjflaksjdflakjdsf;lkajds;lkf;jads;dsahfkjalhdskfjanBREAKjvakjhgkjfdgjahdskjfhdsakjfhkjdsahfkjasdhfkjhdsakjfhsadkjfhdsakjfhadsfjsadlkfjaldskjf;lksadjflkajdsBREAKsa;lkfjdsa;lkfjalkdsjflkdsajflkdsajflkadsjflkajdsf;lkdjf;lakj')

/* EXPECTED OUTPUT (The breaks are just rough estimates on where it should break and is not exact)
INCIDENTNO NOTE
1256 This text is 147 characters long adflkajdsfladsjflkajsdflkajsdlkjflaksjdflakjdsf;lkajds;lkf;jads;lkfjsdlkfjasdlkfadskfhakdsfhaslkdjhf
1256 2: BREAKfkajdshfa
1260 This text is 96 characters long adflkajdsfladsjflkajsdflkajsdlkjflaksjdflakjdsf;lkajds;lkf;jads;
1582 This text is 284 characters long adflkajdsfladsjflkajsdflkajsdlkjflaksjdflakjdsf;lkajds;lkf;jads;dsahfkjalhdskfjan
1582 BREAKjvakjhgkjfdgjahdskjfhdsakjfhkjdsahfkjasdhfkjhdsakjfhsadkjfhdsakjfhadsfjsadlkfjaldskjf;lksadjflkajds
1582 BREAKsa;lkfjdsa;lkfjalkdsjflkdsajflkdsajflkadsjflkajdsf;lkdjf;lakj

*/
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-01-24 : 11:48:52
Yes; CASE always stops at the first match, and only one condition from any given CASE is ever processed.

You need to either:
check lens in descending order, and handle the entire string for each WITH; or
use CASE just to add each relevant segment to the result.
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-24 : 11:53:53
@Scott

Can you have multiple separate case statements within one column (in this situation note) where it identifies it needs to be separate records?
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-01-24 : 11:54:17


SELECT
cConv.INCIDENTNO AS INCIDENTNO
, SUBSTRING(CAST(alertinfo AS varchar(max)), (note# - 1) * 120 + 1, 120) AS NOTES
FROM [db1].[DBO].[tb1] cal
INNER JOIN [db1].[dbo].[tb2] cConv ON cConv.CFSID = cal.CFSID
INNER JOIN (
SELECT 1 AS note# UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 --...
) AS note#s ON
note# BETWEEN 1 AND CEILING(LEN(alertinfo / 120.0))
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-01-24 : 12:04:34
Yes, you can have multiple CASEs. Such are often used to do a controlled concatenation of several values.

I've posted sample code more specific to your case above.

But in a situation where the maximum value needed to be tested in one case, you always test for the high values first. For example:


SELECT CASE
WHEN Age >= 65 THEN '65+'
WHEN Age >= 55 THEN '55 - 64'
WHEN Age >= 45 THEN '45 - 54'
--etc.
END AS Age_Description
Go to Top of Page

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-01-24 : 12:21:36
That makes sense... There is a datetime field that is going to be converted too... I didn't mention it because I didn't think it was relevant. Now they are wanting to add 1 second to each concatenated record so that the second note has +1 sec, the third note has +2 etc. Is that possible with that script?

My thought is that I am going to just make it simple and have several insert/select statements and for the alertinfo have the first one for only LEN(cast(alertinfo as varchar(max))) <120 then the next one throw a substring and only grab the ones that are >120 and grab the field then add +1 sec to the datetime field.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-01-24 : 12:33:48
quote:
Originally posted by cstokes91

That makes sense... There is a datetime field that is going to be converted too... I didn't mention it because I didn't think it was relevant. Now they are wanting to add 1 second to each concatenated record so that the second note has +1 sec, the third note has +2 etc. Is that possible with that script?




Yeah, that's super-easy with the code above.

Just do a DATEADD(SECOND, (note# -1), <note_datetime>) as part of the INSERT to the notes table.


Go to Top of Page
   

- Advertisement -