Filtering two IBTable and insert or edit IBTable

[danten]

I need to recursively insert data from one table to another table by index (USERID =>> ID) common to both tables.
My function does not complete the operation.

Delphi-Quellcode:

function TFrm_main.Add_UserName:boolean;
var
  i:Integer;
begin
  ds_users.DataSet.First;
  for I := 0 to ds_users.DataSet.RecordCount -1 do
  begin
    Application.ProcessMessages;
    ds_attendant.DataSet.Filtered := False;
    ds_attendant.DataSet.Filter := 'USERID = '+QuotedStr(ds_users.DataSet.FieldByName('ID').Value);
    ds_attendant.DataSet.Filtered := True;
    Application.ProcessMessages;
  while not  ds_attendant.DataSet.Eof do
  begin
  if (ds_attendant.DataSet.RecordCount > 0) then
  begin
    Application.ProcessMessages;
    ds_attendant.DataSet.Edit;
    ds_attendant.DataSet.FieldByName('NAME').Value := ds_users.DataSet.FieldByName('USERNAME').Value;
    ds_attendant.DataSet.Post;
    Application.ProcessMessages;
  end else
  begin
    ds_users.DataSet.Next;;
  end;
  end;
    ds_users.DataSet.Next;
  end;
  Result := True;
  ds_attendant.DataSet.Filtered := False;
end;

[DeddyH]

This looks a little weird. Are you sure you navigate through both datasets simultaniously? Why not use SQL? Untested:

SQL-Code:

UPDATE
  ATTENDANT A
SET
  NAME = (
    SELECT 
      USERNAME
    FROM 
      USERS
    WHERE
      USERID = A.ID)

[danten]

Work, thanks :)
update attendant a set name = (select username from users where id = a.userid)

[danten]

Hey, they face a new problem of filtering.
I still have the error.
Use components IBTable and join the Firebird database.
I need to filter by date.
My code:

Delphi-Quellcode:

tab_attendant.Filter := 'select * from ATTENDANT where "WHEN" >= '+QuotedStr('17.2.2014 00:00:00')+' and "WHEN" < '+QuotedStr('20.2.2014 00:00:00');

[mkinzler]

Why an IBTable?
The Filter attribute don't asks for a SQL statement but only the filter condition

[danten]

OK, does not work example:
tab_attendant.Filter := 'WHEN >= '+QuotedStr('17.2.2014 00:00:00')+' and WHEN < '+QuotedStr('20.2.2014 00:00:00');

[mkinzler]

Is when a fieldname?
Is it part of the join?

[danten]

"WHEN" is FieldName.

[DeddyH]

I personally prefer using queries rather than tables. Using a statement like:

SQL-Code:

SELECT 
  *   
FROM
  ATTENDANT 
WHERE
  "WHEN" BETWEEN :start AND :end

and filling the parameters as follows:

Delphi-Quellcode:

Query.ParamByName('start').Value := EncodeDate(2014, 2 ,17);
Query.ParamByName('end').Value := EncodeDate(2014, 2, 20);
Query.Open;

should return the expected result.

[mkinzler]

To make it more flexible ( no filter) you could extend the when condition:

SQL-Code:

WHERE
( :start is null) or ("WHEN" BETWEEN :start AND :end);