Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Improvement to suggested solution

    Liuk,

    You posted a problem a few days ago. (http://www.dbforums.com/showthread.php?t=1619016)

    The suggested solution has a flaw that it will not work when the effective_to date is null.

    I had posted the improvement, but for some strange reason, the changes were removed and the thread was closed! (Any reasons why, Rudy?)

    Ravi

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    There was nothing wrong with Rudy's solution. Your solution was also workable.
    Get over it already.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    The proposed solution gives incorrect results when the effective_to date is null.

    If wrong results are acceptable, then the solution had "nothing wrong" with it, except the results of the query, of course. The author was gracious enough to admit it yesterday.

    Do try to accept the fact that the first solution suggested was incomplete, blindman.

    Ravi

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it was incomplete

    it should have said
    Code:
    WHERE '2002-11-15' 
      between effective_from 
      and coalesce(effective_to,'2937-01-01')
    notice that this "dummy date" is not stored in the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rajiravi
    ... but for some strange reason, the changes were removed and the thread was closed! (Any reasons why, Rudy?)
    probably because the insults were unprofessional

    just drop it, okay?

    it was not i who removed the posts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Quote Originally Posted by r937
    probably because the insults were unprofessional

    just drop it, okay?

    it was not i who removed the posts
    Yes, I agree. The threats to remove dissenters and insults from one party were unprofessional. Statements of fact are never unprofessional. Admonition to question everything, no matter who the source is, is not unprofessional.

    Ravi

    PS: Now I'm dropping it and will reply only to technical matters.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The basic tenet of your solution was that all queries are simplified by omitting nulls. My response (also removed) was that this is not *always* the case as implied in your post. Of course replacing nulls with a default does handle queries where you would want to treat nulls as a known value.

    But you didn't point out that there are also other queries that are complicated by it. For example - show the previous address for all (or a particular) contact(s). Not insurmountable of course. Nor though were the examples you presented.

    The suggested solution was not IMO flawed - it has "features" which require consideration. As does yours. If it is flawed- so is yours. Rudy did present his suggestion as an option (quote: "one common way....").

    The null debate goes on and on. I'm not a fan of either presented as dogma but nor am I a fan of one being considered costless compared to another.

    I can also confirm, in another capacity, that Rudy did not start censoring the thread.

    It would be very pleasing for all concerned if this could (should it continue) remain a cordial thread even if it just becomes yet another one of the null vs no nulls debates.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    The suggested solution was not IMO flawed - it has "features" which require consideration. As does yours. If it is flawed- so is yours. Rudy did present his suggestion as an option (quote: "one common way....").
    The solution suggested earlier was intended to identify the version effective at the specified time. The suggested query fails to achieve its intended purpose. Mine does. Case closed.

    As far as complexity of other queries is concerned, please give an example where using non-null values results in a more complex query compared to queries needed to handle nulls.

    My assertion was that queries become simpler, not that all queries become "simple".

    Until you provide an example to demonstrate your assertion, let us not belabour the point.
    Rudy himself was gracious enough to admit that his query was incomplete, and has yielded that minor point. Why can't others accept that? We all make mistakes, no big deal. The important point I was trying to convey was that ensuring that most of the columns in the database tables are not nullable, (but not necessarily all columns) results in simpler queries and in avoiding small nuances that one would have to worry about with nulls.

    For me the question of missing a small nuance in a query is something anybody can do, no big stuff. After all, this is a design forum, not a SQL solutions forum.
    More important is the actual design and the data model. As seen in this thread: http://www.dbforums.com/showthread.php?t=1619050

    Ravi

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    allow me to offer an improvement on my improvement
    Code:
    WHERE '2002-11-15' 
      between effective_from 
      and coalesce(effective_to,current_date)
    look ma, no dummy data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    While the latest suggestion works in most cases, there is one case where it will not.

    I worked on a system where the proposed interest rates would be stored a few days before they would come into effect. Thr "improved" query would not be able to retrieve them since the effective_from date is in the future.

    Strangely enough, the ones with the dummy effective_to dates would work in this case too.

    Admittedly, this is not quite common.

    But one of the points of avoiding nulls is that even unusual cases can be handled in the same way as the more common cases, mostly. One doesn't have to spend time on choosing the exact default date to use in the query. We can focus our attention on the actual problem in hand rather than the intricacies of SQL.

    Ravi

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rajiravi
    The solution suggested earlier was intended to identify the version effective at the specified time. The suggested query fails to achieve its intended purpose. Mine does. Case closed.
    I'm not talking about the specific query - I'm talking about the principle. You say the same at the bottom of your post so we are on the same page.


    Quote Originally Posted by rajiravi
    As far as complexity of other queries is concerned, please give an example where using non-null values results in a more complex query compared to queries needed to handle nulls.
    I thought I did - second sentence of the second paragraph.

    Quote Originally Posted by rajiravi
    My assertion was that queries become simpler, not that all queries become "simple".
    Did you read my post at all? That is the whole point of what I am saying. I never used the word simple. Neither is simple. Both make certain queries simpler. Neither make all queries simpler. Simple

    This isn't a full blown attack on what you said merely that I think you over-simplified things when making your point.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    there is no need for anyone to be defensive about postings containing information that is designed to be helpful to the original request. And if the postings were not designed to be helpfull then waht the heck were they doing here in the first place*

    it matters not one jot after the even whether "my solution was better than yours" or vice versa, it matters not one jot whether the answers are appropriate, although it helps.

    the purpose of this forums is supposed to provide help assistance and knowledge to others. it is not meant to be an academic debating chamber, although it seems that this and associated threads are becoming more like primary school.

    this forum is supposed to be about help, it isn't some college tutorial (despite numerous request here that students demand that someone does their coursework for them).

    Just move on. to continue the mud slinging just demeans all, it also demeans the forum itself.

    Personally I had hoped that after the thread which was heavily doctored to remove the personal diatribes and slanging matches that all parties concerned would leave it, or at least let thing cool down, but seemingly not

    FFS grow up and let it lie

    *unless of course they are designed to make the originial poster actually think about what they are trying to achieve, or what the coursework they have been set is actually meant to be testing.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    FFS grow up and let it lie
    Point taken. Will do.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •