Multiple Case statement in Update

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 2005 Forums

Transact-SQL (2005)

Multiple Case statement in Update

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-08-11 : 10:25:00

Hey Guys, I’m having a syntax problem with the following code. It seems the 2nd & 3rd Case statement is causing the problem. What am I doing wrong?

UPDATE dbo.ztbl
SET
L_Code = (CASE
WHEN LEN(L_Code) = '0' then '0' + L_Code
END)

Offer = (CASE
WHEN LEN(Off) = '1' then '0000000000' + off
END)
Client_Id = CASE
WHEN LEN(Client_Id) = '2' then '20000100' + Client_Id
END
Thanx,
Trudye

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 10:28:28

[code]UPDATE dbo.ztbl
SET L_Code = CASE WHEN LEN(L_Code) = 0 then '0' ELSE L_Code END,
Offer = CASE WHEN LEN(Off) = 1 then '0000000000' + off ELSE Off END,
Client_Id = CASE WHEN LEN(Client_Id) = 2 then '20000100' + Client_Id ELSE Client_Id END
[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 10:28:56

You also forgot the ELSE parts, just in case then WHEN clause is false.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 10:31:47

quote:
Originally posted by Trudye

Hey Guys, I’m having a syntax problem with the following code. It seems the 2nd & 3rd Case statement is causing the problem. What am I doing wrong?

UPDATE dbo.ztbl
SET
L_Code = (CASE
WHEN LEN(L_Code) = '0' then '0' + L_Code
END)

Offer = (CASE
WHEN LEN(Off) = '1' then '0000000000' + off
END)
Client_Id = CASE
WHEN LEN(Client_Id) = '2' then '20000100' + Client_Id
END
Thanx,
Trudye

1.dont put '' for integer comparison. LEN() always returns integer value.
2.You've missed comma seperating column values
3.Make sure you cast Client_Id,Off,...if they are not of character type else it will error during concatenation

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 10:36:23

Always?

DECLARE	@Sample TABLE (i INT, j VARCHAR(20))

INSERT	@Sample
SELECT	NULL, NULL UNION ALL
SELECT	NULL, '' UNION ALL
SELECT	NULL, ' ' UNION ALL
SELECT	NULL, 'w' UNION ALL
SELECT	1, ' ' UNION ALL
SELECT	1, 'w' UNION ALL
SELECT	1, NULL UNION ALL
SELECT	1, '' UNION ALL
SELECT	1002, ' ' UNION ALL
SELECT	1003, 'w' UNION ALL
SELECT	1004, NULL UNION ALL
SELECT	1005, ''

SELECT	i,
	LEN(i) AS [LEN(i)],
	j,
	LEN(j) AS [LEN(j)]
FROM	@Sample

E 12°55'05.25"
N 56°04'39.16"

Trudye
Posting Yak Master

169 Posts

Posted - 2008-08-11 : 10:51:54

DUH!! Thanks Guys for not reminding me I’m an idiot. Even Rain Man would have at least gotten the commas right. Didn’t know the else was mandatory, but it makes sense, I thought it worked like a WHERE, if it wasn’t true it ignored the record and moved to the next record.

Thanks again,
Trudye

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 10:55:21

The ELSE part is not mandatory. But if it isn't there, all cases not evaluated will be replaced with NULL.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources