can not call method in decimal sql error

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 can not able to get value of balance, i have column of amount also, where i can put amount values.... can you help me to sort out where is mt fault in this query? my stored procedure is as follows:-

GO 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 /and LEFT(gLedeger.gl_voucher,2)=@voucher/

ORDER BY gLedeger.gl_date

end

It would be slightly complicated to get balance, the way you are trying to get it. First it is not almost certainly not the summation of all debits and all credits all the time, because you will need to consider the transaction up-to the current row only.

Furthermore you cannot have normal columns and an aggregate column in the same query and not have group by clause (missing from your query).

The simplest (from my point of view) solution I can suggest is, you create a table variable and put the output of this query into that table. Have one more column for balance which you can keep null initially.

You can then update the balance column in that table by self-joining (or by cursor) and then select from that table finally.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke10y ago
It would be slightly complicated to get balance, the way you are trying to get it. First it is not almost certainly not the summation of all debits and all credits all the time, because you will need to consider the transaction up-to the current row only.

Furthermore you cannot have normal columns and an aggregate column in the same query and not have group by clause (missing from your query).

The simplest (from my point of view) solution I can suggest is, you create a table variable and put the output of this query into that table. Have one more column for balance which you can keep null initially.

You can then update the balance column in that table by self-joining (or by cursor) and then select from that table finally.