Using a "not equal" equation in CASE WHEN statement

How do we define not equal login when using a CASE WHEN statement ie. (see the "NOT EQUAL" line below)

SET CalculatedPrice =
      case inserted.Denomination
      when "NOT EQUAL" 100 then FLOOR(inserted.Price) + ( ( (inserted.Price - FLOOR(inserted.Price)) / inserted.Denomination ) * 100 )
.
.
.
Have tried all the usual <>, != NOT etc etc not doubt SQL has it's own idiosyncratic way of doing it!

Thanks.

James.

JAMESAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
How about...

SET CalculatedPrice =
CASE inserted.Denomination  WHEN 100 THEN {something different goes here} ELSE  FLOOR(inserted.Price) + ( ( (inserted.Price - FLOOR(inserted.Price)) / inserted.Denomination ) * 100 ) END
0
JAMESAuthor Commented:
Already had that but was trying to optimise to remove an unnecessary condition.
0
JAMESAuthor Commented:
+ It's not actually just about optimising, it doesnt work the way we want unless I can get a <> in there.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I see.  The BOL page for CASE statements in T-SQL doesn't make any references to WHEN <>...
0
JAMESAuthor Commented:
I know but it does say :-

"when_expression is any valid SQL Server expression" - which is not the case here I fear!
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I agree with your interpretation that this should be possible.  How about (Yes I'm fishing)...

SET CalculatedPrice =
CASE inserted.Denomination =1000 WHEN True THEN {something different goes here} ELSE  FLOOR(inserted.Price) + ( ( (inserted.Price - FLOOR(inserted.Price)) / inserted.Denomination ) * 100 ) END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JAMESAuthor Commented:
The problem is that we have already set CalculatedPrice in another statement about this one (which is using "inserted" within a trigger) and only want to SET CalculatePrice is this condition is not true.

I will think about your answer for a bit though.
0
JAMESAuthor Commented:
This might work - thanks to your example.

By moving "inserted.Denomination" to after the WHEN it allows me to use the condition <>.  Have to test it now!

SET CalculatedPrice =
  case when inserted.Denomination <> 100 then FLOOR(inserted.Price) + ( ( (inserted.Price - FLOOR(inserted.Price)) / inserted.Denomination ) * 100 )
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
That looks pretty optimized.  Did it work?
0
JAMESAuthor Commented:
Hmmm it worked but doesnt give the desired results after all that!

Another way of looking at my requirement is to ask (I will start a new question if you feel it's appropriate) how to use the updated values set in a trigger in a statement further down the same trigger?

It see that once an UPDATE has been done the table this "new" data is not available later in the same trigger.  The value just returns the value before the trigger got fired - is that makes any sense!?

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Hmm.. I'm still somewhat of a SQL Server newbie, so I'll pass on interpreting triggers.  My advise is to keep this question open and see what other experts comment.  If you feel my comments were helpful, you can split points when you answer the question.   Good luck.  -Jim
0
jrb1senior developerCommented:
You have two options that I see:

SET CalculatedPrice =
     case inserted.Denomination
     when 100 then...
     else FLOOR(inserted.Price) + ( ( (inserted.Price - FLOOR(inserted.Price)) / inserted.Denomination ) * 100 )

or

SET CalculatedPrice =
     case
        when inserted.Denomination  <> 100 then...
0
jrb1senior developerCommented:
Sorry...missed exactly where you were on this issue...

You have to do something like

DECLARE @newName VARCHAR(100)

And then read the value...

SELECT @newName = (SELECT name FROM Inserted)

And then use it later....
0
Anthony PerkinsCommented:
Can you put in English what you are trying to do?  In other words are you trying to update a column based on some condition?
0
JAMESAuthor Commented:
AC,

We are past that one now.

The one im now working on (as the original didnt solve my problem) is :- within the trigger I need access to the cols that have just been updated to use further down the same trigger.

Not sure if assigning the values from a SELECT @X = X FROM INSERTED does it.
0
Anthony PerkinsCommented:
>>We are past that one now.<<
Than perhaps it is time to start a new question.

>>Not sure if assigning the values from a SELECT @X = X FROM INSERTED does it.<<
The problem with this approach is that INSERTED can contain more than one row (@X will contain the last value read which I strongly suspect is meaningless)
0
KarinLoosCommented:
<< within the trigger I need access to the cols that have just been updated to >>

IF UPDATE( COLUMN_NAME)  
     BEGIN
           body of statements
     END

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.