Using filterSQL property of TmyTable

Using filterSQL property of TmyTable

Postby Guest » Sun 29 Jan 2006 23:56

Sorry if this is horribly newbie, but I am writing my very first real app using MySQl and Mydaq (4.00.1.6) I have a DBgrid, connected to a TmyTable,
There is a record in a table called calibration from which I want to extract which is denoted by the maximum value in a certain field, and display only that in the grid.

I tested what I want manually in SQL, and I can get the required dataset with this
select * from calibration where cal_reference_id= (select max (cal_reference_id) from calibration);

but any SQL I put in the filterSQL field of Tmytable which contains this MAX function creates an error #45000 FUNCTION not found uhtvisc.max

(Uhtvisc is the database in question)

I would be very grateful for any help I can get at this point - its the first time I have tried to use any DB programming in a Delphi app.

Thanks

Steve[/b]
Guest
 

Postby GEswin » Mon 30 Jan 2006 08:51

Well just remove the space after the MAX:

Code: Select all
select * from calibration where cal_reference_id= (select max(cal_reference_id) from calibration);
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby steveastrouk » Mon 30 Jan 2006 17:56

GEswin wrote:Well just remove the space after the MAX:

Code: Select all
select * from calibration where cal_reference_id= (select max(cal_reference_id) from calibration);


Thanks for your comment. It helps quite a bit, yet that is still invalid SQL, according to Delphi, when entered into the filterSQL property when the code is run.

Funnily enough, the original SQL shown was copied directly from the MySQL CLI when I tested what I wanted to do, and of course it worked fine there.

As SQL in a Tmyquery object though, it works fine as well.

Anyway, you have definitely got me accelerating up the learning curve.

Thanks again
Steve
steveastrouk
 
Posts: 6
Joined: Sun 29 Jan 2006 23:47

Postby GEswin » Mon 30 Jan 2006 18:05

Well in FilterSQL afaik you only have to put the WHERE part, without the where... it would be:

Code: Select all
cal_reference_id= (select max(cal_reference_id) from calibration)


But as you say, using TmyQuery solved the problem ,and i can suggest you better use TmyQuery on most cases, except when you want to see all table (TMyTable is a wrapper of TMyQuery).

Regards
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby steveastrouk » Tue 31 Jan 2006 18:30

GEswin wrote: But as you say, using TmyQuery solved the problem ,and i can suggest you better use TmyQuery on most cases, except when you want to see all table (TMyTable is a wrapper of TMyQuery).


Pieter,
Thank you very much for your help. I have finally moved off the start blocks.
Steve
steveastrouk
 
Posts: 6
Joined: Sun 29 Jan 2006 23:47


Return to MySQL Data Access Components