Liuk,

You posted a problem a few days ago. (Patterns for things that change with time)

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

There was nothing wrong with Rudy's solution. Your solution was also workable.
Get over it already.

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

yes, it was incomplete

it should have saidnotice that this "dummy date" is not stored in the table

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.

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.

pootle flump ur codings are working excelent.

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: Tree-like "Nesting" DB Questions

Ravi

allow me to offer an improvement on my improvement
look ma, no dummy data

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

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.


I thought I did - second sentence of the second paragraph.

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.

pootle flump ur codings are working excelent.

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.

Point taken. Will do.

pootle flump ur codings are working excelent.