如何减去自我中的法案 [英] How to Subtract in case Statment in self

问题描述

SELECT Payment_Id, Pay_Booking_Id, Convert(varchar(50),Payment_Date,103)AS Payment_Date,Narration,Amount,
			CASE WHEN Transaction_Type = 'M' THEN 'Mile Stone' ELSE CASE WHEN Transaction_Type = 'R' THEN 'Reciept' ELSE CASE WHEN Transaction_Type = 'P' THEN 'Payment' ELSE CASE WHEN Transaction_Type = 'DT' THEN 'Direct Transfer' ELSE CASE WHEN Transaction_Type = 'C' THEN 'Journel Credit' ELSE 'Journel Debit' END END END END END as Transaction_Type , 
			CASE WHEN Transaction_Type = 'M' THEN
			
				ISNULL((SELECT SUM(Adj_Amount) FROM Adjustment WHERE Adj_What_Id = Payment_Id  ), 0.000)
			ELSE 
				ISNULL((SELECT SUM(Adj_Amount) FROM Adjustment WHERE Adj_Payment_Id = Payment_Id AND Adj_What_Id = Payment_Id),0.000)			
			 END AS Adj_Amount_OtherTxn,
			0.000 AS Adj_Amount_thisTxn 
			
			FROM Payment Pay WHERE Pay_Booking_Id = @Booking_Id

推荐答案

查看Tomas Takac的评论。



你在哪里工作out Transaction_Type 你没有正确使用 CASE 语句......它看起来应该更像是

See the comment from Tomas Takac.

Where you are working out Transaction_Type you are not using the CASE statement correctly ... it should look more like

CASE    WHEN Transaction_Type = 'M' THEN 'Mile Stone'
        WHEN Transaction_Type = 'R' THEN 'Reciept'
        WHEN Transaction_Type = 'P' THEN 'Payment'
        WHEN Transaction_Type = 'DT' THEN 'Direct Transfer'
        WHEN Transaction_Type = 'C' THEN 'Journel Credit'
        ELSE 'Journel Debit'
END as Transaction_Type,

但是，由于您的问题似乎是关于减法部分，它不是 CASE 这就是问题，它更可能是 ISNULL的定位 ...你是检查整体 SUM 是否为NULL而不是单个列值。我认为应该更像是

However, as your question appears to be about the subtraction part, it's not the CASE that is the problem, it is more likely to be the positioning of the ISNULL ... you're checking the overall SUM for being NULL rather than the individual column values. I think that should look more like

CASE WHEN Transaction_Type = 'M' THEN
    (SELECT SUM(ISNULL(Adj_Amount,0.000)) FROM Adjustment WHERE Adj_What_Id = Payment_Id)
ELSE
    (SELECT SUM(ISNULL(Adj_Amount, 0.000)) FROM Adjustment WHERE Adj_Payment_Id = Payment_Id AND Adj_What_Id = Payment_Id)
END AS Adj_Amount_OtherTxn,

回到 Transaction_Type 上的案例陈述，你应该在另一张表中真正拥有这些值例如

Going back to your case statement on Transaction_Type, you should really have those values in another table e.g.

create table Tran_Type
(
	shortType varchar(2),
	longType varchar(50)
)
insert into Tran_Type values
('M','Mile Stone'),
('R','Reciept'),
('P','Payment'),
('DT','Direct Transfer' ),
('C','Journel Credit'),
('JD', 'Journel Debit')

然后您可以在查询中加入

which you can then join to in your query

SELECT Payment_Id, Pay_Booking_Id, Convert(varchar(50),Payment_Date,103)AS Payment_Date,Narration,Amount,

	ISNULL(TT.LongType, 'Journel Debit' ) as Transaction_Type, 
			
	CASE WHEN Transaction_Type = 'M' THEN
		(SELECT SUM(ISNULL(Adj_Amount,0.000)) FROM Adjustment WHERE Adj_What_Id = Payment_Id)
	ELSE 
		(SELECT SUM(ISNULL(Adj_Amount, 0.000)) FROM Adjustment WHERE Adj_Payment_Id = Payment_Id AND Adj_What_Id = Payment_Id)
	END AS Adj_Amount_OtherTxn,
			
	0.000 AS Adj_Amount_thisTxn 

FROM Payment Pay 
LEFT OUTER JOIN Tran_Type TT on Pay.Transaction_Type = TT.shortType
WHERE Pay_Booking_Id = @Booking_Id

注意我已使用 left outer join 和 isnull 模仿原始<$ c c中的覆盖 ELSE $ c> CASE 声明

Note I've used left outer join and isnull to mimic your overriding ELSE from the original CASE statement

这篇关于如何减去自我中的法案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！