Displaying null value

[shar7]

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

[Reshmi Jacob]

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

[shar7]

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.