如果列中的数字为负数，则显示为正数，否则显示数字

I have a [Preko]column in a query that is calculating the difference between two columns.If the number is negative,I need to show it positive,and if it is positive,it should stay the same.I can't use ABS in this one.I tried with the case,but it didn't work. The problem I am getting is that column Preko is invalid.

我在查询中有一个[Preko]列，计算两列之间的差异。如果数字为负数，我需要显示为正数，如果是正数，则应该保持不变。我不能使用ABS在这一个。我尝试了案件，但它没有奏效。我得到的问题是列Preko无效。

This is the code for my second try with iif:

这是我第二次使用iif的代码：

SELECT FP.Firma
      ,FP.NazFirme
      ,FP.Konto
      ,FP.NazivKonta
      ,FP.Partner
      ,FP.NazivPartnera
      ,Sum(FP.Duguje) AS dug
      ,Sum(FP.Potrazuje) AS pot
      ,Sum(IIf([FP].[Konto] Like '2*'
      ,[duguje]-[potrazuje]
      ,[potrazuje]-[duguje])) AS USaldo
      ,Sum(IIf([datumval]<= '1.1.2017'
      ,IIf([FP].[Konto] Like '2*'
      ,[duguje]-[potrazuje]
      ,[potrazuje]-[duguje]),0)) AS [Preko]
      ,IIf([Preko]<0,0,[Preko]) AS Preko1
FROM tblFinansijskiPodaci FP
Where FP.Firma = 1 
AND FP.Partner=1110
GROUP BY FP.Firma
        ,FP.NazFirme
        ,FP.Konto
        ,FP.NazivKonta,
        ,FP.Partner
        ,FP.NazivPartnera 
HAVING (((FP.Konto)=2040))

3 个解决方案

#1

You can also use a case statement instead of the iif section.

您还可以使用case语句而不是iif部分。

  CASE WHEN Preko<0 THEN 0 ELSE Preko END

要么

  CASE WHEN Preko<0 THEN -Preko ELSE Preko END

That seems to be more in line with your logic depending on how you want to handle the negatives.

这似乎更符合您的逻辑，具体取决于您如何处理否定。

#2

Agree with HoneyBadger - ABS is the way to go ABS(-1) returns 1. Have a look at the APEX SQL tools for a quick formatting option. It's free and makes your code a lot easier to read, which means you'll find you get more answers.

同意HoneyBadger - ABS是获取ABS（-1）返回的方法1.查看APEX SQL工具以获得快速格式化选项。它是免费的，使你的代码更容易阅读，这意味着你会发现你得到更多的答案。

#3

WITH cte AS (
SELECT FP.Firma
      ,FP.NazFirme
      ,FP.Konto
      ,FP.NazivKonta
      ,FP.Partner
      ,FP.NazivPartnera
      ,Sum(FP.Duguje) AS dug
      ,Sum(FP.Potrazuje) AS pot
      ,Sum(IIf([FP].[Konto] Like '2*'
      ,[duguje]-[potrazuje]
      ,[potrazuje]-[duguje])) AS USaldo
      ,Sum(IIf([datumval]<= '1.1.2017'
      ,IIf([FP].[Konto] Like '2*'
      ,[duguje]-[potrazuje]
      ,[potrazuje]-[duguje]),0)) AS [Preko]
FROM tblFinansijskiPodaci FP
Where FP.Firma = 1 
AND FP.Partner=1110
GROUP BY FP.Firma
        ,FP.NazFirme
        ,FP.Konto
        ,FP.NazivKonta,
        ,FP.Partner
        ,FP.NazivPartnera 
HAVING (((FP.Konto)=2040))
)
SELECT *, CASE WHEN Preko<0 THEN 0 ELSE Preko END preko1 FROM cte

#1