locked
Return zero if query doesn't return anything RRS feed

  • Question

  • How can I make test3 = 0 if the inside query doesn't return any result

    Select test1, test2 + (select number from table1) test3 from table2


    Thursday, October 13, 2016 3:51 PM

Answers

  • Select test1, test2 + case when (select number from table3) is null then 0  else (select number from table3)  end test3 from table2

    • Marked as answer by Lio1972 Thursday, October 13, 2016 4:11 PM
    Thursday, October 13, 2016 3:59 PM

All replies

  • Select test1, test2 + case when (select number from table3) is null then 0  else (select number from table3)  end test3 from table2

    • Marked as answer by Lio1972 Thursday, October 13, 2016 4:11 PM
    Thursday, October 13, 2016 3:59 PM
  • Or with a simple ISNULL function around it

    Select test1, ISNULL(test2 + (select number from table1), 0) test3 from table2


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by senthilswing Thursday, October 13, 2016 4:05 PM
    Thursday, October 13, 2016 4:03 PM
  • Olaf query will return a value of 0 when there is nothing in table1. My query will return the value in column test2 if there is nothing in table1.

    From what you describe it looks like you want Olaf's query.

    Thursday, October 13, 2016 4:10 PM