What's !@#!! wrong with my case expression?

Question

Post reply

What's !@#!! wrong with my case expression?

wjhamel

Old Hand

Points: 347
More actions
November 9, 2011 at 7:19 am

#245914

USE CIVIL
SELECT [Receiptno], Category
CASE [Receiptno]
WHEN category='NON' THEN REPLACE receiptno('%ENF','%NON'),
WHEN Category='DVI' THEN REPLACE Receiptno('%ENF','%DVI'),
WHEN Category='CLD' THEN REPLACE Receiptno('%ENF','%CLD')
END AS TYPE
FROM RECEIPTS
Trying to replace then end of the string "Receiptno" with a new last three characters, based on that row's value in the "Category" column. Keeps telling me that i have "incorrect syntax" near "Case". I'm not seeing it.
help?
thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

There are a few things wrong with it.

1. replace needs 3 paramters you need to extend the brackets

2 as type is not needed since you are naming the column at the start or maybe you are missing a ','

3. you were missing an '='

4. I dont think that replace statement is going to work how you expect as it will treat % as a literal string

5. the when clauses do not need a ',' between them

SELECT [Receiptno], Category =

CASE

WHEN category='NON' THEN REPLACE (receiptno,'%ENF','%NON')

WHEN Category='DVI' THEN REPLACE (Receiptno,'%ENF','%DVI')

WHEN Category='CLD' THEN REPLACE (Receiptno,'%ENF','%CLD')

END

FROM RECEIPTS

wjhamel Old Hand Points: 347 More actions · Answer 2

Thanks.

Again, the fruits of me starting my day by rushing into something without first having had a gallon of coffee and clearing the cobwebs.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Breakwaterpc (11/9/2011)
USE CIVIL
SELECT [Receiptno], Category
CASE [Receiptno]
WHEN category='NON' THEN REPLACE receiptno('%ENF','%NON'),
WHEN Category='DVI' THEN REPLACE Receiptno('%ENF','%DVI'),
WHEN Category='CLD' THEN REPLACE Receiptno('%ENF','%CLD')
END AS TYPE
FROM RECEIPTS
Trying to replace then end of the string "Receiptno" with a new last three characters, based on that row's value in the "Category" column. Keeps telling me that i have "incorrect syntax" near "Case". I'm not seeing it.
help?
thanks

There are quite a few issues with that select. First you are missing a comma after Category. Your case statement is incorrect. You say Case ReceiptNo and then check when a different field = soemthing. I assume you want that be something like

CASE category

WHEN 'NON' THEN someValue

WHEN 'DVI' THEN someValue

WHEN 'CLD' THEN someValue

end as Type

Now lets look at the replace portion. It is a function. Replace(Receiptno, '%ENF','%CLD'). That still won't accomplish what you are trying to do but at least it is syntactically correct. In fact, now that I look at this again you don't even need a case statement at all. Drop the whole case statement for a simple string concatenation.

SELECT [Receiptno], Category, LEFT(Receiptno, datalength(Receiptno) - 3) + Category as Type

from Receipts

--edited typo

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

wjhamel Old Hand Points: 347 More actions · Answer 4

I like the non CE option better. The select statement fires off fine, but what do i need to change to make it actually update those receiptno's?

GSquared SSC Guru Points: 260824 More actions · Answer 5

Turn it into an Update instead of a Select.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Breakwaterpc (11/9/2011)
I like the non CE option better. The select statement fires off fine, but what do i need to change to make it actually update those receiptno's?

Update table set column = value

Specifically..

Update Receipts

set ReceiptNo = LEFT(Receiptno, datalength(Receiptno) - 3) + Category

--edit...can't type today.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

Sean Lange (11/9/2011)
Breakwaterpc (11/9/2011)
I like the non CE option better. The select statement fires off fine, but what do i need to change to make it actually update those receiptno's?
Update table set column = value
Specifically..
Update Receipts
set ReceiptNo = LEFT(Receiptno, datalength(Receiptno) - 3) + Category
--edit...can't type today.

Oh, be careful, Sean... as you know, that will unconditionally update all rows. The REPLACE functions in the original post implied than only those items that ended with "ENF" should be updated. The code should probably have some criteria in it as to which rows to update... perhaps such as the following...

UPDATE dbo.Receipts

SET ReceiptNo = LEFT(Receiptno, DATALENGTH(Receiptno) - 3) + Category

WHERE RIGHT(ReceiptNo,3) = 'ENF';

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 8

Actually, the Where clause probably needs to be IN not =, and a list of the values to update.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Either way I guess I foolishly assumed that the OP would realize an update statement needs a where clause. If not, they will only make that mistake once. 😛 I know I will never run an update on production without a transaction first as a sanity check. I made that mistake once.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

wjhamel Old Hand Points: 347 More actions · Answer 10

All set on this end. Thanks to all.

That project is now closed and waiting for internal review. On to the next.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

@BreakWaterPc,

As you can see, there's some question as to what really needs to be done here. It looks like you want to repalce on those things that end with "ENF" but maybe you want to replace things that end with "%ENF" where the "%" ISN'T a wildcard. My suggestion at this point would be to read the article at the first link in my signature lines below and post your data using the methods in that article along with the actual expected results should be. You don't have to post all of the rows of data... just enough to make the problem crystal clear. And, yeah... if you post the rows in the readily conmsumble format identified in the article, that will remove all questions from everyone's minds as well as making it super easy to get a coded example that actually works the way you want it to. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 12

Also, you've posted your question on several different forums on this site. A lot of the heavy hitters actually monitor all of the forums via "Questions posted today" and so they'll see your question. No need to post on multiple forums. Pick the most appropriate forum and post it just once.

~~Also, posting on multiple forums just divides the possible answers up if others need to do the same thing.~~

~~Thanks.~~

{EDIT} My apologies... :blush: You didn't list under more than one forum. I had been looking at all previous posts for someone and it listed all of your posts, as well, making it look like you had posted your question more than once.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)