Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4900
  • Last Modified:

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.

0
JAMES
Asked:
JAMES
  • 7
  • 5
  • 2
  • +2
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
jrb1Commented:
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
 
jrb1Commented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now