If you've been using Log Parser for any amount of time you're probably well versed with
Looking at a
SELECT clause against IIS logs, you may find something that looks like the following:
SELECT TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))) AS [LocalDate] , TO_TIME(TO_LOCALTIME(TO_TIMESTAMP(date, time))) AS [LocalTime] , c-ip, cs-uri-stem, cs-uri-query, sc-status, sc-bytes, cs-bytes, time-taken , cs(User-Agent), cs(Referer) [...]
Here we're taking the
time stored in the W3C logs as UTC and converting them to local time. We repeat a couple of items, specifically
In addition, if we want to use
LocalTimeTime in our
WHERE clause we'll find that we can't.
Let's modify our query slightly by taking advantage of the
USING clause. Since we have that expression we'd like to use in multiple places that's the best place to start.
SELECT TO_DATE(localDateTime) AS [LocalDate] , TO_TIME(localDateTime) AS [LocalTime] , c-ip, cs-uri-stem, cs-uri-query, sc-status, sc-bytes, cs-bytes, time-taken , cs(User-Agent), cs(Referer) USING TO_LOCALTIME(TO_TIMESTAMP(date, time)) AS [localDateTime] [...]
We've started by adding a
USING immediately after the
FROM). We then declared an alias just like we'd do in the
SELECT. In this case we're creating an alias for our shared expression. With this done we can replace the corresponding expressions within the
Now that we have our alias defined by the
USING clause we can use it in our
WHERE [localDateTime] > TO_TIMESTAMP('2011-09-01', 'yyyy-MM-dd')
Putting complex expressions in the
USING clause - like
CASE cs-uri-query WHEN null THEN cs-uri-stem ELSE STRCAT(STRCAT(cs-uri-stem, '?'), cs-uri-query) END AS [fullUrl] - can help keep your
SELECT nice and concise.
Generally, if you find yourself using an expression in more than one place in your query, ask yourself if it makes sense to move it into the
How do you use the USING clause?
Do you have complex expressions you typically put within a
USING clause? If so, please leave them in the comments, along with the log format.
Article provided by James Skemp.