Do you mention that no record exists in either of the two tables, OPCH or ORIN ?
If there is no record in one of the tables, the result you get is null, so I suggest that you can directly judge the subtracted content by using
case when, and make it equal to 0 when the return value is null, which will be more convenient.
ps : please pay attention to some details, it doesn't need a closing bracket after the parameter like : @frDate}
The specific content is as follows:
SELECT '1', CASE WHEN ( (SELECT SUM(DocTotal) FROM OPCH where DocDate = @frDate )-(SELECT SUM(DocTotal) FROM ORIN where DocDate = @frDate) ) IS NULL THEN 0 ELSE ( (SELECT SUM(DocTotal) FROM OPCH where DocDate = @frDate )-(SELECT SUM(DocTotal) FROM ORIN where DocDate = @frDate) ) end as Amount
Best Regards,
YongQing.
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
140 Points
518 Posts
Query
Nov 27, 2019 04:55 AM|jsshivalik|LINK
Hi
I have below query .
SELECT '1', (SELECT SUM(DocTotal) FROM OPCH where DocDate = @frDate} )-(SELECT SUM(DocTotal) FROM ORIN where DocDate = @frDate}) as Amount
I want if no record exists then it should display like below
Selecct '1',0 as AMount
Thanks
Contributor
3710 Points
1043 Posts
Re: Query
Nov 27, 2019 07:08 AM|Yongqing Yu|LINK
Hi jsshivalik,
Do you mention that no record exists in either of the two tables, OPCH or ORIN ?
If there is no record in one of the tables, the result you get is null, so I suggest that you can directly judge the subtracted content by using case when, and make it equal to 0 when the return value is null, which will be more convenient.
ps : please pay attention to some details, it doesn't need a closing bracket after the parameter like : @frDate}
The specific content is as follows:
Best Regards,
YongQing.
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.