The original query was:
<cfquery name="get_ab" datasource="MyDSN">
SELECT A.Name, B.Cost
FROM tableA AS A INNER JOIN
tableB AS B ON (A.ID = B.aID)
WHERE B.aID = 1
</cfquery>
Those were not working:
INNER JOIN
JOIN
tableA AS A
tableA A
tables fullname
Solution 1:
change "Cost" datatype from REAL to INT (or FLOAT) in SQL, worked with
the same query.
Solution 2:
SELECT SUM(B.Cost) AS Cost
FROM tableA AS A INNER JOIN
tableB AS B ON (A.ID = B.aID)
WHERE B.aID = 1
----- Original Message -----
From: "Joe Eugene" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, July 16, 2002 3:08 PM
Subject: RE: CFMX & SQL - simple query with weird error message
> I didnt see you mention anything about Sum(cost) or what not
> in your original post..! all you had was a join and where clause..
>
>
> -----Original Message-----
> From: Ye Wang [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 16, 2002 3:00 PM
> To: CF-Talk
> Subject: Re: CFMX & SQL - simple query with weird error message
>
>
> tableA.ID and tableB.aID are both INT. No problem with that.
>
> Two ways I could make this query work.
> 1. change Cost datatype from Real to INT in SQL;
> 2. SELECT SUM(B.cost) AS cost;
>
> Now with Solution 1, one has to user REAL datatype somehow, Cost is just a
> sample variable I used here.
> With Solution 2, I cannot select "A.Name" together with "B.Cost", unless I
> have some GROUP BY statement. However with large SELECT set sometimes one
> just doesn't want to use GROUP BY at all.
>
> Seems CFMX having problem retrieving REAL values from SQL?! I didn't have
> problem with INT in select statement. Or is this just a JDBC problem. This
> message of "Value can not be converted to requested type. " really
confused
> me.
>
>
> ----- Original Message -----
> From: "Jochem van Dieten" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Tuesday, July 16, 2002 2:46 PM
> Subject: Re: CFMX & SQL - simple query with weird error message
>
>
> > Ye Wang wrote:
> > > We just installed CFMX on a brand new server, W2k with SQL 2000. Most
> > > CF5 codes worked and no problems with stored procedures so far, except
a
> > > few errors on simple queries. One of them was quite weird.
> > >
> > > Name : VARCHAR(128)
> > > Cost: real
> > > tableA joins tableB on (tableA.ID = tableB.aID)
> >
> > The datatypes of tableA.ID and tableB.aID are probably more relevant.
> >
> > Jochem
> >
> >
>
>
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
