locked
Query RRS feed

  • Question

  • User-797751191 posted

    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

    Wednesday, November 27, 2019 4:55 AM

Answers

  • User665608656 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 27, 2019 7:08 AM