UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Case Expression For Inequality Comparison, Any Versions    
 
   
BruceM
post May 19 2020, 01:42 PM
Post#1


UtterAccess VIP
Posts: 8,110
Joined: 24-May 10
From: Downeast Maine


It seems the CASE statement cannot handle inequality directly. For instance, I can do (Dtl is a table alias):

CASE Dtl.Expected - Dtl.Actual WHEN 1 THEN 1 WHEN 0 THEN 0 ELSE 2 END AS Test

This is not useful, but it produces the expected result. However, I cannot do:

CASE Dtl.Expected - Dtl.Actual WHEN > 1 THEN 1 WHEN 0 THEN 0 ELSE 2 END AS Test

That leads to error messages that '>' and ')' not recognized. When I try:

CASE Dtl.Expected - Dtl.Actual WHEN 1 THEN Format(Dtl.RC_AFExpected - Dtl.RC_AL, '\+0.0') WHEN 0 THEN 0 ELSE 2 END AS Test

I get the error message 'Conversion failed when converting the nchar value '+1.0' to data type int'

The data type for the fields is Float. The SQL Server tables were copied from Access. Typically I use Currency / Money for such fields. Not sure why I used Double / Float, but I doubt it is the problem.

I'm trying to subtract one value from the other. If the result is > 0, format it like this: +1.2. If it is 0: 0. If it is negative, -1.2.

Is what I am trying to do possible? I can't imagine it is a completely unique requirement, but I have spent several hours today trying to find a workaround.
Go to the top of the page
 
Jeff B.
post May 19 2020, 01:52 PM
Post#2


UtterAccess VIP
Posts: 10,482
Joined: 30-April 10
From: Pacific NorthWet


Are you attempting this entirely within SQL Server, or are you using an Access front-end?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
BruceM
post May 19 2020, 01:54 PM
Post#3


UtterAccess VIP
Posts: 8,110
Joined: 24-May 10
From: Downeast Maine


Access front end. I can parse the data there, but wondered if it is possible in SQL Server.
Go to the top of the page
 
cheekybuddha
post May 19 2020, 02:00 PM
Post#4


UtterAccess Moderator
Posts: 12,810
Joined: 6-December 03
From: Telegraph Hill


CODE
CASE
  WHEN Dtl.Expected - Dtl.Actual > 1 THEN 1
  WHEN Dtl.Expected - Dtl.Actual = 0 THEN 0
  ELSE 2
END AS Test


Do the formatting in Access, unless you want the whole lot as strings

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post May 19 2020, 02:03 PM
Post#5


UtterAccess Moderator
Posts: 12,810
Joined: 6-December 03
From: Telegraph Hill


Just to check, if the difference is 1.5 you want the result to be 2?

If not then:
CODE
CASE
  WHEN Dtl.Expected - Dtl.Actual >= 1 THEN 1
-- ...


Also, what about when Dtl.Expected - Dtl.Actual < 0 ?

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post May 19 2020, 02:41 PM
Post#6


UtterAccess VIP
Posts: 8,110
Joined: 24-May 10
From: Downeast Maine


I don't want the result to be 1 or 2, I want the actual result formatted as I wish. I posted the first expression just because it was the only one that didn't produce an error. I thought it would be possible to do the formatting directly in SQL Server, but it seems it may not be. If I can get the formatting to work within a CASE expression I can work out the details of handling plus and minus values, but I cannot get the formatting to work within CASE.

I tried it this way:

CASE WHEN Dtl.Expected - Dtl.Actual > 0 THEN 1 WHEN Dtl.RC_AFExpected - Dtl.RC_AL < 0 THEN 2 ELSE 0 END AS Test

It worked, but I am trying to show the results of the actual math.

I tried this:

CASE WHEN Dtl.Expected - Dtl.Actual > 0 THEN Dtl.Expected - Dtl.Actual WHEN Dtl.Expected - Dtl.Actual < 0 THEN Dtl.RC_AFExpected - Dtl.RC_AL ELSE 0 END AS Test

It worked. However, it is obviously redundant since the result is the same calculation whether the value is greater or less than 0. What I really want is the following for positive, 0, and negative values:

+1.2
0
-2.1

So I tried formatting, and it fell apart. Here is a simplified version:

CASE WHEN Dtl.Expected - Dtl.Actual > 0 THEN Format(Dtl.Expected - Dtl.Actual, '0.0') ELSE 0 END AS CorrFactor

That leads to the error message 'Conversion failed when converting the nchar value '1.0' to data type int'

As I said, I can simply do the formatting in Access, but am trying to learn more about working with SQL Server. However, it seems this formatting, which would be rather simple in Access, is difficult at best in SQL Server.
Go to the top of the page
 
BruceM
post May 19 2020, 03:27 PM
Post#7


UtterAccess VIP
Posts: 8,110
Joined: 24-May 10
From: Downeast Maine


Thanks anyhow. It's not worth the bother of fighting with SQL Server. I had thought it was possible to do what I wanted, but Access is clearly a much better tool for the job.
Go to the top of the page
 
cheekybuddha
post May 19 2020, 04:04 PM
Post#8


UtterAccess Moderator
Posts: 12,810
Joined: 6-December 03
From: Telegraph Hill


Well, it's only the '+' symbol that's the issue - really it's a display issue and is best handled in the application layer rather than the data layer.

It's easy enough to get the output you want, but you would then be dealing with strings rather than numbers.

If you have no need to use the numbers as numbers in the application then by all means do it in the data layer (ie your query), but if you were to put the format in the format property of a textbox then you have both the display you want and then underlying value is still a number.

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post May 19 2020, 04:14 PM
Post#9


UtterAccess Moderator
Posts: 12,810
Joined: 6-December 03
From: Telegraph Hill


If you want to do it in the server then try:
CODE
  CASE
    WHEN Dtl.Expected - Dtl.Actual > 0 THEN '+' + CAST (Dtl.Expected - Dtl.Actual AS NVARCHAR(10))
    ELSE CAST (Dtl.Expected - Dtl.Actual AS NVARCHAR(10))
  END AS Test


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
MadPiet
post May 19 2020, 05:35 PM
Post#10



Posts: 3,706
Joined: 27-February 09



FWIW

CODE
DECLARE @Expected DECIMAL(3,2) = 5.00
        ,@Actual DECIMAL(3,2) = 4.96;
DECLARE @Symbol CHAR = '+';

SELECT IIF(@Actual>@Expected,'+','') + CAST(@Actual - @Expected AS VARCHAR(5)) AS AE
    , IIF(@Expected>@Actual,'+','') + CAST(@Expected - @Actual AS VARCHAR(5)) AS EA;
Go to the top of the page
 
BruceM
post May 20 2020, 07:11 AM
Post#11


UtterAccess VIP
Posts: 8,110
Joined: 24-May 10
From: Downeast Maine


This is for a report, so the number values are displayed only. I most definitely need number values in the actual application, but not in this View. Because of floating point strangeness I cannot CAST the result as text, because I expect would end up with things like 1.1999999999999999998 (or something of the sort, which is what I see for unformatted values). But perhaps I can cast the format result as text. I didn't try that.

The strange thing (or so it seems to me) is that I can simply format the value: Format(Dtl.Expected - Dtl.Actual,'\+0.0') and it works as it should. I don't want the + sign for every value, of course, but the point is that it produces the expected result. However, when I apply the exact same calculation and formatting as a WHEN option in the CASE expression as shown in my initial posting I get the error "Conversion failed when converting the nchar value '+1.0' to data type int". The difference between the two situations eludes me. Also, I don't see why the message is about an integer value since the fields involved are Float, and for what it's worth the formatting is decimal. Maybe I'll try casting each value (Expected and Actual) as money or some other fixed decimal format (I'm not sure of all the SQL Server options). Also, since there is no need for floating point I will try changing the table data types to a fixed decimal type (after copying the table, of course).

As I have mentioned, I can make this work perfectly well by formatting in Access, but I am trying to learn as much as I can about SQL Server so I have the option when it is necessary. I'm curious and intrigued, but not stuck.

Piet, I have seen the sort of thing you describe, and I expect I will use it in some situation, but in this case I can have Access apply the formatting I need, with no discernible time lag.

Thanks for the replies!
Go to the top of the page
 
cheekybuddha
post May 20 2020, 07:20 AM
Post#12


UtterAccess Moderator
Posts: 12,810
Joined: 6-December 03
From: Telegraph Hill


>> I cannot CAST the result as text, because I expect would end up with things like 1.1999999999999999998 <<

You can use ROUND() before casting:
CODE
  CASE
    WHEN Dtl.Expected - Dtl.Actual > 0 THEN '+' + CAST (ROUND(Dtl.Expected - Dtl.Actual, 2) AS NVARCHAR(10))
    ELSE CAST (ROUND(Dtl.Expected - Dtl.Actual, 2) AS NVARCHAR(10))
  END AS Test

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post May 20 2020, 08:06 AM
Post#13


UtterAccess VIP
Posts: 8,110
Joined: 24-May 10
From: Downeast Maine


Round instead of formatting. Could be worth exploring. I will try that, maybe later today, and let you know. Thanks. It sounds promising.
Go to the top of the page
 
BruceM
post May 20 2020, 10:53 AM
Post#14


UtterAccess VIP
Posts: 8,110
Joined: 24-May 10
From: Downeast Maine


I think I found the problem: the ELSE 0 at the end of the CASE. As a recap, I wanted positive, 0, and negative values like this: +1.2, 0, -2.3

If the Expected - Actual calculation is 0 it is to be formatted as the integer 0, not as 0.0. To that end, I had a formatted value for > 0 and < 0, which means it was text (same as if I cast the result as nvarchar or similar). However, the 0 value was an integer. As I recall, the results within a CASE expression (the WHEN results and the ELSE) need to be the same data type, or maybe if any of them are integer they are all expected to be integer. Something like that. Anyhow, this is what worked:
CODE
CASE
  WHEN Dtl.Expected - Dtl.Actual > 0 THEN FORMAT(Dtl.Expected - Dtl.Actual,'\+0.0')
  WHEN Dtl.Expected - Dtl.Actual < 0 THEN FORMAT(Dtl.Expected - Dtl.Actual,'0.0')  
  ELSE FORMAT(0,'0')
END AS Test

If I did ELSE 0 I got the error about converting to an integer. It also works if I cast the 0 as nvarchar, but I decided to use the same function as for the WHEN expressions.
Go to the top of the page
 
cheekybuddha
post May 20 2020, 11:49 AM
Post#15


UtterAccess Moderator
Posts: 12,810
Joined: 6-December 03
From: Telegraph Hill


Great! Glad you got there in the end! thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post May 21 2020, 06:45 AM
Post#16


UtterAccess VIP
Posts: 8,110
Joined: 24-May 10
From: Downeast Maine


I vaguely recall seeing something like this in the past, but I had forgotten the details. Maybe this time it will sink in. Could happen smile.gif
Go to the top of the page
 
cheekybuddha
post May 21 2020, 06:50 AM
Post#17


UtterAccess Moderator
Posts: 12,810
Joined: 6-December 03
From: Telegraph Hill


>> Could happen smile.gif <<

If not, we're always here to try and work it out again!!

--------------------


Regards,

David Marten
Go to the top of the page
 
AlbertKallal
post May 21 2020, 09:52 PM
Post#18


UtterAccess VIP
Posts: 3,064
Joined: 12-April 07
From: Edmonton, Alberta Canada


Ok, your problem is “subtle” in that the case when statement needs to work on Boolean expressions.


So, this fails at the SSMS command line:

CODE
print case 31 - 5 when > 30 then '> 30' else ' < 30' end


So, the “expression” must be a true/false and THAT allows use of “then” or “else”. “when” is a equal value – no “<, >, <>, =” allowed. It is simply “when” a value.


So, you need this:

CODE
print case when (31 – 5) > 30 then '> 30' else ' < 30' end


Or, in your case, you can paste this into SSMS


This would work for you

CODE
CASE
   When (Dtl.Expected - Dtl.Actual) > 1 then 1.2
   When (dtl.Expected – dt1.Actual) < 1 then -1.2
   Else 0
END AS Test


Now, to be fair? Well, we really should not have to re-type the expression 2 times.

There is a sign() function.
It returns 1 for any > 1
It returns -1 for any < 1
And it returns 0 for zero.

So, in the interest of not having to repeat the expression?

You wind up with this:

CODE
CASE sign(Dtl.Expected - Dtl.Actual)
   When 1 then 1.2
   When 0 then 0
   When -1 then -1.2
END AS Test


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
BruceM
post May 26 2020, 07:23 AM
Post#19


UtterAccess VIP
Posts: 8,110
Joined: 24-May 10
From: Downeast Maine


Thanks, Albert. It's good to have the clear summation that a Boolean is needed. Beyond that, I was trying to mix data types. For complicated reasons, we have to do the unnecessary step of using the + sign for positive numbers, so AFAIK the only way is to format the result, which means it becomes text. The 0, which receives neither sign, needs to be a text value since the formatted positive and negative values are text.

Good to know about the SIGN function. That is going to come up as I continue to convert queries and code to SQL Server, where a comparison resulting in 0 or negative means the test passes, otherwise it fails.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th May 2020 - 10:04 PM