-2

We discovered a bug in our program where there are some instances of a doubled value. I am trying to write a query to find all instances of this doubled value.

Right now I am using

    WHERE 
(GRIR.ReceivedQty > GRIR.OrderedQty)

And this shows any instance where the "Received" is greater than the "Ordered" but it's returning lots of data that I don't need. (e.g. 41 received where 40 ordered, this is fine and non-issue)

How can I compare my Received against Ordered where the Received is exactly double the Ordered value. (e.g. 8 received and 4 ordered)

Thank you for your time.

1
2

Your WHERE clause can include basic math like this, e.g.

WHERE GRIR.ReceivedQty = 2.0 * GRIR.OrderedQty;

...or...

WHERE GRIR.OrderedQty = GRIR.ReceivedQty / 2.0;

(And this db<>fiddle demonstrates why 2.0 is more accurate than 2, assuming your columns are in the int family.)

2
  • I like the demonstration of the integer division. Another possible example: using WHERE GRIR.ReceivedQty / GRIR.OrderedQty = 2 returns one more row ;) Sep 24 '21 at 7:07
  • I would recommend using 2e or 2e0 (float literals) as opposed to 2.0. 2.0 is a numeric literal. If both columns are float sql will probably do the right thing, but it can do silly things if one side is numeric/decimal.
    – Michael B
    Sep 25 '21 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.