[thelist] SQL Statement problem
rudy
r937 at interlog.com
Mon Feb 11 16:57:00 CST 2002
> I know in MS SQL Server you can't group by an alias.
hi howard
wacky, eh?
> In this case, your only solution is to use the same function
> in your GROUP BY clause.
maybe not the *only* solution -- i think a view will also work
;o)
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...
SELECT COALESCE (DATEPART(ww, approved_date)
, DATEPART(ww, terminate_date)) AS status_date
, COUNT(approved_date) AS apdate
, COUNT(terminate_date) AS tdate
FROM applications
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
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
<tip type="sql">
remove unnecessary parentheses in the WHERE clause, and insert parentheses
to avoid implicit evaluation precedence for compound conditions
</tip>
what you have is --
WHERE condition1 AND (condition2) OR (condition3) AND (condition4)
remove the parentheses that are not required, and add the ones that reflect
the default evaluation precedence --
WHERE ( condition1 AND condition2 ) OR ( condition3 AND condition4 )
are you sure that's what you want?
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
granted, this offers very little in additional benefits over coding the
large expression in the GROUP BY clause, but there are other examples where
defining a view is a much better approach...
rudy
More information about the thelist
mailing list