February 16, 2009 at 8:22 am
Hi, I'm trying to run a query below and kept on getting an error. I stared at it quite sometime now and could not figure out what's wrong with it. Please help me to find the problem and fix it. Thank you.
update tblDrawings
set TypeCode = case SheetNumber when charindex('-',SheetNumber) <> 0 then left(SheetNumber, charindex('-',SheetNumber)-1)
Dong
February 16, 2009 at 8:28 am
...You missed the ELSE & END part of the CASE statement, it should be like...
UPDATEtblDrawings
SETTypeCode = ( CASE SheetNumber WHEN CHARINDEX( '-', SheetNumber ) <> 0 THEN LEFT( SheetNumber, CHARINDEX( '-', SheetNumber ) - 1 ) ELSE '' END )[/b]
--Ramesh
February 16, 2009 at 8:44 am
Thank you for your reply. The script you posted gave me an error message that I kept on getting. Here is the error message.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '<'.
I'm not sure why I'm getting this.
February 16, 2009 at 8:47 am
just missing the ELSE/END:
update tblDrawings
set TypeCode = case SheetNumber
when charindex('-',SheetNumber) <> 0 then left(SheetNumber, charindex('-',SheetNumber)-1)
--ELSE ADefaultValue?
END
Lowell
February 16, 2009 at 9:13 am
Dong Lee (2/16/2009)
Thank you for your reply. The script you posted gave me an error message that I kept on getting. Here is the error message.Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '<'.
I'm not sure why I'm getting this.
...Just change it to
UPDATE tblDrawings
SET TypeCode = ( CASE WHEN CHARINDEX( '-', SheetNumber ) <> 0 THEN LEFT( SheetNumber, CHARINDEX( '-', SheetNumber ) - 1 ) ELSE '' END )
--Ramesh
February 16, 2009 at 9:21 am
I got another error this time.
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
February 16, 2009 at 9:24 am
Thank you for your reply.
I got the same error message that posted earlier.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '<'.
I ran the script as it is and then I ran it with a null defalut value. I got the same result.
February 16, 2009 at 9:35 am
Dong Lee (2/16/2009)
Thank you for your reply.I got the same error message that posted earlier.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '<'.
I ran the script as it is and then I ran it with a null defalut value. I got the same result.
I tested it, and it should work (same update as previously posted)...
DECLARE @tblDrawings TABLE ( SheetNumber VARCHAR(10), TypeCode VARCHAR(5) )
INSERT@tblDrawings( SheetNumber )
SELECT'1-Sheet2'
UPDATE @tblDrawings
SET TypeCode = ( CASE WHEN CHARINDEX( '-', SheetNumber ) <> 0 THEN LEFT( SheetNumber, CHARINDEX( '-', SheetNumber ) - 1 ) ELSE '' END )
SELECT * FROM @tblDrawings
--Ramesh
February 16, 2009 at 10:04 am
Ramesh,
Last reply I posted was for Lowell. I thought the reply would go right underneath the one replied to. I ran your sample code and it worked fine, but it did not work when I applied to my database. Which tells me the code is correct but then the problem reside in my database.
I'm posting the script and the error message here.
SheetNumber is varchar(50) and TypeCode is varchar(5).
The SheetNumber data field has none hyponed numbers(like xxxx), null values (NULL), and hyponed numbers (like x-xxxx)
update tblDrawings
set TypeCode = ( CASE WHEN CHARINDEX( '-', SheetNumber ) <> 0 THEN LEFT( SheetNumber, CHARINDEX( '-', SheetNumber ) - 1 ) ELSE '' END )
Server: Msg 8152, Level 16, State 9, Line 7
String or binary data would be truncated.
The statement has been terminated.
February 16, 2009 at 3:16 pm
what's the data type of the "Typecode" column? I suspect that some of your rows have the dash in the werong location, causing you to try to put 10 characters into a 5-character field column thus getting the truncation error (numbers above are just to illustrate the point).
perhaps if you run the update statement as a SELECT instead, you may find that one of the entries is longer than the others......
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 16, 2009 at 3:20 pm
Also - hedging your bet so that the CHARINDEX always returns a valid value to the LEFT function might make the error go away.
update tblDrawings
set TypeCode = (
CASE
WHEN CHARINDEX( '-', SheetNumber ) <> 0
THEN LEFT( SheetNumber, CHARINDEX( '-', SheetNumber
+'-' --NOW you're sure that the sheetnumber actually has a dash in it.
) - 1 ) ELSE '' END )
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 16, 2009 at 11:30 pm
Matt Miller (2/16/2009)
Also - hedging your bet so that the CHARINDEX always returns a valid value to the LEFT function might make the error go away.
update tblDrawings
set TypeCode = (
CASE
WHEN CHARINDEX( '-', SheetNumber ) <> 0
THEN LEFT( SheetNumber, CHARINDEX( '-', SheetNumber
+'-' --NOW you're sure that the sheetnumber actually has a dash in it.
) - 1 ) ELSE '' END )
Since there is a WHEN condition that checks for the existence of '-' before doing a LEFT on it, so there is really no chance of getting errors on LEFT function.
Or you may be thinking of something like....
UPDATE tblDrawings SET TypeCode = LEFT( SheetNumber, CHARINDEX( '-', SheetNumber + '-' ) - 1 )
For the OP, as Matt said, you first need to check the data by doing a select on it, to make sure you don't exceed the limit...
SELECTSheetNumber,
LEFT( SheetNumber, CHARINDEX( '-', SheetNumber + '-' ) - 1 ) AS NewTypeCode,
LEN( LEFT( SheetNumber, CHARINDEX( '-', SheetNumber + '-' ) - 1 ) ) AS ExpectedLength
FROMtblDrawings
ORDER BY ExpectedLength DESC
--Ramesh
February 17, 2009 at 8:46 am
Your prediction was right. I found all different formats of sheet number after running your select statement. Thank you so much to you all and especially to Ramesh.
Dong
February 17, 2009 at 11:27 am
I dont know about your requirement, In case If you are OK with ignoring the data which exceeds the TypeCode length.
Here is your update statement
DECLARE @tblDrawings TABLE ( SheetNumber VARCHAR(50), TypeCode VARCHAR(5) )
INSERT @tblDrawings( SheetNumber )
SELECT '1-Sheet1'
INSERT @tblDrawings( SheetNumber )
SELECT '1312321-Sheet2'
UPDATE @tblDrawings
SET TypeCode = ( CASE WHEN CHARINDEX( '-', SheetNumber ) <> 0 THEN LEFT(LEFT( SheetNumber, CHARINDEX( '-', SheetNumber ) - 1 ),5) ELSE '' END )
SELECT * FROM @tblDrawings
Or Change the TypeCode column length to more suitable value.
Or Fix the data whose TypeCode value is SheetNumber field is exceeding more than 5 characters
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply