December 19, 2008 at 1:28 pm
I am trying to simple this query:
select field 1, (complicated sql involving, ISNULL, rtrim. left, REPLACE, etc)
field 2,
field 3
from
where field 1 = ......
group by field 1,...
I have to re-write 3 times, in full detail, the complicated sql representing "field 1". Is there an "as" or some such that would let me define "field 1" only once in the query and reuse that definition?
TIA,
Barkingdog
December 19, 2008 at 1:46 pm
Sure Can you post an example of your first one?
you can create a function
that does the trim, replace, and isnull (if your supplying a stating string on isnull)
See Create function in books online
December 19, 2008 at 1:55 pm
You can use a CTE or a derived table - here is an example using a CTE.
;WITH myCTE (field1, field2, field3, ...)
AS (SELECT {complicated sql...}
,field2
,field3
...
FROM ...
WHERE ...)
SELECT field1
,field2
,field3
FROM myCTE;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 22, 2008 at 1:45 am
Definitely go with the CTE or derived table solution, function will be slower.
If there is a chance that someone will need to use your code with SQL Server 2000, use derived table! CTE are available only from SQLS2005.
Derived table is actually the same as CTE, the only difference is that CTE is defined before the query in which you use it, and the query itself contains only reference to it. Derived table is defined inside the query - what you do is write a query, wrap it in brackets, give it an alias and use it as if it was a table (select from it, join it to another table etc.).
SELECT Q.col1, Q.mycomplicatedcolumn, MAX(Q.col2), SUM(Q.col3)
(select col1, (complicated sql involving, ISNULL, rtrim. left, REPLACE, etc) as mycomplicatedcolumn
col2,
col3
from sometable
where col1 = something) AS Q /*Q is the "derived table" - caution! computed columns must have aliases*/
GROUP BY Q.col1, Q.mycomplicatedcolumn
December 23, 2008 at 7:03 am
OR . . . . (you guys are making this harder than it needs to be)
Copy . . .
Paste . . .
😀
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
December 23, 2008 at 7:10 am
Right, of course you can copy and paste, but derived tables make the code easier to maintain - you only need to change it in one place if something changes in the complicated expression. I'm not speaking about SUM(col1) or a+b, but about some expression that is pretty long, especially if it uses a complicated CASE.
To me, and I agree that this is a matter of taste, the solution with derived table looks more compact and understandable.
December 23, 2008 at 7:53 pm
I like the solution using derived tabels but thanks to everyone who replied!
Barkingdog
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply