信用借方余额不能调用十进制的方法 [英] Credit debit balance cannot call methods on decimal

问题描述

这是我的左连接查询，我自动开发信用卡借记和余额。



也许我缺乏专栏。我使用gl_drcr作为主列，并通过查询将其拆分为信用卡和借记卡，但我无法获得余额值。我也有金额列，我可以存储金额值。



你能帮我解决一下这个查询中我的错吗？



我尝试了什么：

Here is my query with left join and I am automatically developing credit debit and balance.

Perhaps I am lacking upon column. I used gl_drcr as main column and split it by query into credit and debit but I am unable to get value of balance. I have column of amount also, where I can store amount values.

Can you help me to sort out where my fault is in this query?

What I have tried:

ALTER procedure [dbo].[sp_getDataML]
    (@gl_acid int, @userid int)
AS
BEGIN
    SELECT 
        ROW_NUMBER() OVER (ORDER BY gLedeger.PK_id) AS sno,
        CASE 
           WHEN master.ma_accname IS NULL THEN ''
           ELSE ma_accname 
        END AS AccName, 
        gLedeger.gl_date AS date, gLedeger.gl_narration AS Narration, 
        gLedeger.gl_drcr as DRCR,
        CASE
           WHEN gLedeger.gl_drcr > 0 THEN ABS(gLedeger.gl_drcr) 
           ELSE 0.00 
        END AS debit,
        CASE 
           WHEN gLedeger.gl_drcr < 0 THEN ABS(gLedeger.gl_drcr) 
           ELSE 0.00 
        END AS credit,
        SUM(COALESCE(gLedeger.gl_drcr.credit, 0) 
         - COALESCE(gLedeger.gl_drcr.debit, 0)) AS Balance  
    FROM 
        gLedeger   
    LEFT JOIN 
        master ON gLedeger.GL_ACID = master.PK_ID 
    LEFT JOIN
        registration r ON master.userId = @userid
    WHERE 
        gLedeger.GL_ACID = @gl_acid
        
    ORDER BY 
        gLedeger.gl_date
END

推荐答案

不确定我是否正确理解了问题，但有一点是你似乎在注册和其他表之间缺少连接条件。你也可以移动条件

Not sure if I understand the problem correctly but one thing is that you seem to be missing join condition between registration and some other table. Also you could move the condition

master.userId = @userid

到WHERE子句

to WHERE clause

我得到了解决方案

如下： -

i got the solution
as follows:-

ALTER procedure [dbo].[fulltest]
(
  @gl_acid int,
  @userid int
  )
  as 
  begin


SELECT 	ROW_NUMBER() OVER (Order by gLedeger.PK_id) as sno,case when master.ma_accname is null then '' else ma_accname end as AccName,gLedeger.gl_date as date,gLedeger.gl_narration  as Narration,gLedeger.gl_drcr as DRCR,
	case when gLedeger.gl_drcr > 0 then abs(gLedeger.gl_drcr) else 0.00 end as debit,
	case when gLedeger.GL_DRCR	 < 0 then abs(gLedeger.gl_drcr) else 0.00 end as credit,
SUM(gLedeger.gl_drcr) OVER(ORDER BY gledeger.pk_ID ROWS UNBOUNDED PRECEDING) AS    Balance  
	FROM gLedeger   LEFT JOIN master ON 
    gLedeger.GL_ACID = master.PK_ID left join registration r on master.userId=@userid
    WHERE gLedeger.GL_ACID = @gl_acid
	/*and LEFT(gLedeger.gl_voucher,2)=@voucher*/
	
	ORDER BY gLedeger.gl_date

	end

这篇关于信用借方余额不能调用十进制的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！