use Perl

• are you sure that works? (Score:1)

  by bpphillips (5948)

  Shouldn't it be (as it appears was suggested in your earlier post):
  
  SUM( COALESCE( price, 0 ) ) AS total
  
  If the coalesce is outside the sume, it doesn't fix the underlying issue of apply an aggregate function on a set of data that may contain a NULL value. Or, perhaps I'm missing something...
  • Re: (Score:1)

    by derobert (9509)

    SUM ignores NULLs. So the sum of 1 and NULL is 1.

    SUM's behavior, of course, makes perfect sense: as 1+NULL yields NULL, so clearly the sum is 1.</sarcasm>
• Not quite right (Score:1)

  by huxtonr (8912)

  Now consider a simpler, yet silly, example: SELECT first_name FROM employee WHERE salary > 50000; What happens if the salary field is NULL? You'll get a list of employees whose known salary is NULL.

  I can't figure out what you meant to say there, but the query doesn't seem to go with the text.

  In this scenario, it is the case that everyone has a salary; you just don't know what some of them are. So here's the kicker: SELECT first_name FROM employee WHERE salary = salary; Th
  • Re: (Score:2)

    by Ovid (2709)

    Ack, fixed the typo regard "known salary is null".

    You don't know that though. Not within the terms of the database. If you did, they'd have a numeric salary and not NULL.

    That's entirely the point. Database "logic", when combined with NULLs, returns answers that we really know are not true. I don't mean "within the terms of the database". p = p is true, regardless of whether or not a poorly designed query language says otherwise.