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 nullend AS VARCHAR(120)) AS NOTESFROM [db1].[DBO].[tb1] calINNER 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 NOTE1256 This text is 147 characters long adflkajdsfladsjflkajsdflkajsdlkjflaksjdflakjdsf;lkajds;lkf;jads;lkfjsdlkfjasdlkfadskfhakdsfhaslkdjhf1256 2: BREAKfkajdshfa1260 This text is 96 characters long adflkajdsfladsjflkajsdflkajsdlkjflaksjdflakjdsf;lkajds;lkf;jads;1582 This text is 284 characters long adflkajdsfladsjflkajsdflkajsdlkjflaksjdflakjdsf;lkajds;lkf;jads;dsahfkjalhdskfjan1582 BREAKjvakjhgkjfdgjahdskjfhdsakjfhkjdsahfkjasdhfkjhdsakjfhsadkjfhdsakjfhadsfjsadlkfjaldskjf;lksadjflkajds1582 BREAKsa;lkfjdsa;lkfjalkdsjflkdsajflkdsajflkadsjflkajdsf;lkdjf;lakj*/ |
|
|
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; oruse CASE just to add each relevant segment to the result. |
|
|
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? |
|
|
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 NOTESFROM [db1].[DBO].[tb1] calINNER JOIN [db1].[dbo].[tb2] cConv ON cConv.CFSID = cal.CFSIDINNER 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)) |
|
|
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 |
|
|
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. |
|
|
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. |
|
|
|