Thanks

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks for "With" clause, made my life quite easy

not using formatting, just a function call inside a stored procedure. Thanks :)



--CELKO-- wrote:

Why are you formatting data in the back end?
12-Dec-07

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS. You are still writing COBOL, where everything is
characters and the display and internal storage are the same things.

Previous Posts In This Thread:

On Monday, December 10, 2007 5:26 PM
Matt Williamson wrote:

how to use custom column heading in where clause
Given something like the following:

select substring ((convert(varChar, g.startdate, 112)), 5, 2) + right
((convert(varChar, g.startdate, 112)), 2) + left ((convert(varChar,
g.startdate, 112)), 4) [StartDt],
substring ((convert(varChar, g.closedate, 112)), 5, 2) + right
((convert(varChar, g.closedate, 112)), 2) + left ((convert(varChar,
g.closedate, 112)), 4) [CloseDt],
h.*
from table1 g
left outer join table2 h on h.id = g.id
where [StartDt] > '2007-12-10'

How can I use [StartDt] in my where clause without using the whole
conversion formula?

TIA

Matt

On Monday, December 10, 2007 5:37 PM
David Portas wrote:

Re: how to use custom column heading in where clause
"Matt Williamson" <ih8spam@xxxxxxxxxxx> wrote in message
news:%23bwpuu3OIHA.820@xxxxxxxxxxxxxxxxxxxxxxx

One answer is:

SELECT *
FROM
(SELECT
SUBSTRING ((CONVERT(VARCHAR, g.startdate, 112)), 5, 2)
+ RIGHT((CONVERT(VARCHAR, g.startdate, 112)), 2)
+ LEFT ((CONVERT(VARCHAR, g.startdate, 112)), 4) [StartDt],
SUBSTRING ((CONVERT(VARCHAR, g.closedate, 112)), 5, 2)
+ RIGHT((CONVERT(VARCHAR, g.closedate, 112)), 2)
+ LEFT ((CONVERT(VARCHAR, g.closedate, 112)), 4) [CloseDt],
h.*
FROM table1 g
LEFT OUTER JOIN table2 h
ON h.id = g.id
) t
WHERE [StartDt] > '2007-12-10';


A far more efficient alternative would be:

SELECT
SUBSTRING ((CONVERT(VARCHAR, g.startdate, 112)), 5, 2)
+ RIGHT((CONVERT(VARCHAR, g.startdate, 112)), 2)
+ LEFT ((CONVERT(VARCHAR, g.startdate, 112)), 4) [StartDt],
SUBSTRING ((CONVERT(VARCHAR, g.closedate, 112)), 5, 2)
+ RIGHT((CONVERT(VARCHAR, g.closedate, 112)), 2)
+ LEFT ((CONVERT(VARCHAR, g.closedate, 112)), 4) [CloseDt],
h.*
FROM table1 g
LEFT OUTER JOIN table2 h
ON h.id = g.id
WHERE g.startdate >= '2007-12-11T00:00:00';

The second example can avoid computing the date conversion for every single
row in the table.

PS. Don't use SELECT * in production quality code.

--
David Portas

On Monday, December 10, 2007 5:45 PM
Erland Sommarskog wrote:

Re: how to use custom column heading in where clause
Matt Williamson (ih8spam@xxxxxxxxxxx) writes:

WITH MyDates AS (
select substring ((convert(varChar, g.startdate, 112)), 5, 2) + right
((convert(varChar, g.startdate, 112)), 2) + left ((convert(varChar,
g.startdate, 112)), 4) [StartDt],
substring ((convert(varChar, g.closedate, 112)), 5, 2) + right
((convert(varChar, g.closedate, 112)), 2) + left ((convert(varChar,
g.closedate, 112)), 4) [CloseDt]
FROM table1 g
)
SELECT g.StartDt, g.CloseDt
FROM MyDates
LEFT JOIN table2 h h.id = g.id
where g.StartDt > '2007-12-10'

This only works on SQL 2005. On SQL 2000 you can use:

SELECT g.StartDt, g.CloseDt
FROM (select substring ((convert(varChar, g.startdate, 112)), 5, 2) +
right ((convert(varChar, g.startdate, 112)), 2) +
left ((convert(varChar, g.startdate, 112)), 4) [StartDt],
substring ((convert(varChar, g.closedate, 112)), 5, 2) +
right ((convert(varChar, g.closedate, 112)), 2) +
left ((convert(varChar, g.closedate, 112)), 4) [CloseDt]
FROM table1) AS g
LEFT JOIN table2 h h.id = g.id
where g.StartDt > '2007-12-10'

The first thing is called a Common Table Expression, the second a
derived table. They are very much the same thing, except that a CTE
has a name, and can refer to itself. They are logical concepts and
the actual computation order is often different.

Now, I can't really make any sense of your example. You have a date
which you format as MMDDYYYY and then compared with a string that
starts with 2007. Since few years have 20 months, you will not find
very many rows.

Overall, avoid putting your columns in expressions in WHERE clauses,
at least if they are indexed. Once the column is in an expression,
the index is less useful.



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

On Tuesday, December 11, 2007 7:36 PM
Steve Dassin wrote:

Re: how to use custom column heading in where clause
"Matt Williamson" <ih8spam@xxxxxxxxxxx> wrote in message
news:%23bwpuu3OIHA.820@xxxxxxxxxxxxxxxxxxxxxxx

For an intelligient answer as to why sql can't recognize [StartDt] (as
defined in SELECT)
in WHERE see my post in the tread:
Tuesday, December 11, 2007 3:39 PM
microsoft.public.sqlserver.programming
'Give me a break, Re: Moving from Sybase SQL Anywhere v5.5 into MS SQL 2000'

www.beyondsql.blogspot.com

On Wednesday, December 12, 2007 5:42 AM
--CELKO-- wrote:

Why are you formatting data in the back end?
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS. You are still writing COBOL, where everything is
characters and the display and internal storage are the same things.

On Wednesday, December 16, 2009 12:01 PM
fwd123 Raza wrote:

Excellent
Thanks for the "With" method, I didn't know it, and it just got my life easier.

No formatting, just calling a function within a stored procedure and wanted to give it a label.

Thanks :)


Submitted via EggHeadCafe - Software Developer Portal of Choice
UrlFetch Command Line Utility
http://www.eggheadcafe.com/tutorials/aspnet/a9ed5c6b-63ac-480b-bb3d-1702c290836c/urlfetch-command-line-uti.aspx
.

Relevant Pages

  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: What if languages could be copyrighted?
    ... SQL includes both a query sublanguage and a data ... Projection by the GROUP BY clause. ...
    (sci.lang)
  • Re: Report Parameters - no results
    ... Allen Browne - Microsoft MVP. ... I did use the WHERE clause you suggested, and now I'm getting the "wrong ... > What else can I do to try to correct my sql? ... >> the WHERE clause in your query. ...
    (microsoft.public.access.reports)
  • Re: the "having" clause
    ... Start in the FROM clause and build a working table from all of the ... rest of the containing query. ... e) Go to the SELECT clause and construct the expressions in the list. ... As you can see, things happen "all at once" in SQL, not from left to ...
    (microsoft.public.sqlserver.programming)