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  1786 
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 GUI during startup. I wish to also use
the same Form after startup as well if I do something CPU intensive.
I have developed a multi-threaded application however the very first
|
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"
txtCol.HeaderText = ""
txtCol.Width = 25
txtCol.ReadOnly = False
txtCol.AllowNull = False
|
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,
System.Web.UI.WebControls.DataGridItemEventArgs e)
{
DataRowView rowData;
decimal price;
decimal priceWk;
decimal newssum;
|
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 calculated fields. That
i can understand
but how to tell the dataset or what ever to NOT do this update to the
server?
|
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 display data in a "GridView"
which is tied to an "ObjectDataSource".
In turn, this ObjectDatasource gets it's data from a strongly-typed
business object within my code.
| | |
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 showKens(){
var kenList = document.form1.ken;
var cityList = document.form1.city;
var kenId = kenList.options.value;
var cityName = '';
|
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 cross-posting.
I am trying to build a "checklist", where a user can navigate to an ASP page
on the intranet which shows a list of "questions" that the user can check
off. I am trying to figure out how to do this so that it is scalable, but I
am...
|
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 is working fine when a Enter username with Already Existing mail id then it is not taking.But only problem is msg is not displaying client side.
How to display msg .Pls find my code also.
|
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 format (0000-00-00-00.00.00.000000) for which i am getting error
Query
SELECT CASE WHEN MAX(ACTION_TS) IS NULL THEN '0000-00-00-00.00.00.000000' ELSE MAX(ACTION_TS) END FROM AUDIT , CARD WHERE ROW_ID = CARD_ACCT_SHORT_NO
Error
SQL0181N The...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
