代码之家  ›  专栏  ›  技术社区  ›  Sanjeewa

在sql server case语句的THEN部分中设置另一个条件

  •  0
  • Sanjeewa  · 技术社区  · 6 年前

    我有一个需要解决的问题

    CASE JDT1.TransType 
       WHEN 46 THEN  JDT1.Debit > 0
    END
    

    这导致了错误,我如何克服这个问题,我的完整查询是

    create PROCEDURE [dbo].[Supplier_Enquiry]   
    AS
    BEGIN
    
    SELECT        OCRD.CardCode, OCRD.CardName, OCRD.GroupCode, OCRG.GroupName, OCRD.CreditLine, ISNULL(JDT1.FCCurrency,'LKR') AS Currency, JDT1.RefDate, 
                             CAST(OJDT.BaseRef AS int) AS 'Document Number',
    CASE  WHEN JDT1.TransType=18 THEN 'Invoice'
              WHEN JDT1.TransType=19 THEN 'Credit Memo'
              WHEN JDT1.TransType=204 THEN 'A/P Down Payment'
              WHEN JDT1.TransType=46 THEN 'Vendor Payment'
              WHEN JDT1.TransType=30 THEN 'Journal Entry'
    
              END AS 'Transaction', 
                             JDT1.DueDate, JDT1.Debit - JDT1.Credit AS 'Outstanding', JDT1.Debit AS [Amount Dr], JDT1.Credit AS [Amount Cr],ISNULL(VPM1.CheckNum, 0) AS CheckNum
    FROM            VPM1 INNER JOIN
                             OVPM ON VPM1.DocNum = OVPM.DocEntry RIGHT OUTER JOIN
                             JDT1 INNER JOIN
                             OCRD ON JDT1.ShortName = OCRD.CardCode INNER JOIN
                             OCRG ON OCRD.GroupCode = OCRG.GroupCode INNER JOIN
                             OJDT ON JDT1.TransId = OJDT.TransId ON OVPM.TransId = OJDT.TransId
    WHERE        (OCRD.CardType = 'S')  
    AND
    (
    
    CASE JDT1.TransType 
        WHEN 46 THEN  JDT1.Debit > 0
    
    END
    
    ) 
    END
    

    需要一些指南来更正此问题

    2 回复  |  直到 6 年前
        1
  •  1
  •   Mark    6 年前

    因此,这将检查借方>仅当TransType=46时为0

    ...
    AND (JDT1.TransType <>46 OR (JDT1.TransType = 46 and JDT1.Debit > 0))
    ...
    
        2
  •  1
  •   Zohar Peled    6 年前

    在这种情况下,您根本不需要案例:

    WHERE OCRD.CardType = 'S' 
    AND 
    (
        JDT1.TransType <> 46
        OR JDT1.Debit > 0
    )