Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: Should my where clause be confusing or NOT???

    heh,heh,heh...cute title, eh? I kill me...

    Anyway, The following two select statements return exactly the same results, and utilize exactly the same server resources to run (according to the generated explain execution plans).

    The where clause(s) in each are designed to exclude rows that are "empty" according to the designer of the db (don't ask...*rolleyes*).

    My question is this...which way would y'all code the select. I have always been "trained" to NOT use negative logic (heh,heh,heh), so originally wrote the select (which, in effect, translates to "get me everything but the empty rows") as:
    Code:
    SELECT	OSID AS OSID,
    	[Year] AS QtrYear,
    	1 AS QuarterNo,
    	QEPS4 AS EPS,
    	QSales4 AS Sales,
    	QInc4 AS Income,
    	CONVERT(varchar(10), QDate4, 101) AS EarningsReportDate
    FROM dbo.RSMsi 
    WHERE 	((QEPS4 <> 0.001 AND QEPS4 <> 0.0)
    		OR QSales4 <> 0.0
    		OR QInc4 <> 0.0
    		OR QDate4 <> '01/01/1900')
    but in retrospect, I think that's an ugly where clause, and think folks will look at it and think "WTF??? what does THAT mean?". So, I re-wrote the select as if it was looking for the empty rows, then slapped a "NOT" in front of the where clause to negate it in order to STILL say "Get me everything, but NOT the empty rows", as follows:
    Code:
    SELECT	OSID AS OSID,
    	[Year] AS QtrYear,
    	1 AS QuarterNo,
    	QEPS4 AS EPS,
    	QSales4 AS Sales,
    	QInc4 AS Income,
    	CONVERT(varchar(10), QDate4, 101) AS EarningsReportDate
    FROM dbo.RSMsi 
    WHERE 	NOT ((QEPS4 = 0.001 OR QEPS4 = 0.0)
    		AND QSales4 = 0.0
    		AND QInc4 = 0.0
    		AND QDate4 = '01/01/1900')
    whatcha think y'all? It's a style issue, I know...just curious for consensus' sake. Thanks!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    suppose the where-clause is twice this long, or perhaps double that too. Would you consider creating two views, one depending on the other?

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Hmmmm...not at all sure what you are talking about, or how it relates...

    but the short answer is YES! I would CONSIDER anything, but as a rule try to pick the best solution.

    I'm just not sure where you are coming from or headed with your response. Can you elaborate?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    As far as I know, there's no straight-forward answer. I don't think there's a rule of thumb that considers when the negative logic is perferred over the positive logic. Sometimes the flow of a method or procedure is thrown overboard with a single negative statement, making the procedure hard to follow. However, having to consider the exception is just as bad.

    However, in this case, my feel says that I'd go for the last one. The AND's and the equals make the statement faster to read. Not necessarily easier. But that's just this case. I don't know the flow or how it's used. And if the statement would be/get larger, there's probably a point where it's just to big to read quickly. A solution might be to push back the problem.

    Code:
    select OSID
    ,      QtrYear
    ,      QuarterNo
    ,      EPS
    ,      Sales
    ,      Income
    ,      EarningsReportDate
    from vwEmptyRows
    
    select... from vwNonEmptyRows
    is a lot easier to read. Another one would be to have a view that that expresses the row to be empty and have the where-clause use that;
    Code:
    select... from vwTheRowsThatIWant where isEmpty = 1
    
    select... from vwTheRowsThatIWant where isEmpty = 0
    In the first case, either view would still have the positive logic or the negative logic, but, since the name of the view expresses what it returns, someone dealing with that view should have no problem using it.
    My question is: would you have the vwNonEmptyRows refer to vwEmptyRows or write-out the whole statement?

    In the second case, apart from the quirky view name, the view might be easier to maintain or usable for other purposes.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ooo....ooo...ooo...pick me...

    Load the values into a table variable and do a NOT EXISTS
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Out of curiosity - is there any reason you don't simply annotate your code or are you something a connoisseur when it comes to SQL aesthetics?

    EDIT - BTW - nice SQL punning. There isn't really enough of that IMO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Posts
    492
    naah, I'm taking a management course called "MGMT1 - Pushing back the problem." =)

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Kaiowas
    My question is: would you have the vwNonEmptyRows refer to vwEmptyRows or write-out the whole statement?
    Thanks for the input...that at least lets me know what your first post was getting at.
    As far as your question is concerned, I think I would probably write out the whole statement. Where possible, I like the code to be self-documenting...and in a single, not-too-complicated select like this, I don't think it's "worth" hiding the logic too deeply.

    I read with interest the suggestion to use a view (or views) on this problem...maybe I don't use views as much as I should *LOL* but it would never have occurred to me to use a view on this issue. I guess I am still a little cloudy on what it gains me - - I guess readability primarily. Again, I just don't think the basic problem (readability) is worth the time and overhead of writing/maintaining a view to support readability. If there was another use for the view, then I'd consider it, but complexity for the sake of readability - a very slippery slope, IMHO.

    Thanks for the response though, and thanks alot for making me think so early in the morning .
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  9. #9
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Brett Kaiser
    Load the values into a table variable and do a NOT EXISTS
    Whatcha mean, Brett? What would I use as the comparison value?

    I'm sure this murders your intent...but I am just not seeing how this would be coded?
    Code:
    SELECT ... WHERE NOT EXISTS (SELECT * FROM @EmptyRowDefinitionTable)
    Wouldn't this always return the row from the table variable? (and the associated boolean TRUE)?

    I'm all for obfuscating code, but can't figure what you mean by this.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by pootle flump
    Out of curiosity - is there any reason you don't simply annotate your code or are you something a connoisseur when it comes to SQL aesthetics?
    Yeah, I have been accused on many occasions of over-annotating my code so that is not an issue.

    ...and I hate to think of myself as a connoisseur of ANYTHING, but yeah, I guess I like to have "pretty" code when it's not at the expense of performance. I just ain't not been taught through the years to use negative logic only where it isn't not avoidable...

    I kinda rank my coding goals in the following order:
    1) does the job
    2) performs as well as possible
    3) is understandable/maintainable (though not so much that I become dispensible )
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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