Trouble with nested select query

[skydvrz]

This works perfectly outside of DevArt in a MySQL query browser:

Code: Select all

select s.name, format(sv.value,(select digits from sensors where id=s.id)) as value
from sensors s, sensorvalues sv, packets p
where (select id from packets where packettype=2 order by timestamp desc limit 1) = p.id and sv.id=p.id and sv.sid=s.id

I am trying to get the most recent sensor readings and display them neatly formatted. Each sensor has different numeric precision formatting.

v8.2.5 chokes on the column named "digits" if I try to put it into a TMyQuery component. As you can see, there is no column named digits in my main select. Digits is a column name in the sensors table that holds the amount of numeric precision for a given sensor.

Ok, so I quote the "digits" field in the query to try to fool your parser. Now it always returns 0 for the format precision.

Any ideas or workarounds?

Thanks!

Kevin

[AlexP]

Hello,

We cannot reproduce the problem. The below sample returns correct data. Please modify it so that the problem can be reproduced and send it back to us.

Code: Select all

program Project4;

{$APPTYPE CONSOLE}

uses
  SysUtils, MyAccess;

var
  MyConnection: TMyConnection;
  MyQuery: TMyQuery;
begin
  MyConnection := TMyConnection.Create(nil);
  try
    MyConnection.ConnectString := '...';
    MyConnection.Connect;
    MyConnection.ExecSQL('DROP TABLE IF EXISTS test_table');
    MyConnection.ExecSQL('CREATE TABLE test_table ( id int(11) NOT NULL AUTO_INCREMENT,  digits int(11) DEFAULT NULL,  value double DEFAULT NULL,  PRIMARY KEY (id))');
    MyConnection.ExecSQL('INSERT INTO test_table(digits, value) VALUES(2, 123.1212)');
    MyQuery := TMyQuery.Create(nil);
    try
      MyQuery.Connection := MyConnection;
      MyQuery.SQL.Text := 'SELECT VALUE, (SELECT FORMAT(value, digits) FROM test_table WHERE id = t1.id) FROM test_table t1';
      MyQuery.Open;
      Writeln(Format('Real Value: %s - Format Value %s',[MyQuery.Fields[0].AsString, MyQuery.Fields[1].AsString]));
    finally
      MyQuery.Free;
    end;
  finally
    MyConnection.Free;
    readln;
  end;
end.