'Unknown column in where clause' in Delphi but not MySQL

Question

I'm about ready to rip my hair out and take up poop flinging as a living!

I have a MySQL query which runs fine in MySQL

SELECT 
    p.ID AS DataID, 
    p.timestamp AS Timestamp, 
    sum(p.Value * v.Factor) AS Value,
    v.VirtualProfiles_id AS VProfileID 
FROM 
    profiledata p 
JOIN 
    profilevirtualjoin v 
ON 
    p.Profile_ID=v.Profile_ID 
WHERE 
    v.VirtualProfiles_id = 5
GROUP BY 
    v.Profile_ID, 
    p.timestamp 

But when I try to run this as a query in a SQLDataSet in Delphi

SQLDataSet2.Active := False;
SQLDataSet2.CommandText := 'SELECT p.ID AS DataID, p.timestamp AS Timestamp, sum(p.Value * v.Factor) AS Value,' +
   'v.VirtualProfiles_id AS VProfileID FROM profiledata p JOIN profilevirtualjoin v ON ' +
   'p.Profile_ID=v.Profile_ID WHERE v.VirtualProfiles_id = ' + InttoStr(5)
   +' GROUP BY v.Profile_ID, p.timestamp';
SQLDataSet2.Active := True;

I get an error

First chance exception at $765BC41F. Exception class TDBXError with message 'Unknown column 'v.VirtualProfiles_id' in 'where clause''. Process EMVS.exe (7556)

If anyone can offer any insight, I would be most appreciative.

EDIT:

I am using the MySQL server 5.5 and Delphi XE

What I am trying to do is this:

I have a tables as follows:

Profile:
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| ID          | int(11)      | NO   | PRI | NULL    | auto_increment |
| Designation | varchar(255) | YES  |     | NULL    |                |
| Description | text         | YES  |     | NULL    |                |
| UnitID      | int(11)      | NO   | PRI | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

profiledata
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| ID         | int(11)  | NO   | PRI | NULL    | auto_increment |
| TimeStamp  | datetime | YES  |     | NULL    |                |
| Value      | double   | YES  |     | NULL    |                |
| Profile_ID | int(11)  | NO   | PRI | NULL    |                |
+------------+----------+------+-----+---------+----------------+

Virtualprofiles
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| Designation | varchar(45)  | NO   |     | NULL    |                |
| Description | varchar(255) | YES  |     | NULL    |                |
| Unit_ID     | int(11)      | NO   | PRI | 0       |                |
+-------------+--------------+------+-----+---------+----------------+

profilevirtualjoin
+--------------------+---------+------+-----+---------+----------------+
| Field              | Type    | Null | Key | Default | Extra          |
+--------------------+---------+------+-----+---------+----------------+
| id                 | int(11) | NO   | PRI | NULL    | auto_increment |
| VirtualProfiles_id | int(11) | NO   | PRI | NULL    |                |
| Profile_ID         | int(11) | NO   | PRI | NULL    |                |
| Factor             | double  | NO   |     | 1       |                |
+--------------------+---------+------+-----+---------+----------------+

What I need to do is to "produce" a new profile which is the sum of a set of existing profiles. so, the data from the profiledata table must be summed where the ProfileID is included in the virtualprofile and the timestamp values are equal.

Answer 1

The Problem

So, the problem is this. The DBExpress driver provided with Delphi XE can only process Dynamic SQL queries, not MySQL Queries. Although Dynamic SQL is compatible with MySQL, it is not compatible the other way around.

Quoting from the MySQL Manual (sec 12.16.3):

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause.

The updated DBExpress driver included with Delphi XE3 includes specific support for MySQL code, and so this limitation is not applicable.

The Workaround

The solution to this problem is to create a view in MySQL server and to call it from Delphi using only Dynamic SQL compatible code. In the end the following workaround did the trick:

In MySQL:

CREATE VIEW `VirtualProfileData` AS
SELECT 
    p.ID AS DataID, 
    p.timestamp AS Timestamp, 
    sum(p.Value * v.Factor) AS Value,
    v.VirtualProfiles_id AS VProfileID 
FROM 
    profiledata p 
JOIN 
    profilevirtualjoin v 
ON 
    p.Profile_ID=v.Profile_ID 
GROUP BY 
    v.Profile_ID, 
    p.timestamp

Then in Delphi

SQLDataSet2.Active := False;
SQLDataSet2.CommandText := 'SELECT * FROM VirtualProfileData WHERE VProfileID = ' + InttoStr(5);
SQLDataSet2.Active := True;