Re: Query with NULL
Date: Wed, 30 Dec 2009 09:17:12 -0800 (PST)
Message-ID: <5702e0cb-c0e3-49e9-92b5-14166650458f_at_a32g2000yqm.googlegroups.com>
On Dec 30, 7:36 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
>
> While one query could be written to handle this set of requirements, I
> would consider to make the correct desired result clear for future
> maintenance programmers just coding a simple SQL statement for each
> condition within a IF structure within the program. That way, based
> on which variables have values the correct desired SQL statement is
> submitted.
>
> I think taking the simple approach would likely greatly reduce the
> chance the CBO chooses anything but the optimal plan for the submitted
> query and again the simplier SQL will be unlikely to confuse any
> future maintenance programmer as to what result is desired.
I'd go for a CASE over IF for future maintenance, if it is a tossup. Had one of those yesterday, was so glad it was CASE - took 5 minutes, most of which was source control/comments, would have needed much more work if IF's. The issue is often that over time, the IF's get more complicated - people can't resist nesting and ELSE. This usually arises from changing business rules to account for all the situations not thought of (or admitted) originally. I know, you'd think there would be no difference since they can be logically equivalent... but programmer behavior removes the equivalency.
jg
-- _at_home.com is bogus. http://100waystokillapeep.blogspot.com/2009/04/psycho-bunny-killer.htmlReceived on Wed Dec 30 2009 - 11:17:12 CST