[thelist] SQL Statement problem

rudy r937 at interlog.com
Mon Feb 11 21:17:01 CST 2002


> Frickin' SQL Server query builder keeps taking liberties with
> my queries..

well, seth, it's kinda hard to imagine that it knew the level of AND and
OR evaluations that you *meant* to have, but substituted parentheses
*that changed the semantic intent* -- i just don't believe it does that

i'm sorry to make such a big deal about this but perhaps you missed the
implication in my last post

referring to your original query, would a row with terminate_date
02/02/2002 and terminate_type 'declined' and company_key 'FU'
satisfy the WHERE clause?

if you think the answer is no because of the 'FU'
you'd better look at your ANDs and ORs again


now, on the subject of views...

view *definitions* are stored in the database catalog, but they don't
actually have any data

(okay, microsoft has introduced the concept of a "materialized" view in a
recent version of sql/server, but let's not go there...)

when you write a query against a view, the query definition and view
definition are "merged" and the resultant database request is executed

there's no appreciable difference in compile time (at least, none that
you're gonna be able to measure accurately) between a huge messy query
and a simple query of a messy view

it's the same thing as the difference between a subquery and a join --
the database engine is going to resolve how to execute it, and you needn't
worry about it

if you find yourself writing the same complex expression into all your
queries, like

          COALESCE (DATEPART(ww, approved_date)
                , DATEPART(ww, terminate_date))

or

         Trim(LastName) + ', ' + Trim(FirstName)

then by all means, create views to simplify your queries

views are also extremely useful for "hiding" other messiness, like WHERE
clauses

for example, how do you find the average sales by department, as well as
the name and amount of the person who sold the most in each department?

piece of cake if the top sales people for each department are defined by a
view

      create view topsalespeople
         as
            select salesperson, department, salesamount
               from salestable X
                  where salesamount =
                       ( select max(salesamount)
                            from salestable
                          where department = X.department)

then the query becomes

  select T.department, avg(T.salesamount), V.salesperson, V.salesamount
    from salestable T
          , topsalespeople V
    where T.department = V.department
  group by T.department, V.salesperson, V.salesamount

okay, i can hear you saying, but couldn't you write the query without the
view?

sure, but it's ugly    ;o)


rudy

p.s. caution: the above example not tested





More information about the thelist mailing list