[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