Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Find the bug (sql) 4 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • "price" I presume is from the order_items table. If there are no orders and hence no order_items, the "price" field is a null (from the outer joins). sum(null) is null, and null is not less than 15, the comparison is null. This means you will not see the customers with no (completed) orders.
    • Even worse, if a client has completed orders, and at least one incomplete order, this gives a null price, and the sum will be null again.

      • What do you mean by "incomplete order"? I just added an order for a customer who already had orders, but did not add order items for it. The query results were the same, so I think I may have misunderstood you.

        • An "orders" record without corresponding "order_items" record(s) would be an "incomplete" order.

          Regarding your example, the result depends on whether there were other "complete" orders for the customer or not, and how your database treats nulls. Since a null means "totally unknown", anything plus null results in null, and comparing null with 15 is also null (neither true nor false). You should be using sum(coalesce(price,0)) for your definition of the field total.

Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.