[thelist] SQL Chaos!!
darren
darren at web-bitch.co.uk
Wed Jan 29 06:13:14 CST 2003
On Wednesday, January 29, 2003 at 08:53, Martyn Haigh wrote:
MH> SELECT
MH> Sum(FieldA),
MH> Sum(FieldB),
MH> Sub(FieldC),
MH> (RIGHT('0' + cast(DatePart(dd,DateCreated) as varchar), 2) +'-'+ RIGHT('0'
MH> + cast(DatePart(mm,DateCreated) as varchar), 2) +'-'+
MH> cast(DatePart(yyyy,DateCreated) as varchar)) as GroupDate
MH> FROM InterestedForm, GeneralForm
MH> INNER JOIN GeneralForm ON InterestedForm.ID = GeneralForm.ID
MH> GROUP BY GroupDate
MH> But I'm experiencing the error of "tables / functions generalForm and
MH> generalForm have the same exposed name"
yeah, you're adding the same table into the query twice. i don't think
you'd need the generalForm table in your where clause, just in the join.
you're also not indicating which table you are pulling the columns from,
if you have the same column name in both tables, you will also get an
error.
i haven't been following this, but if you're using sql server, can't you
use convert and a date style to get the date format you want?? something
like:
convert(varchar(10), DateCreated, 105) as groupDate
should give a date in dd-mm-yyyy format.
so as an example (and assuming sql server!) - if you were getting fieldA,
B and C from the InterestedForm table and CreatedDate from the
GeneralForm table you would have something like:
SELECT
SUM(i.FieldA),
SUM(i.FieldB),
SUM(i.FieldC),
CONVERT(varchar(10), g.DateCreated, 105) as GroupDate
FROM
InterestedForm as i
INNER JOIN GeneralForm as g
ON i.ID = g.ID
hth,
darren
More information about the thelist
mailing list