Search code examples
sqlsql-serversql-server-2014

Set another condition in THEN section in case statement sql server


I have a situation where I need to set

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

which leads to error , how can I overcome this issue , my full query is

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

need some guide on to correct the issue


Solution

  • so this checks the Debit >0 only if TransType = 46

    ...
    AND (JDT1.TransType <>46 OR (JDT1.TransType = 46 and JDT1.Debit > 0))
    ...