whatwhatwhat whatwhatwhat - 4 months ago 8
SQL Question

SQL: Why is my query not returning values with a zero quantity?

For some reason, my query is only returning 26 rows instead of 69 rows. It is omitting all the rows where the quantity is zero. I think there is a problem with the

WHERE
clause. I wrote that obscene line because I didn't know how else to make the query return values based on 1 of these 2 scenarios:


  1. User supplies a quantity -> return only rows with that quantity

  2. User does not supply a quantity -> return all quantities



How can I make it so that the query returns all rows if the user does not supply a value and also maybe clean up my non-optimal
WHERE
clause?

DECLARE @lp varchar(30) = '1250116'
DECLARE @qty decimal

SELECT

t.lp_num,
t.qty

FROM

ISW_LPTrans AS t

WHERE

((t.qty >-5000000000 AND t.qty < 5000000000))


2nd attempt: this didn't work either though, it still only returns 26 rows.
Edit: Sorry! Copied the query from above but forgot to change the line that I fiddled with!

DECLARE @lp varchar(30) = '1250116'
DECLARE @qty decimal = NULL;

SELECT

t.lp_num,
t.qty

FROM

ISW_LPTrans AS t

WHERE

(@qty IS NULL OR t.qty = @qty)

Answer

I am not totally sure what you want for the where clause because your edits have changed the meaning a couple of times, but you can just build a case statement in your where clause and test for your conditions.

DECLARE @lp varchar(30) = '1250116'
DECLARE @qty decimal

SELECT

    t.lp_num,
    t.qty

FROM

    ISW_LPTrans AS t

WHERE
   (CASE
      WHEN @qty IS NULL THEN 1
      WHEN t.qty = @qty THEN 1
      ELSE 0
   END) = 1