Assigning values using CASE statmenent

Question

Post reply

Assigning values using CASE statmenent

simflex-897410

Hall of Fame

Points: 3881
More actions
September 13, 2011 at 1:46 pm

#257342

Dear Experts,

I have the following fieldnames:

CheckNumber, CheckAmount, CashAmount

We have a requirement that if the user collects some money, and the money is check amount,

enter the check number under the Check Number column, then enter the amount Amount column and the Cash Amount by default is $0.0

Right now, I am trying to use a CASE statement to assign a value of 'Cash Payment' under the Check Number column if the amount collected is cash.

Please see my layout.

Check Number Check Amount Cash Amount
123654789 $125.00 $0.00 Cash Payment $0.00 $26.00

So far, my code is bumming out with the following error message:

Incorrect syntax near '='.

The error is on this bolded portion.

Here is the code I am using.

select CASE WHEN chcknumber='' AND checkAmount <>'' THEN chcknumber = 'Cash Payment' ELSE chcknumber END from mytable

Thanks for any help I can get.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

just syntax; the case returns the desired value only, so you can't have a assigning, like that chknbr='Cash':



select

  CASE

  WHEN chcknumber='' AND checkAmount <> ''

  THEN 'Cash Payment'

  ELSE chcknumber

END AS chcknumber

from mytable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

Thank you very very much for your prompt response.

I had tried the same code before but it doesn't add that 'Cash Payment' under the Check Amount column.

It doesn't show up anywhere. I tried it again after your response, still nothing.

When I run this in query analyzer, it shows the 'Cash Payment' in some but not in others.

Could it be because some are NULL, not necessary empty?

If so, how do I account for those, maybe use coalesce?

Lowell SSC Guru Points: 323482 More actions · Answer 3

yep null would probably be the culprit; just wraping it with ISNULL should fix it i think:



select

  CASE

  WHEN ISNULL(chcknumber,'') = '' AND checkAmount <> ''

  THEN 'Cash Payment'

  ELSE chcknumber

END AS chcknumber

from mytable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 4

simflex-897410

Hall of Fame

Points: 3881

September 13, 2011 at 2:53 pm

#1380925

Thank you so much Lowell.

Your assistance and prompt response, very much appreciated.

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 5

Hi mates,

I am still having problem with this code.

This is the code from Lowell (Much appreciated sir).

select



  CASE

  WHEN ISNULL(chcknumber,'') = '' AND checkAmount <> ''

  THEN 'Cash Payment'

  ELSE chcknumber

END AS chcknumber

from mytable

I was getting some results in some but not all.

For instance, I have this client with 5 client Ids.

2 of those Ids have check amount and 2 have cash amount

None of the cash amounts have have show 'Cash Payments'

I modified the code slightly and this is the modified code.

select



 CASE

  WHEN chcknumber ='' AND isnull(checkAmount ,'0.0') is not null

  THEN 'Cash Payment'

 ELSE chcknumber

END As chcknumber

from mytable

This code shows the 'Cash Payment' text.

The problem is that it shows it also for some clientIds that don't have don't have checknumber and check amount values.

Any ideas what I could be doing wrong?

Lowell SSC Guru Points: 323482 More actions · Answer 6

well, you are using a two part test for the "Cash Payment"...chknumber has to be blank and ALSO you are checking the amount...

i'd think that the amount may be non zero regardless of the chknumber, right?



select

ISNULL(chcknumber, 'Cash Payment') AS chcknumber,

 isnull(checkAmount ,0.00) AS checkAmount

from mytable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 7

Thanks so much again Lowell for coming to the rescue.

If the checknumber column is blank, then the money collected must be cash because check cannot be collected without check number.

So, if the cashAmount column for a particular client has some money (zero or null are considered empty don't count as amount).

So, if cashAmount fieldname for a particular client is not empty, and the checkNumber is empty (as it is supposed to be), then we want 'Cash Payment' to be in that Check Number column.

I hope it is a bit clearer.

I will try last code now.

Again, many thanks Lowell.

UPDATE: I think that the issue is that a lot of the check numbers are showing either NULL or empty.

The ones that show NULL have the 'Cash Payment'.

The ones that show empty space don't display the 'Cash Payment' text.