editing field using SQL

I have dataset that has BCD fields.
How can I change value of one BCD field using another fields. Something like Field3 := Field1 * Field2. One important not type of Field1 and Field3 is BCD, type of Field2 is Long.
I need SQL code that makes it. I have already tried to do it with following code but it doesn't work.

Update test Set Field3=Field1*Field2
LVL 2
ferhadAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
esoftbgConnect With a Mentor Commented:
download             http://www.geocities.com/esoftbg/ee/Q_20946220.zip
there is the latest really working version
0
 
kretzschmarCommented:
which database?
0
 
mokuleCommented:
Query1: TQuery

Query1.SQL := 'UPDATE table SET Field3 = Field1*Field2';
Query1.ExecSQL;
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ferhadAuthor Commented:
Paradox kretzschmar
0
 
esoftbgCommented:
you can do that on extract data from the table:
SELECT Field1, Field2, Field1*Field2 as Multi_1_2 FROM TEST
instesd of to be stored into table

emil
0
 
Ivanov_GCommented:

  CAST ( FIELD2 AS ...data_type_you_need here...)
0
 
kretzschmarCommented:
>Paradox kretzschmar
currently i have no problem with
Update test Set Field3=Field1*Field2 (tested)!

what error do you get?

meikl ;-)
0
 
ferhadAuthor Commented:
Thanks mokule for your comment. I have already tested it. Its not giving me error but doesn't work also.

Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('Update test Set Field3=Field1*15');
Query1.ExecSQL;

Both Field3 and Field1 are in BCD types.
0
 
ferhadAuthor Commented:
To esoftbg
Thanks for your comment. But I am trying to change value of some field using other fields. For example Field3=Field1*15
============================================================
To kretzschmar
Its not giving error. Its just not working. I am using the following code

Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('Update test Set Field3=Field1*15');
Query1.ExecSQL;

If type of Field3 and Field1 is BCD its not working. If Long Integer it works. But I cant change the type of these fields.
0
 
kretzschmarCommented:
as stated,
works properly for me,
maybe its caused by different bde-settings

meikl ;-)
0
 
mokuleCommented:
Shouldn't be placed full path name for test table?
0
 
ferhadAuthor Commented:
i have never changed these settings. May be its caused by Delphi6 ? I am using Delphi6
0
 
esoftbgCommented:
Both examples below work fine with BCD fields in Paradox table:

procedure TForm1.SpeedButton1Click(Sender: TObject);
var
  I:      Integer;
begin
  Query1.Active := False;
  Query1.SQL.Text := 'SELECT Field1, Field2, Field3, Field1*Field2 as Multi_1_2 FROM TEST';
  for I := 0 to Query1.FieldCount-1 do
    Query1.Fields[I].Size := 48;
  Query1.Active := True;
end;

procedure TForm1.SpeedButton2Click(Sender: TObject);
begin
  Query1.Active := False;
  Query1.SQL.Text := 'UPDATE TEST SET Field3=Field1*Field2';
  Query1.ExecSQL;
end;
0
 
esoftbgCommented:
I tested with Delphi 7, Paradox 7
please ignore:
>  for I := 0 to Query1.FieldCount-1 do
>    Query1.Fields[I].Size := 48;
0
 
ferhadAuthor Commented:
mokule i have placed one Query component and in the DatabaseName property i am setting full path to this test.db. And i think if it would be path problem i gave me error. But its not giving me any error.
0
 
ferhadAuthor Commented:
I may work esoftbg but i am not trying to do it. I have Field3 and want to change its value into Field1*Field2 or Field1*15 dont matter. Field that you call Multi_1_2 is something virtual. But I want physically change value of Field3.
0
 
esoftbgCommented:
My second example above is:

procedure TForm1.SpeedButton2Click(Sender: TObject);
begin
  Query1.Active := False;
  Query1.SQL.Text := 'UPDATE TEST SET Field3=Field1*Field2';
  Query1.ExecSQL;
end;

it physically changes value of Field3 without problems under Delphi 7 and Paradox 7.
0
 
ferhadAuthor Commented:
In Delphi6 it works only with LongInteger fields. Is it not possible to do it with BCD fields in Delphi6 ?
0
 
esoftbgCommented:
I can upload my example application as it is (Delphi 7 and Paradox 7). And you can download it and taste it under Delphi 6. I am curious what will be the result ????
0
 
kretzschmarCommented:
it works for me also with the database-desktop,
--> its not a problem of delphi.

there should be something, which happens only by yours,
maybe localshare not properly set,
or the alias is mapped to subst-directory

tell a bit about your enviroment

meikl ;-)
0
 
Ivanov_GCommented:

    On ADOQuery1 on you form set EnableBCD = True;

    The on your column Field2 set FieldType = ftCalculated;

   ADOQuery1 has method OnCalcFields - make your calculation there.
0
 
esoftbgCommented:
I will be back after 8:30 hours. Bye for now....
0
 
ferhadAuthor Commented:
I gave this question to my teacher in university and he said me that it may be caused by different BDE settings. Now kretzchmar what should I change in BDE seetings in order to solve this problem ?

=====================================================

I will wait your answers esoftbg.
0
 
ferhadAuthor Commented:
I have opened BDE administrator and was looking for something like EnableBCD. I created alias with which i was testing some SQL codes. I found that its EnableBCD property equals to False. Actually I am not using this alias in my app. In DatabaseName property I have set direct path to data folder.
0
 
kretzschmarCommented:
this directory is a regular path, or is it maped?
0
 
esoftbgCommented:
I have some logical devices on my HDD using multi operational systems. Usualy I a working under Windows XP and Delphi 7. But I have Windows 98 where I just installed Delphi 6. I tested my example under Delphi 6 as it is just installed and it works fine.

emil
0
 
esoftbgCommented:
May be this problem is right in the Paradox table:
- if you have defined a unique index with Field3, it may fail during calculating (for example 2*6=12 and later 3*4=12). The unique index (if you have it) could not allow to be posted second equal of 12 value into Field3....
- some other reason in the Paradox table....
0
 
kretzschmarCommented:
in this cases, emil, he will get an exception
0
 
esoftbgCommented:
you are right meikl.... all of it may have a simple explanation, but for me it looks very bizarre yet....
0
 
ferhadAuthor Commented:
I will explain situation in my table. I have 4 fields:

               Type    Size    Key    
1. ID           +                  *
2. Field1     #         2
3. Field2     #         2
4. Field3     #         2
0
 
esoftbgCommented:
Try with:

               Type    Size    Key    
1. ID           +                  *
2. Field1     #         20
3. Field2     #         20
4. Field3     #         20
0
 
ferhadAuthor Commented:
esoftbg I dont understand why but it works. Can you explain me why ? :)
And one important thing if will do size of field 20 is that means after point I will see 20 digits ?
0
 
ferhadAuthor Commented:
esoftbg it works in SQLExplorer but in my Delphi code when I am writing the following code its not working:

QuerySort.Close;
QuerySort.SQL.Clear;
QuerySort.SQL.Add('Update test Set Field3=:PPercent * Field1 Where Calcul=True');
QuerySort.ParamByName('PPercent').AsFloat := Table1.FieldByName('FieldName').AsFloat;
QuerySort.ExecSQL;
0
 
esoftbgCommented:
There is not a field Calcul in your table-structure ????
It raises an exception when I try to execute your QuerySort....
0
 
esoftbgCommented:
 QuerySort.Active := False;
  QuerySort.SQL.Text := 'UPDATE TEST SET Field3 = Field1 * Field2';
  QuerySort.ExecSQL;
  QuerySort.SQL.Text := 'SELECT Field1, Field2, Field3 FROM TEST';
  QuerySort.Active := True;

I tested the table with different sizes of the Field1, Field2 and Field3. It works fine always the sizes of the 3 fields are equal:

               Type    Size    Key    
1. ID           +                  *
2. Field1     #         4
3. Field2     #         4
4. Field3     #         4

               Type    Size    Key    
1. ID           +                  *
2. Field1     #         8
3. Field2     #         8
4. Field3     #         8

It does not work when the size of the Field3 is different, example:
               Type    Size    Key    
1. ID           +                  *
2. Field1     #         2
3. Field2     #         2
4. Field3     #         4
0
 
ferhadAuthor Commented:
As you see I have declared param which gets value from other table(Table1).

QuerySort.Close;
QuerySort.SQL.Clear;
QuerySort.SQL.Add('Update test Set Field3=:PPercent * Field1 Where Calcul=True');
QuerySort.ParamByName('PPercent').AsFloat := Table1.FieldByName('FieldName').AsFloat;
QuerySort.ExecSQL;

I gaved to the size of FieldName the same value with the first table(20).
0
 
esoftbgCommented:
SQL text
'update TEST set Field3=:PPercent * Field1 Where Calcul=True'
expects that Calcul is a logical field into table TEST

emil
0
 
esoftbgCommented:
May be you would like to do something like that:

  QuerySort.SQL.Text := 'UPDATE TEST SET Field3 = (SELECT FIELDNAME FROM PERCENT WHERE CALCUL=TRUE) * Field1';

I created a Paradox table PERCENT:

                   Type    Size    Key    
1. CALCUL       L
2. FIELDNAME  #        2

I just guess what you want to do.
0
 
esoftbgCommented:
I posted into the table PERCENT.db two records:
FALSE, 0.00
TRUE,  15.24
0
 
ferhadAuthor Commented:
I see you didn't understand me correctly. I have two tables. First table has the following structure.(test.db)

               Type    Size    Key    
1. ID           +                  *
2. Field1     #         20
3. Field2     #         20
4. Field3     #         20

And the second :(currency.db)

   Type              Size    Key    
1. ID                   +                  *
2. FieldName       #       20


QuerySort.Close;
QuerySort.SQL.Clear;
QuerySort.SQL.Add('Update test Set Field3=:PPercent * Field1 Where Calcul=True');
QuerySort.ParamByName('PPercent').AsFloat := Table1.FieldByName('FieldName').AsFloat;
QuerySort.ExecSQL;

Why its not working I dont know.
0
 
ferhadAuthor Commented:
Table1 is connected with currency.db
0
 
esoftbgCommented:
I don't see field Calcul in your tables ????
If your TEST.db table has a structure

               Type    Size    Key    
1. ID           +                  *
2. Field1     #         20
3. Field2     #         20
4. Field3     #         20
5. CALCUL   L

then:

QuerySort.Close;
QuerySort.SQL.Clear;
QuerySort.SQL.Add('Update test Set Field3=:PPercent * Field1 Where Calcul=True');
QuerySort.ParamByName('PPercent').AsFloat := Table1.FieldByName('FieldName').AsFloat;
QuerySort.ExecSQL;

will update anly these records into TEST.db which CALCUL field value is True

Do you understand ????
0
 
esoftbgCommented:
replace
> will update anly these records into TEST.db which CALCUL field value is True
with
will update only these records into TEST.db which CALCUL field value is True
0
 
ferhadAuthor Commented:
Certainly I understand what you mean cause I have written this code :)
Code that you gave above is not working and I think because of declared param. What should I do ?
=======================================
kretzschmar why are you so passive ?
0
 
esoftbgCommented:
If you understand what doing the condition 'Where Calcul=True' ???? You have not a field named Calcul at all ????

This code works, it is tested:

  QuerySort.Active := False;
  QuerySort.SQL.Text :=''
                     + 'UPDATE TEST SET Field3=(SELECT FIELDNAME FROM CURRENCY WHERE ID='
                     + ''''
                     + Table1.FieldByName('ID').AsString
                     + ''''
                     + ') * Field1'
                     + '';
  QuerySort.ExecSQL;
  QuerySort.SQL.Text := 'SELECT Field1, Field2, Field3 FROM TEST';
  QuerySort.Active := True;
0
 
ferhadAuthor Commented:
Certainly I have field Calcul :) I have tested the following code:

QuerySort.Active := False;
QuerySort.SQL.Text :=''
                       + 'UPDATE debts SET CalculatedPercentAZM=(SELECT Kurs FROM CURRENCY C WHERE C."Date"='
                       + ''''
                       + DateTimeToStr(Date)
                       + ''''
                       + ') * CalculatedPercent'
                       + '';
QuerySort.ExecSQL;
QuerySort.Active := True;

and it gave me "Single row subquery produced more than one row" error. I understood that with the same Date field table always has 4 records. After it I changed code above into the following code making search more accurate.

QuerySort.Active := False;
    QuerySort.SQL.Text :=''
                       + 'UPDATE debts SET CalculatedPercentAZM=(SELECT Kurs FROM CURRENCY C WHERE C."Date"='
                       + ''''
                       + DateTimeToStr(Date)
                       + ''''+'and (C."Code"=1)'
                       + ') * CalculatedPercent'
                       + '';
    QuerySort.ExecSQL;
    QuerySort.Active := True;

and it gave me "Error creating cursor handle" error.
0
 
esoftbgCommented:
> Certainly I have field Calcul :) I have tested the following code:
IT IS NOT Certainly, because you wrote:

Comment from ferhad
Date: 04/09/2004
>I see you didn't understand me correctly. I have two tables. First table has the following structure.(test.db)

>               Type    Size    Key    
>1. ID           +                  *
>2. Field1     #         20
>3. Field2     #         20
>4. Field3     #         20

>And the second :(currency.db)

>   Type              Size    Key    
>1. ID                   +                  *
>2. FieldName       #       20

DO YOU SEE IN YOUR COMMENT FIELDS NAMED "CALCUL" AND "DATE"
HOW CAN I ANSWER CORRECTLY TO YOUR QUESTION, WHEN YOU CHANGE THE CONDITION AND THE FIELDS ON EVERY YOUR COMMENT ????

ARE YOU MAKE ME A FOOL ????
0
 
ferhadAuthor Commented:
First of all excuse me esoftbg
Actually sometimes when I give code I forget delete fields which were not exist in previous. Because of it don't pay attention to the mount of fields. I just don't want to give entire structure of my database cause its very big and has a lot of fields. Each time I give only these fields which presense in code.
The main purpose is to change all records in some field multiplying two fields which are in different tables. very simple.  I will not change structur that u gave me above. I wanna to make something like that Field3=Field1*FieldName(I am using structure that u wrote above).
0
 
ferhadAuthor Commented:
If you have code that works yous can send me this code to my mail address too. (ferhadismayil@yahoo.com)
0
 
esoftbgCommented:
Ok, I will develop a working code and will send it to you....
0
 
ferhadAuthor Commented:
Thank you very very much esoftbg for your mail. I will test it and say you results. But I have already looked how its working and want to say you one thing sure result will be acception of your answer. :)
0
 
esoftbgCommented:
I hope the example is useful :)
0
 
esoftbgCommented:
please replace the procedure CalendarChange(Sender: TObject); with this one:

procedure TForm1.CalendarChange(Sender: TObject);
var
  S:      string;
begin
  S := FormatDateTime(ShortDateFormat, EncodeDate(Calendar.Year, Calendar.Month, Calendar.Day));
  if not QueryCurrency.Active then
    QueryCurrency.Open;
  QueryDateCourse.Active := False;
  QueryDateCourse.SQL.Text :=''
                           + ' SELECT cu.ID as CURRENCY_ID, cu.CODE, co.COURSE, co.COURSE_DATE FROM COURSES co'
                           + ' LEFT OUTER JOIN CURRENCY cu ON co.CURRENCY_ID=cu.ID'
                           + ' WHERE co.COURSE_DATE='
                           + '''' + S + ''''
                           + ' AND co.CURRENCY_ID='
                           + '''' + QueryCurrency.FieldByName('ID').AsString + ''''
                           + '';
  QueryDateCourse.Active := True;
end;
0
 
esoftbgCommented:
I just sent an improved version by e-mail.
0
 
ferhadAuthor Commented:
I couldn't download the latest really working version?
It gave me an error. Please, send it to my email.
0
 
ferhadAuthor Commented:
Thank you very much esoftbg for you help. Using your previous code I have found and solved problem.
0
 
esoftbgCommented:
This is not a new answer. This is simpli the code which was accepted from the author of the question:

unit Unit_Q_20946220;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, Grids, DBGrids, Buttons, Calendar, StdCtrls,
  DateUtils;

type
  TForm1 = class(TForm)
    QueryDebts: TQuery;
    DataSourceDebts: TDataSource;
    DBGrid1: TDBGrid;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    DBGrid2: TDBGrid;
    DataSourceCourses: TDataSource;
    SpeedButton3: TSpeedButton;
    Calendar: TCalendar;
    DBGrid3: TDBGrid;
    QueryDateCourse: TQuery;
    DataSourceDateCourse: TDataSource;
    DBGrid4: TDBGrid;
    QueryCurrency: TQuery;
    DataSourceCurrency: TDataSource;
    QueryCourses: TQuery;
    procedure FormCreate(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton3Click(Sender: TObject);
    procedure CalendarChange(Sender: TObject);
    procedure QueryCurrencyAfterScroll(DataSet: TDataSet);
    procedure QueryCoursesNewRecord(DataSet: TDataSet);
    procedure QueryDebtsNewRecord(DataSet: TDataSet);
  private { Private declarations }
  public  { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
  Calendar.Day := 12;
  Calendar.Month := 4;
  Calendar.Year := 2004;
  SpeedButton1Click(Self);
end;

procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
  QueryCurrency.Active := False;
  QueryCurrency.Active := True;
  {
  QueryCourses.Active := False;
  QueryCourses.Active := True;
  }
  QueryDebts.Active := False;
  QueryDebts.SQL.Text := ' SELECT CURRENCY_ID, CalculatedPercent, CalculatedPercentAZM FROM DEBTS';
  QueryDebts.Active := True;
end;

procedure TForm1.SpeedButton2Click(Sender: TObject);
begin
  QueryDebts.Active := False;
  QueryDebts.SQL.Text := ' UPDATE DEBTS SET CalculatedPercentAZM=0';
  QueryDebts.ExecSQL;
  QueryDebts.SQL.Text := ' SELECT CURRENCY_ID, CalculatedPercent, CalculatedPercentAZM FROM DEBTS';
  QueryDebts.Active := True;
end;

procedure TForm1.SpeedButton3Click(Sender: TObject);
var
  S:      string;
begin
  if (QueryDateCourse.RecordCount=1) then
  begin
    QueryDebts.Active := False;
    QueryDebts.SQL.Text :=''
                        + ' UPDATE DEBTS SET CalculatedPercentAZM=(SELECT COURSE FROM COURSES WHERE COURSE_DATE='
                        + ''''
                        + QueryDateCourse.FieldByName('COURSE_DATE').AsString
                        + ''''
                        + ' AND CURRENCY_ID='
                        + ''''
                        + QueryDateCourse.FieldByName('CURRENCY_ID').AsString
                        + ''''
                        + ') * CalculatedPercent'
                        + ' WHERE CURRENCY_ID='
                        + ''''
                        + QueryDateCourse.FieldByName('CURRENCY_ID').AsString
                        + ''''
                        + '';
    QueryDebts.ExecSQL;
    QueryDebts.SQL.Text := ' SELECT CURRENCY_ID, CalculatedPercent, CalculatedPercentAZM FROM DEBTS';
    QueryDebts.Active := True;
  end
  else
  begin
    S := FormatDateTime(ShortDateFormat, EncodeDate(Calendar.Year, Calendar.Month, Calendar.Day));
    ShowMessage('You have not course for ' + S);
  end;
end;

procedure TForm1.CalendarChange(Sender: TObject);
var
  S:      string;
begin
  S := FormatDateTime(ShortDateFormat, EncodeDate(Calendar.Year, Calendar.Month, Calendar.Day));
  if not QueryCurrency.Active then
    QueryCurrency.Open;
  QueryDateCourse.Active := False;
  QueryDateCourse.SQL.Text :=''
                           + ' SELECT cu.ID as CURRENCY_ID, cu.CODE, co.COURSE, co.COURSE_DATE FROM COURSES co'
                           + ' LEFT OUTER JOIN CURRENCY cu ON co.CURRENCY_ID=cu.ID'
                           + ' WHERE co.COURSE_DATE='
                           + '''' + S + ''''
                           + ' AND co.CURRENCY_ID='
                           + '''' + QueryCurrency.FieldByName('ID').AsString + ''''
                           + '';
  QueryDateCourse.Active := True;
end;

procedure TForm1.QueryCurrencyAfterScroll(DataSet: TDataSet);
begin
  CalendarChange(Self);
  QueryCourses.Active := False;
  QueryCourses.SQL.Text :=''
                        + ' SELECT * FROM COURSES WHERE CURRENCY_ID='
                        + ''''
                        + QueryCurrency.FieldByName('ID').AsString
                        + ''''
                        + '';
  QueryCourses.Active := True;
end;

procedure TForm1.QueryCoursesNewRecord(DataSet: TDataSet);
begin
  QueryCourses.FieldByName('CURRENCY_ID').AsInteger := QueryCurrency.FieldByName('ID').AsInteger;
  QueryCourses.FieldByName('COURSE_DATE').AsDateTime := Today;
end;

procedure TForm1.QueryDebtsNewRecord(DataSet: TDataSet);
begin
  QueryDebts.FieldByName('CURRENCY_ID').AsInteger := QueryCurrency.FieldByName('ID').AsInteger;
end;

end.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.