?
Solved

Using a "not equal" equation in CASE WHEN statement

Posted on 2005-03-11
17
Medium Priority
?
4,572 Views
Last Modified: 2012-05-05
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
Comment
Question by:JAMES
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +2
17 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13520672
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
 

Author Comment

by:JAMES
ID: 13520684
Already had that but was trying to optimise to remove an unnecessary condition.
0
 

Author Comment

by:JAMES
ID: 13520700
+ It's not actually just about optimising, it doesnt work the way we want unless I can get a <> in there.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 66

Expert Comment

by:Jim Horn
ID: 13520709
I see.  The BOL page for CASE statements in T-SQL doesn't make any references to WHEN <>...
0
 

Author Comment

by:JAMES
ID: 13520733
I know but it does say :-

"when_expression is any valid SQL Server expression" - which is not the case here I fear!
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 13520781
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
 

Author Comment

by:JAMES
ID: 13520797
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
 

Author Comment

by:JAMES
ID: 13520821
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13520877
That looks pretty optimized.  Did it work?
0
 

Author Comment

by:JAMES
ID: 13520888
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13520916
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
 
LVL 25

Expert Comment

by:jrb1
ID: 13520984
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
 
LVL 25

Expert Comment

by:jrb1
ID: 13521023
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13521184
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
 

Author Comment

by:JAMES
ID: 13521300
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13521500
>>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
 
LVL 13

Expert Comment

by:KarinLoos
ID: 13523800
<< 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question