nickjfox
asked on
Delphi SQL 'And/Or' Problem
I want the code to return 18-year-olds who scored grade A,B or C. Instead it gives me all ages who scored A, B, Or C. I'm confused by the use of 'Where/And/Or' in an SQL statement, particularly applied to Delphi code. Help much appreciated.
Qresults.Active:=True;
Qresults.SQL.Clear;
Qresults.SQL.Add('SELECT * FROM ResultsTable');
Qresults.SQL.Add('WHERE age:=+IntToStr(18));
Qresults.SQL.Add('AND Grade='+QuotedStr('A'));
Qresults.SQL.Add('OR Grade='+QuotedStr('B'));
Qresults.SQL.Add('OR Grade='+QuotedStr('C'));
Qresults.Open;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, I gave a simplified version of my problem in the hope that once I grasped the concept I'd be able to do the rest myself. So, thanks, to both of you, I'm almost there. But I still seem a bracket short? Or one bracket too many? I count five extra brackets but it won't work.
QResullts.SQL.Clear;
QResullts.SQL.Add('SELECT * FROM ResultsTable');
QResullts.SQL.Add('WHERE (age<='+IntToStr(18));
QResullts.SQL.Add('AND (passed>'+IntToStr(0));
QResullts.SQL.Add('AND (pass_Type<>null');
QResullts.SQL.Add('AND (Overwork<>null');
QResullts.SQL.Add(' AND (Grade='+QuotedStr('A'));
QResullts.SQL.Add(' OR Grade='+QuotedStr('G'));
QResullts.SQL.Add(' OR Grade='+QuotedStr('I'));
QResullts.SQL.Add(' OR Grade='+QuotedStr('HA'));
QResullts.SQL.Add(' OR Grade='+QuotedStr('L'));
QResullts.SQL.Add(' OR Grade='+QuotedStr('P'));
QResullts.SQL.Add(' OR Grade='+QuotedStr('Q'));
QResullts.SQL.Add(' OR Grade='+QuotedStr('R'));
QResullts.SQL.Add(' OR Grade='+QuotedStr('T'));
QResullts.SQL.Add(' OR Grade='+QuotedStr('X')+')))))');
QResullts.Open;
ASKER
No, my mistake, the code as last posted works fine. It was something above causing a problem.
you will produce a lot of problems on the database this way.
use bind variables, or parameters !
next to that consider using ALT+mouse drag to select the sql text within the quotes:
(only works in advanced editors like in Delphi)
use bind variables, or parameters !
next to that consider using ALT+mouse drag to select the sql text within the quotes:
(only works in advanced editors like in Delphi)
QResults.SQL.Text :=
'SELECT * FROM ResultsTable '+
'WHERE (age<= :minage) '+
' AND (passed > :minpass) '+
' AND (pass_Type is not null) '+
' AND (Overwork is not null) ';
// rest of lines
QResults.Prepare;
QResults.ParamByName('minage').AsInteger := 18;
QResults.ParamByName('minpass').AsInteger := 0;
QResults.Open;
@Geert_Gruwez,
Actually, if this is done on the fly and prepared every time, it isn't going to matter a lot one way or the other.
If the SQL were set up in a T(ADO)Query component on a DataModule and prepared there so that it was sitting around waiting to be used or if it were set up outside of a loop and then the parameters set and the query executed multiple times within the loop, your method would be much more efficent.
In fact, if it were set up in a stored procedure, then that would be still more efficient.
Actually, if this is done on the fly and prepared every time, it isn't going to matter a lot one way or the other.
If the SQL were set up in a T(ADO)Query component on a DataModule and prepared there so that it was sitting around waiting to be used or if it were set up outside of a loop and then the parameters set and the query executed multiple times within the loop, your method would be much more efficent.
In fact, if it were set up in a stored procedure, then that would be still more efficient.
Qresults.Active:=True;
Qresults.SQL.Clear;
Qresults.SQL.Add('SELECT * FROM ResultsTable');
Qresults.SQL.Add(' WHERE (age:='+IntToStr(18));
Qresults.SQL.Add(' AND (Grade='+QuotedStr('A'));
Qresults.SQL.Add(' OR Grade='+QuotedStr('B'));
Qresults.SQL.Add(' OR Grade='+QuotedStr('C')+'))
Qresults.Open;
Regards,
B.