[thelist] SQL Chaos!!
Joshua Olson
joshua at waetech.com
Wed Jan 29 07:21:01 CST 2003
----- Original Message -----
From: "Martyn Haigh" <martyn.haigh at virgincosmetics.com>
Sent: Wednesday, January 29, 2003 4:13 AM
> Joshua,
>
> How would I go about creating the view. I've got it so it returns the ID
> and then I'm a little stuck on how to return the formatted date, I can
> return the date parts (dd, mm, yyyy) but can seem to tie them all
together!
>
> Also - how would I then reference the view instead of the table.
Martyn,
Typically I don't try to overturn Rudy's advice, but since you are asking
about how to construct the view I am compelled to respond. The view can be
constructed using the following syntax:
CREATE VIEW [view_name] AS
SELECT ID, xxxxxxxxxxxxxxxxxxxxxx AS DateCreated
FROM GeneralForm
And the to quote rudy,
if it's access, use
Format(bookDate,'yyyy-mm-dd')
if it's sql server, use
convert(char(10),bookDate,120)
If you do a SELECT * FROM [view_name] you'll see the records of the view.
It acts almost exactly like a table from the database's point of view.
There are a few esoteric limitations to the use of a view, but you won't
encounter them in this problem.
So, to finish the puzzle swap the table with the view:
SELECT
[view_name].DateCreated
Sum(InterestForm.FieldA) AS FieldA,
Sum(InterestForm.FieldB) AS FieldB,
Sub(InterestForm.FieldC) AS FieldC
FROM InterestForm
INNER JOIN [view_name] ON InterestForm.ID = [view_name].ID
GROUP BY [view_name].DateCreated
That query is the same query as I presented before except that I've swapped
out the InterestForm table with the view and I qualified the field names for
clarity.
So, my curiosity has gotten the best of me.... what database are you using?
<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.868.0240
More information about the thelist
mailing list