Credit debit balance cannot call methods on decimal

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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:

SQL

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

Posted 24-Oct-16 1:37am

samlee69

Updated 24-Oct-16 4:07am

Wendelius

v4

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-10-24T04:10:00

Solution 1

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

SQL

master.userId = @userid

to WHERE clause

Posted 24-Oct-16 4:10am

Wendelius

samlee69 · Answer 2 · 2016-10-25T02:16:00

i got the solution
as follows:-

SQL

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