[thelist] SQL Statement problem

Seth Bienek seth at sethbienek.com
Mon Feb 11 17:23:00 CST 2002


Hi rudy!


> maybe not the *only* solution -- i think a view will also work

Hmm..

> seth, in your original query, are you sure you're happy with the default
> evaluation sequence for the ANDs and ORs?  looks a little suspicious to
> me...

Frickin' SQL Server query builder keeps taking liberties with my queries.. It's
like trying to build a website using FrontPage. :)  I need to hunt down if there
is a way to turn off the automatic reformatting it does every time you run a
query..

> seems to me that this must've been produced by some query tool, because
> there really is no need to put those parentheses around simple conditions

Uhh.. Kinda.. I pasted my query into the SQL Server query building tool because
it's quicker to debug than running the dynamic page I'll be using it on..  I
keep having to go back and remove those extra parens and re-space everything.

> okay, now as to the view
>
> a simple view involves just renaming columns, and does not
> involve WHERE conditions --
>
>   create view sethbaby
>         (status_date, apdate, tdate)
>     as
>       select
>         COALESCE (DATEPART(ww, approved_date)
>                 , DATEPART(ww, terminate_date))
>       , approved_date
>       , terminate_date
>    FROM applications
>
> supply the WHERE and GROUP BY clauses when using the view --
>
>   select status_date
>       , COUNT(apdate) as apdate
>       , COUNT(tdate) as tdate
>    FROM sethbaby
>   WHERE company_key = 'LI'
>     AND (approved_date BETWEEN '01/01/2001'
>                            AND '01/01/2002')
>      OR (terminate_date BETWEEN '01/01/2001'
>                             AND '01/01/2002')
>     AND (terminate_type = 'declined')
>   GROUP BY
>         status_date

This is a great idea.  I will be running a multitude of reports that will be
based on this key data, so it seems like a sound approach.

One question.  When I create a view, is it stored on the SQL server?  More
importantly, is it available across requests, or indefinitely, or only for the
local request?  Do I access it as a "virtual table". just using the name given
it in the create view statement?

Just want to clarify here before I dive in.

THANK YOU!

Seth





More information about the thelist mailing list