SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Note is longer than field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cstokes91
Yak Posting Veteran

USA
60 Posts

Posted - 01/24/2013 :  11:23:09  Show Profile  Reply with Quote
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

USA
60 Posts

Posted - 01/24/2013 :  11:46:42  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
414 Posts

Posted - 01/24/2013 :  11:48:52  Show Profile  Reply with Quote
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

USA
60 Posts

Posted - 01/24/2013 :  11:53:53  Show Profile  Reply with Quote
@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
Constraint Violating Yak Guru

USA
414 Posts

Posted - 01/24/2013 :  11:54:17  Show Profile  Reply with Quote


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
Constraint Violating Yak Guru

USA
414 Posts

Posted - 01/24/2013 :  12:04:34  Show Profile  Reply with Quote
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

USA
60 Posts

Posted - 01/24/2013 :  12:21:36  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
414 Posts

Posted - 01/24/2013 :  12:33:48  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000