?
Solved

Return 0.00 where there is no records

Posted on 2014-11-27
12
Medium Priority
?
75 Views
Last Modified: 2014-11-27
I have two tables, an GL_ACCT table and a GL_Trans table.

I'm trying to determine the closing balance for a ledger account that had no transactions is a particular month but with an opening balance of 400.

The problem I am having is that when I select records from the transaction table, the ledger ID I'm keen to see doesn't return a value of '0.00' which I expect knowing there are no records, but does have an opening balance. It just give me two records instead of four.

LD_ID       OPENING BALANCES             MOVEMENT                                      CLOSING BALANCES        
                 DR                       CR                     DR                      CR                                 DR                     CR
  2           449,999.60      0.00                633,396.60      483,396.60                 599,999.60             0.00
  4         150,000.00      0.00                     200,000.00      150,000.00                 200,000.00             0.00

Basically this is what I trying to achieve is this:

LD_ID       OPENING BALANCES             MOVEMENT                                      CLOSING BALANCES        
                 DR                       CR                     DR                      CR                                 DR                     CR
  2           449,999.60      0.00                633,396.60      483,396.60                 599,999.60             0.00
  3         0.00                      0.00                     0.00                      0.00                                 0.00                     0.00
  4         150,000.00      0.00                     200,000.00      150,000.00                 200,000.00             0.00
  7        200,000.00        0.00                     0.00                      0.00                                 200,000.00             0.00
0
Comment
Question by:barkome
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
12 Comments
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 1000 total points
ID: 40468708
Hi,

You can use the left join in place of inner join

and after that you can use case statement to remove null from '0.00'
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40468805
Can you post the query that you are using now?
0
 

Author Comment

by:barkome
ID: 40468912
Hi Vitor

This is what Im using...

SELECT A.LD_ID, B.BAL FROM
(SELECT                  GA.LD_ID L_ACCT_ID, GL.LD_ID, GL.PROD, GA.CHRT_ID, GA.LD_DC,
                                                SUM(CASE WHEN GL.TDM = '0' THEN -GL.TMT ELSE 0 END) AS DR,
                                                SUM(CASE WHEN GL.TDM = '1' THEN GL.TMT ELSE 0 END) AS CR,
                                                GL.SRC_SYS
                        FROM                  GL, GA GA
                        WHERE                  GA.LD_ID = GL.LD_ID AND GL.EFF_DT >= '01-OCT-2014' AND GL.EFF_DT <= '31-OCT-2014'
                        AND                        GL.LD_ID IN ('ID_1','ID_2','ID_3','ID_4')
                        GROUP BY            GA.LD_ID, GL.LD_ID, GL.PROD,GA.LD_DC, GA.CHRT_ID, GL.SRC_SYS) A,

(SELECT         GL.LD_ID,            (SUM(CASE WHEN GL.TDM = '0' THEN -GL.TMT ELSE 0 END)) + (SUM(CASE WHEN GL.TDM = '1' THEN GL.TMT ELSE 0 END)) OPN_BAL
                        FROM                  GL GL WHERE GL.EFF_DT <= '01-OCT-2014'
                        AND                  LD_ID IN ('ID_1','ID_2','ID_3','ID_4')
                        GROUP BY            GL.LD_ID )B

WHERE A.LD_ID = B.LD_ID AND B.LD_ID IN ('ID_1','ID_2','ID_3','ID_4')
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:barkome
ID: 40468923
Hi Vikas

Tried the left join that you suggested, it returned no records
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 1000 total points
ID: 40468947
The SUM doesn't process NULL values. For that you need to use the ISNULL function to assume NULL values as zero:
SELECT A.LD_ID, B.BAL 
FROM (SELECT  GA.LD_ID L_ACCT_ID, GL.LD_ID, GL.PROD, GA.CHRT_ID, GA.LD_DC,
              SUM(CASE WHEN GL.TDM = '0' THEN -ISNULL(GL.TMT,0) ELSE 0 END) AS DR,
              SUM(CASE WHEN GL.TDM = '1' THEN ISNULL(GL.TMT,0) ELSE 0 END) AS CR,GL.SRC_SYS
		FROM GL, GA GA
        WHERE GA.LD_ID = GL.LD_ID AND GL.EFF_DT >= '01-OCT-2014' AND GL.EFF_DT <= '31-OCT-2014'
			AND GL.LD_ID IN ('ID_1','ID_2','ID_3','ID_4')
        GROUP BY GA.LD_ID, GL.LD_ID, GL.PROD,GA.LD_DC, GA.CHRT_ID, GL.SRC_SYS) A, 
	(SELECT GL.LD_ID, (SUM(CASE WHEN GL.TDM = '0' THEN -ISNULL(GL.TMT,0) ELSE 0 END)) + (SUM(CASE WHEN GL.TDM = '1' THEN ISNULL(GL.TMT,0) ELSE 0 END)) OPN_BAL
	FROM GL GL 
	WHERE GL.EFF_DT <= '01-OCT-2014' AND LD_ID IN ('ID_1','ID_2','ID_3','ID_4')
	GROUP BY GL.LD_ID )B
 WHERE A.LD_ID = B.LD_ID AND B.LD_ID IN ('ID_1','ID_2','ID_3','ID_4') 

Open in new window

0
 

Author Comment

by:barkome
ID: 40469016
Hi Guys

Has to also add ISNULL at the select clause when also retrieving at that level
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40469020
So, why my solution isn't "acceptable"?
0
 

Author Comment

by:barkome
ID: 40469108
Hi Vitor

I tried to accept multiple solutions, don't know why the two didn't reflect..(it's been a while since I used EE)
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40469112
You can request for an EE moderator to help you with that.
0
 

Author Comment

by:barkome
ID: 40469117
O that's great, how do I do that?.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40469123
At the top of this question there's a Requestion Attention link. Just click on it.
Thanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question