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);

[danten]

OK, ERROR:
"Unsupported Feature"

Delphi-Quellcode:

Query.Params[0].Name := 'start';
Query.Params[0].DataType := ftTimeStamp;
Query.Params[0].ParamType := ptUnknown;
Query.Params[0].Value := EncodeDate(2014, 2 ,17);
Query.Params[1].Name := 'end';
Query.Params[1].DataType := ftTimeStamp;
Query.Params[1].ParamType := ptUnknown;
Query.Params[1].Value := EncodeDate(2014, 2 ,20);

[DeddyH]

Did you try my (much shorter) code? What is the datatype of "WHEN"?

[mkinzler]

I would change the name when to a not reserved name. The paramters should be created automatically, when ParamCheck is true.

[danten]

Zitat:

Zitat von DeddyH (Beitrag 1250289)

Did you try my (much shorter) code? What is the datatype of "WHEN"?

"WHEN" =>> ftDateTime

Delphi-Quellcode:

SELECT 
  * 
FROM
  ATTENDANT 
WHERE
  "WHEN" BETWEEN :start1 AND :end1

New Error:
"Dynamic SQL Error.
SQL error code=-206
Column unknown
START1
At line 1, column 48."

[sx2008]

Zitat:

Zitat von mkinzler (Beitrag 1250295)

I would change the name when to a not reserved name.

WHEN is a reserved word.
Using reserved words in SQL could lead to VERY STRANGE errors.
I (and mkinzler) recommend to change the name of the field.

PS: don't set ParamType to ptUnknown! Set it to ptInput.

[danten]

:-D Thank you to all friends 8-)

Delphi-Quellcode:

function Tfrm_main.Attendant_Filter:boolean;
var
  myYear,myMonth,myDay:word;
  set_day, my_day,
  set_week,start_week,end_week,
  set_month,start_month,end_month,
  day_month,
  set_year, start_year,end_year:string;
begin

  DecodeDate(NOW, myYear, myMonth, myDay);

  ///////////////////////////////////////////////////////////

    (* Filter day *)
  my_day := DateToStr(NOW);
  set_day := '("WHEN" >= ' + QuotedStr(my_day) + ')';

  (* Filter week *)
  start_week := IntToStr(dayofweek(now -1))+'.'+ IntToStr(myMonth)+'.'+IntToStr(myYear);
  end_week := IntToStr(dayofweek(now -1)+7)+'.'+ IntToStr(myMonth)+'.'+IntToStr(myYear);

  set_week := '("WHEN" >= ' + QuotedStr(start_week)
              + ') and ("WHEN" <= ' + QuotedStr(end_week)
              + ')';

  (* Filter  month *)
  start_month := '1.'+ IntToStr(myMonth)+ '.' + IntToStr(myYear);
//  day_month := IntToStr(MonthDays[IsLeapYear(myYear)][2]); //
  end_month :=  '1.' + IntToStr(myMonth + 1) + '.' + IntToStr(myYear);

  set_month := '("WHEN" >= ' + QuotedStr(start_month)
              + ') and ("WHEN" <= ' + QuotedStr(end_month)
              + ')';

  (* Filter year*)
  start_year := '1.1.'+ IntToStr(myYear);
  end_year := '1.1.'+ IntToStr(myYear +1);

  set_year := '("WHEN" >= ' + QuotedStr(start_year)
              + ') and ("WHEN" <= ' + QuotedStr(end_year)
              + ')';

  ///////////////////////////////////////////////////////////////

  ShowMessage(QuotedStr(start_year) + ' : '+ QuotedStr(end_year));
  ShowMessage(QuotedStr(start_month) + ' : '+ QuotedStr(end_month));
  ShowMessage(QuotedStr(start_week) + ' : '+ QuotedStr(end_week));
  ShowMessage(QuotedStr(my_day)); 
  ........
  Result := True;

end;

[DeddyH]

Now set ShotDateFormat to e.g. "MM/DD/YYYY" and try it again.

[danten]

Thank you perfect. :)

Last question: How do I know the days last week.?
start_old_week := ????? return = 24.2.2014 ?????
end_old_week := ????? return = 2.3.2014 ?????

[DeddyH]

I don' t know what you mean exactly :gruebel:. You might have a look at the "DateUtils"-Unit, maybe there' s a function for your purpose.

[danten]

Find out last week, when it started and when it ended. Return date.

[DeddyH]

As I said, have a look at DateUtils. Possibly StartOfTheWeek and EndOfTheWeek are the functions you are looking for.

[danten]

OK.

Delphi-Quellcode:

var
  sod: TDateTime;
  set_week,start_week,end_week, set_old_week, start_old_week, end_old_week,sec: string;
  begin
(* Filter week *)
  start_week := IntToStr(dayofweek(now -1))+'.'+ IntToStr(myMonth)+'.'+IntToStr(myYear);
  end_week := IntToStr(dayofweek(now -1)+7)+'.'+ IntToStr(myMonth)+'.'+IntToStr(myYear);

  set_week := '("WHEN" >= ' + QuotedStr(start_week)
              + ') and ("WHEN" <= ' + QuotedStr(end_week)
              + ')';

  (* Filter old week *)
  sod := StrToDate(start_week) -7;
  sec := DateToStr(sod);

  start_old_week := sec;
  end_old_week := start_week;

  set_old_week := '("WHEN" >= ' + QuotedStr(start_old_week)
              + ') and ("WHEN" <= ' + QuotedStr(end_old_week)
              + ')';
.................