sql server probelm

[jmensah]

please I need a sql command similar to decode in oracle becuase am getting an error when i verified as an sql command.

below is my code which, please help by correting the problem

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(NVL(DECODE(dbo.Trans_Details.Trans_type, 'DR',  dbo.Trans_Details.Amount), 0)) Payemnt,
SUM(NVL(DECODE(dbo.Trans_Details.Trans_type, 'CR',  dbo.Trans_Details.Amount), 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

[lolafuertes]

The most similar in SQL

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.

Hope this helps.

[jmensah]

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

[jmensah]

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.

[lolafuertes]

Sorry. My miss. In order to use the SUM function you will need to add to the end of the SQL sentence:

GROUP BY dbo.Trans_Head.Doc_Date, dbo.Trans_Head.Document_No, dbo.Trans_Head.Account_Type, dbo.NewAccount.Surname + '' + dbo.NewAccount.First_Name, dbo.Trans_Details.Account_Name

Hope this helps

[jmensah]

This works perfectly
thanks for your help