SELECT dbo.Trans_Head.Doc_Date, dbo.Trans_Head.Document_No, dbo.Trans_Head.Account_Type, dbo.NewAccount.Surname + '' + dbo.NewAccount.First_Name AS Account_Holder, dbo.Trans_Details.Account_Name,SUM(ISNULL(CASE dbo.Trans_Details.Trans_type WHEN 'DR' THEN dbo.Trans_Details.Amount ELSE 0 END, 0)) Payemnt,
SUM(ISNULL(CASE dbo.Trans_Details.Trans_type WHEN 'CR' THEN dbo.Trans_Details.Amount ELSE 0 END, 0)) Receipts
FROM dbo.NewAccount INNER JOIN dbo.Trans_Details ON dbo.NewAccount.Account_no = dbo.Trans_Details.Account_No INNER JOIN
dbo.Trans_Head ON dbo.Trans_Details.Document__no = dbo.Trans_Head.Document_No INNER JOIN
dbo.Accounts ON dbo.NewAccount.Account_Type = dbo.Accounts.Account_Type
What we do is:
NVL(Value, 0) will return 0 if the value is null. We use the SQL function to do the same behaviour.
DECODE(CompareFiled, CompareValue, ReturnValueIfTrue) will return the ReturnValueIfTrue when the contents of the compare field is equals to the compare value. If they are not equal will return a NULL. We use the CASE CompareField WHEN CompareValue THEN ReturnValueIfTrue ELSE ReturnZeroValue END to obtain the value, but because in this example the ReturnValueIfTrue can be also NULL we still using the ISNULL SQL Function.
thanks for your reply which is very helpful but
after mofifying to inlude your code, am gettting the coulmn 'dbo.trans_head.Doc_Date' is invalid in the select statement list because it is not contained in either an aggregate function or the GROUP By clause,
When i remove the code, including the decode which ws in my code, i dont get the this eror during saving
when i removed the Group by which is the sum, then it works, if I included the sum, the eror ''dbo.trans_head.Doc_Date' is invalid in the select statement list because it is not contained in either an aggregate function or the GROUP By clause,
what is the cause.