Hi, I am new to SQL and I need help to display null value as 0.
Problem description:
The query is to display Branch Code, Branch Location, Account Type Code, account Type Description and Total Current Values of each type of account. The report also needs to show a total of zero if the account type is not currently in use at that branch - ie. all branches should have two entries, one for Savings and one for Term Deposits.
My code works perfectly, but it will just skip all the null values. For example, if the Total Current Values of Savings in Branch1 is null, the report will not display this.
Thank you for your replies. -
SELECT B.BRA_CODE "Branch Code", B.BRA_LOCATION "Branch Location", A.ACCTYP_CODE "Account Type Code",
-
AT.ACCTYP_DESC "Account Type Description", SUM(NVL(A.ACCNT_BAL, 0)) "Total Current Values"
-
FROM ACCOUNT A, ACCTYPE AT, BRANCH B, CUSTOMER C
-
WHERE A.CUST_NO = C.CUST_NO
-
AND C.BRA_CODE = B.BRA_CODE
-
AND A.ACCTYP_CODE = AT.ACCTYP_CODE
-
GROUP BY B.BRA_CODE, B.BRA_LOCATION, A.ACCTYP_CODE, AT.ACCTYP_DESC
-
ORDER BY B.BRA_CODE
-
2  1763 
Try lik this - SELECT B.BRA_CODE "Branch Code", B.BRA_LOCATION "Branch Location", A.ACCTYP_CODE "Account Type Code",
-
AT.ACCTYP_DESC "Account Type Description",
-
-
DECODE(SUM(NVL(A.ACCNT_BAL, 0)),NULL,0,SUM(NVL(A.ACCNT_BAL, 0))) "Total Current Values"
-
-
FROM ACCOUNT A, ACCTYPE AT, BRANCH B, CUSTOMER C
-
WHERE A.CUST_NO = C.CUST_NO
-
AND C.BRA_CODE = B.BRA_CODE
-
AND A.ACCTYP_CODE = AT.ACCTYP_CODE
-
GROUP BY B.BRA_CODE, B.BRA_LOCATION, A.ACCTYP_CODE, AT.ACCTYP_DESC
-
ORDER BY B.BRA_CODE
But I wonder how it goes to NULL if u r giving NVL()
RESHMI
Hi, I am new to SQL and I need help to display null value as 0.
Problem description:
The query is to display Branch Code, Branch Location, Account Type Code, account Type Description and Total Current Values of each type of account. The report also needs to show a total of zero if the account type is not currently in use at that branch - ie. all branches should have two entries, one for Savings and one for Term Deposits.
My code works perfectly, but it will just skip all the null values. For example, if the Total Current Values of Savings in Branch1 is null, the report will not display this.
Thank you for your replies.
-
SELECT B.BRA_CODE "Branch Code", B.BRA_LOCATION "Branch Location", A.ACCTYP_CODE "Account Type Code",
-
AT.ACCTYP_DESC "Account Type Description", SUM(NVL(A.ACCNT_BAL, 0)) "Total Current Values"
-
FROM ACCOUNT A, ACCTYPE AT, BRANCH B, CUSTOMER C
-
WHERE A.CUST_NO = C.CUST_NO
-
AND C.BRA_CODE = B.BRA_CODE
-
AND A.ACCTYP_CODE = AT.ACCTYP_CODE
-
GROUP BY B.BRA_CODE, B.BRA_LOCATION, A.ACCTYP_CODE, AT.ACCTYP_DESC
-
ORDER BY B.BRA_CODE
-
Thanks for the code and I have tried it, but it still does not return the null value as 0.
For the NVL, I thought it will return 0 if there is a null value.
I will post again if I find the solution.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Stuart Norris |
last post by:
Dear Group,
I am attempting to write a "splash" and "status" Form using a second
thread.
I wish to use this Form to display status information to the user when
I do CPU intensive work in my...
|
by: Edward Koucherian |
last post by:
I'm getting the incorrect display value on my DataGrid column.
The field is numeric (1) in the SQL database table
The Code is
txtCol = New DataGridBoolColumn
txtCol.MappingName = "MyCol"...
|
by: .Net Sports |
last post by:
the below itemdatabound function works , displays all the grand totals
in the footer control of the datagrid:
private void dglvboard_ItemDataBound(object sender,...
|
by: Martin Arvidsson |
last post by:
Hi!
I have two computed datafields that i am displaying in my windowsform.
When i try to update the dataset and commit the transaction.
I get an error message telling me that i cant update...
|
by: Greg Lyles |
last post by:
Hi all,
I'm trying to develop an ASP.NET 2.0 website and am running into some
real problems with what I thought would be a relatively simple thing
to do.
In a nutshell, I'm stuck on trying to...
|
by: anatak |
last post by:
Hello I have function that generates a list op options for a select dropdown box
here is the function
I am pretty sure that the problem is situated in the new Option() function
function...
|
by: Drew |
last post by:
I posted this to the asp.db group, but it doesn't look like there is much
activity on there, also I noticed that there are a bunch of posts on here
pertaining to database and asp. Sorry for...
|
by: siddu |
last post by:
Hi All,
I Created a Registration Page with only username,Pwd,Email,Contactno and Address fields.
1.when a Enter user with different Email then it is saved the details in Database.
2.My Code...
|
by: ravindranathreddy |
last post by:
Hi,
I am trying to select a value from a table which data type is time stamp and in my select query i am checking for the condition, if the retrieved value is null then i am displaying it in the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
