[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