Guys i have been trying to make this work for quite some time but it is not showing any difference.
I have tried with Nz(), Coalesce, ISNULL but it is not working when i run the code. Here is the code:
<asp:SqlDataSource ID="dsGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MyConn %>" ProviderName="<%$ ConnectionStrings:MyConn.ProviderName %>"
SelectCommand="SELECT d.dept_ID, d.dept_name, c.cc_ID, c.costCentre_name, ISNULL(i.Net, 0) - ISNULL(u.Net, 0) AS Balance
FROM ((((department d)
INNER JOIN costCentre c ON d.dept_ID=c.dept_ID)
LEFT JOIN (SELECT cc_ID, SUM(amount) AS Net FROM monthlyIncome GROUP BY cc_ID) i ON c.cc_ID=i.cc_ID)
LEFT JOIN (SELECT cc_ID, SUM(amount) AS Net FROM utilization GROUP BY cc_ID) u ON c.cc_ID=u.cc_ID)"
FilterExpression="Convert(dept_ID, 'System.String') like '{0}%'">
<FilterParameters>
<asp:ControlParameter Name="dept_ID" ControlID="ddlDept" PropertyName="SelectedValue" />
</FilterParameters>
</asp:SqlDataSource>
U guys av any idea to make this particular part of code to work:
ISNULL(i.Net, 0) - ISNULL(u.Net, 0) AS Balance
Thanks in advance!
Please mark as 'answer' if it is helpful. Thanks in advance!
SELECT d.dept_ID, d.dept_name, c.cc_ID, c.costCentre_name, ISNULL(i.Net, 0) - ISNULL(u.Net, 0) AS Balance
FROM ((((department d)
INNER JOIN costCentre c ON d.dept_ID=c.dept_ID)
LEFT JOIN (SELECT cc_ID, SUM(amount) AS Net FROM monthlyIncome GROUP BY cc_ID) i ON c.cc_ID=i.cc_ID)
LEFT JOIN (SELECT cc_ID, SUM(amount) AS Net FROM utilization GROUP BY cc_ID) u ON c.cc_ID=u.cc_ID)
Why are you using these many brackets around the table and the joins? Try running modified version of the query. Your isnull syntax looks fine.
SELECT d.dept_ID ,
d.dept_name ,
c.cc_ID ,
c.costCentre_name ,
ISNULL(i.Net , 0) - ISNULL(u.Net , 0) AS Balance
FROM department d
INNER JOIN costCentre c
ON d.dept_ID = c.dept_ID
LEFT JOIN (
SELECT cc_ID ,
SUM(amount) AS Net
FROM monthlyIncome
GROUP BY cc_ID
) i
ON c.cc_ID = i.cc_ID
LEFT JOIN (
SELECT cc_ID ,
SUM(amount) AS Net
FROM utilization
GROUP BY cc_ID
) u
ON c.cc_ID = u.cc_ID
Why are you using these many brackets around the table and the joins? Try running modified version of the query. Your isnull syntax looks fine.
Hello mate. Initially the query was like what u av posted but it dun work til it do the parentheses. It works in this way wen i try it on different SSMS.
Please mark as 'answer' if it is helpful. Thanks in advance!
NIthya Natha...
Member
6 Points
59 Posts
Ways to run the SQL statement
May 02, 2012 09:48 AM|LINK
Guys i have been trying to make this work for quite some time but it is not showing any difference.
I have tried with Nz(), Coalesce, ISNULL but it is not working when i run the code. Here is the code:
<asp:SqlDataSource ID="dsGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MyConn %>" ProviderName="<%$ ConnectionStrings:MyConn.ProviderName %>" SelectCommand="SELECT d.dept_ID, d.dept_name, c.cc_ID, c.costCentre_name, ISNULL(i.Net, 0) - ISNULL(u.Net, 0) AS Balance FROM ((((department d) INNER JOIN costCentre c ON d.dept_ID=c.dept_ID) LEFT JOIN (SELECT cc_ID, SUM(amount) AS Net FROM monthlyIncome GROUP BY cc_ID) i ON c.cc_ID=i.cc_ID) LEFT JOIN (SELECT cc_ID, SUM(amount) AS Net FROM utilization GROUP BY cc_ID) u ON c.cc_ID=u.cc_ID)" FilterExpression="Convert(dept_ID, 'System.String') like '{0}%'"> <FilterParameters> <asp:ControlParameter Name="dept_ID" ControlID="ddlDept" PropertyName="SelectedValue" /> </FilterParameters> </asp:SqlDataSource>U guys av any idea to make this particular part of code to work:
ISNULL(i.Net, 0) - ISNULL(u.Net, 0) AS Balance
Thanks in advance!
patuary
Member
425 Points
143 Posts
Re: Ways to run the SQL statement
May 02, 2012 10:06 AM|LINK
try to cast
ISNULL(i.Net, 0) - ISNULL(u.Net, 0) at decimal with precision. like: CONVERT(DECIMAL(10,2), ISNULL(i.Net, 0) - ISNULL(u.Net, 0)) as Balance
dhimant
Star
8170 Points
1152 Posts
Re: Ways to run the SQL statement
May 02, 2012 10:07 AM|LINK
Why are you using these many brackets around the table and the joins? Try running modified version of the query. Your isnull syntax looks fine.
SELECT d.dept_ID , d.dept_name , c.cc_ID , c.costCentre_name , ISNULL(i.Net , 0) - ISNULL(u.Net , 0) AS Balance FROM department d INNER JOIN costCentre c ON d.dept_ID = c.dept_ID LEFT JOIN ( SELECT cc_ID , SUM(amount) AS Net FROM monthlyIncome GROUP BY cc_ID ) i ON c.cc_ID = i.cc_ID LEFT JOIN ( SELECT cc_ID , SUM(amount) AS Net FROM utilization GROUP BY cc_ID ) u ON c.cc_ID = u.cc_IDDhimant Trivedi
"When the going gets tough, tough gets going."
"Mark as Answer" the post(s) which helped you solve the problem
NIthya Natha...
Member
6 Points
59 Posts
Re: Ways to run the SQL statement
May 03, 2012 01:51 AM|LINK
I have tried the way u suggested but im getting this error:
Wrong number of arguments used with function in query expression 'CONVERT(DECIMAL(10, 2), ISNULL(i.Net, 0) - ISNULL(u.Net, 0))'.
Any idea?
NIthya Natha...
Member
6 Points
59 Posts
Re: Ways to run the SQL statement
May 03, 2012 01:53 AM|LINK
Hello mate. Initially the query was like what u av posted but it dun work til it do the parentheses. It works in this way wen i try it on different SSMS.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Ways to run the SQL statement
May 04, 2012 01:49 AM|LINK
Hello：）
Please first make sure that your i.NET or u.NET can be really changed to type of decimal，and then you can do this following thing：
CONVERT(DECIMAL(10, 2), ISNULL(i.Net, 0))-CONVERT(DECIMAL(10, 2), ISNULL(u.Net, 0))