Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: D10, FireDAC, MySQL


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 2 - Last Post: Aug 18, 2017 10:28 PM Last Post By: Jim Sawyer
Jim Sawyer

Posts: 214
Registered: 1/3/10
D10, FireDAC, MySQL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 18, 2017 7:55 PM
The following routine is intended to select a Minder record for only dogs (Patient.Species = 'CAN') and exclude other species.
It doesn't work - it selects all Minder records for all species. I've tried inner, right, and left joins with same result. Can someone
see where my problem is and communicate it to me?

   mQuery := TFDQuery.Create( AConnection );
   with mQuery do
   begin
     Connection := AConnection;
     SQL.Add( 'Update Minder ' );
     SQL.Add( 'join Patient ' );
     SQL.Add( 'on Patient.Patno = Minder.Patno ' );
     SQL.Add( 'set Minder.Picked = true ' );
     SQL.Add( 'where Patient.Species = ''CAN'' ' );
     SQL.Add( 'and Minder.Minded < :M and Minder.Period < 7 and Minder.Due between :S and :ES or Minder.Period >= 7 and Minder.Due between :St and :EL' );
     ParamByName('M').AsInteger := nMind;
     ParamByName('S').AsDate := dStart;
     ParamByName('ES').AsDate := dShortEnd;
     ParamByName('St').AsDate := dStart;
     ParamByName('EL').AsDate := dLongEnd;
     ExecSQL;
     Free;
   end;


Thanks,
Jim Sawyer
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: D10, FireDAC, MySQL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 18, 2017 10:15 PM   in response to: Jim Sawyer in response to: Jim Sawyer
Jim Sawyer wrote:

The following routine is intended to select a Minder record for only
dogs (Patient.Species = 'CAN') and exclude other species. It doesn't
work - it selects all Minder records for all species. I've tried
inner, right, and left joins with same result. Can someone see where
my problem is and communicate it to me?

   mQuery := TFDQuery.Create( AConnection );
   with mQuery do
   begin
     Connection := AConnection;
     SQL.Add( 'Update Minder ' );
     SQL.Add( 'join Patient ' );
     SQL.Add( 'on Patient.Patno = Minder.Patno ' );
     SQL.Add( 'set Minder.Picked = true ' );
     SQL.Add( 'where Patient.Species = ''CAN'' ' );
     SQL.Add( 'and Minder.Minded < :M and Minder.Period < 7 and
Minder.Due between :S and :ES or Minder.Period >= 7 and Minder.Due
between :St and :EL' );      ParamByName('M').AsInteger := nMind;
ParamByName('S').AsDate := dStart;      ParamByName('ES').AsDate :=
dShortEnd;      ParamByName('St').AsDate := dStart;
     ParamByName('EL').AsDate := dLongEnd;
     ExecSQL;
     Free;
   end;


We don't know what you've tried to code but you're missing some ().
Maybe you've tried to write:

SQL.Add( 'and Minder.Minded < :M and ((Minder.Period < 7 and
Minder.Due between :S and :ES) or (Minder.Period >= 7 and Minder.Due
between :St and :EL))' );

It's hard to tell from a select statement what was your intention.

Jim Sawyer

Posts: 214
Registered: 1/3/10
Re: D10, FireDAC, MySQL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 18, 2017 10:28 PM   in response to: Lajos Juhasz in response to: Lajos Juhasz
That part of the code is working. the PATIENT file has a species field which can contain 'CAN', 'FEL', EQU, etc. The patient table and the Minder table share a common field, PATNO.
I am trying to select Minder records that meet the criteia you addressed (that is working), but only if the Patient record with the matching field, PATNO, has the field SPECIES = 'CAN',
but all Minder records that meet the criteria you addressed whether the Patient.Species has a value of 'CAN' or something else.

Thanks for your help.

Lajos Juhasz wrote:
Jim Sawyer wrote:

The following routine is intended to select a Minder record for only
dogs (Patient.Species = 'CAN') and exclude other species. It doesn't
work - it selects all Minder records for all species. I've tried
inner, right, and left joins with same result. Can someone see where
my problem is and communicate it to me?

   mQuery := TFDQuery.Create( AConnection );
   with mQuery do
   begin
     Connection := AConnection;
     SQL.Add( 'Update Minder ' );
     SQL.Add( 'join Patient ' );
     SQL.Add( 'on Patient.Patno = Minder.Patno ' );
     SQL.Add( 'set Minder.Picked = true ' );
     SQL.Add( 'where Patient.Species = ''CAN'' ' );
     SQL.Add( 'and Minder.Minded < :M and Minder.Period < 7 and
Minder.Due between :S and :ES or Minder.Period >= 7 and Minder.Due
between :St and :EL' );      ParamByName('M').AsInteger := nMind;
ParamByName('S').AsDate := dStart;      ParamByName('ES').AsDate :=
dShortEnd;      ParamByName('St').AsDate := dStart;
     ParamByName('EL').AsDate := dLongEnd;
     ExecSQL;
     Free;
   end;


We don't know what you've tried to code but you're missing some ().
Maybe you've tried to write:

SQL.Add( 'and Minder.Minded < :M and ((Minder.Period < 7 and
Minder.Due between :S and :ES) or (Minder.Period >= 7 and Minder.Due
between :St and :EL))' );

It's hard to tell from a select statement what was your intention.

Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02