[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