Query [Answered]RSS

• • Reply
  jsshivalik

  Member

  118 Points

  424 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

• • Reply
  Yongqing Yu

  Contributor

  3130 Points

  907 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:

  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.