Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

New Free Tools on Dev Shed!

#1
December 15th, 2010, 03:26 AM
 AhmetNuri
Registered User

Join Date: Nov 2008
Posts: 12
Time spent in forums: 1 h 22 m 47 sec
Reputation Power: 0
Computation valuta on firebird from different tables

Hi,
I have a two tables on my firebird 2.5 db. first table is valuta table. it include euro, usd rate for daily. The second table is order.
now order_price is default currency is usd. and I want to calculate it euro to order_priceeuro field. I test it wtih trigers but i can't succes. Because triger can read only own table (order) it can't read valuta_euro.
The formula is
order.order_priceeuro = order.order_price * valuta_euro
how can i calculate it?
thanks

#2
December 15th, 2010, 11:11 AM
 costin_mentor
Contributing User

Join Date: Jan 2007
Posts: 36
Time spent in forums: 14 h 23 m 16 sec
Reputation Power: 7
Quote:
 Originally Posted by AhmetNuri Hi, I have a two tables on my firebird 2.5 db. first table is valuta table. it include euro, usd rate for daily. The second table is order. now order_price is default currency is usd. and I want to calculate it euro to order_priceeuro field. I test it wtih trigers but i can't succes. Because triger can read only own table (order) it can't read valuta_euro. The formula is order.order_priceeuro = order.order_price * valuta_euro how can i calculate it? thanks

in your trigger you can do something like this :
AS
declare variable valuta_euro numeric(15,4);
begin
/* Trigger text */
.....
select your_field from valuta_table where _conditions_ into :valuta_euro ;
new.order_priceeuro = new.order_price * valuta_euro;
.....
end

#3
December 16th, 2010, 08:07 AM
 AhmetNuri
Registered User

Join Date: Nov 2008
Posts: 12
Time spent in forums: 1 h 22 m 47 sec
Reputation Power: 0
Quote:
Originally Posted by costin_mentor
in your trigger you can do someth
Quote:
 ing like this : AS declare variable valuta_euro numeric(15,4);

begin
/* Trigger text */
.....
select your_field from valuta_table where _conditions_ into :valuta_euro ;
new.order_priceeuro = new.order_price * valuta_euro;
.....
end

thanks it is working. now i wantto use if or case in this triger.
Code:
```AS
declare variable valuta_euro numeric(15,4);
declare variable valuta_usd numeric(15,4);
begin
/* Trigger text */
select valuta_euro from valuta  into :valuta_euro ;
select valuta_usd from valuta  into :valuta_usd ;
if (order.order_pricetype = 'EURO') then
new.order_pricegold = new.order_price * valuta_euro;
if (order.order_pricetype = 'USD') then
new.order_pricegold = new.order_price * valuta_usd;

end```

but it is not working. how can i use if in this triger?
thansk

#4
December 16th, 2010, 01:45 PM
 clivew
Contributing User

Join Date: Jan 2006
Posts: 2,047
Time spent in forums: 1 Week 6 Days 3 h 27 m 46 sec
Reputation Power: 382
Try:
Code:
```AS
declare variable avaluta numeric(15,4);
begin
/* Trigger text */

if (new.order_pricetype = 'EURO') then begin
select valuta_euro from valuta  into :avaluta;
end
else if (new.order_pricetype = 'USD') then begin
select valuta_usd from valuta  into :avaluta;
else begin
/* Catch any unexpected situation here */
end;
new.order_pricegold = new.order_price * avaluta;

end```

IMO it is better to set order_pricegold in one place only and do the conditional calculations first. Others may disagree

Clive

#5
December 16th, 2010, 03:24 PM
 AhmetNuri
Registered User

Join Date: Nov 2008
Posts: 12
Time spent in forums: 1 h 22 m 47 sec
Reputation Power: 0
Quote:
 Originally Posted by clivew Try: Code: ```AS declare variable avaluta numeric(15,4); begin /* Trigger text */ if (new.order_pricetype = 'EURO') then begin select valuta_euro from valuta into :avaluta; end else if (new.order_pricetype = 'USD') then begin select valuta_usd from valuta into :avaluta; else begin /* Catch any unexpected situation here */ end; new.order_pricegold = new.order_price * avaluta; end``` IMO it is better to set order_pricegold in one place only and do the conditional calculations first. Others may disagree Clive

very very thanks it is working I am very happy now

#6
December 17th, 2010, 02:58 AM
 costin_mentor
Contributing User

Join Date: Jan 2007
Posts: 36
Time spent in forums: 14 h 23 m 16 sec
Reputation Power: 7

Another solutions are ;

1 .
select iif(new.order_pricetype = 'EURO',valuta_euro,
iif(new.order_pricetype = 'USD',valuta_usd,0))
from valuta into :avaluta;

2. select
case new.order_pricetype
when 'EURO' then valuta_euro
when 'USD' then valuta_usd
else 0
end
from valuta into :avaluta;

#7
January 3rd, 2011, 05:20 AM
 mrtblt
Registered User

Join Date: Jan 2011
Posts: 14
Time spent in forums: 3 h 4 m 26 sec
Reputation Power: 0
Buna Costin!

Esti Roman?

#8
January 4th, 2011, 01:38 AM
 costin_mentor
Contributing User

Join Date: Jan 2007
Posts: 36
Time spent in forums: 14 h 23 m 16 sec
Reputation Power: 7
Quote:
 Originally Posted by mrtblt Buna Costin! Esti Roman?

Da

 Viewing: Dev Shed Forums > Databases > Firebird SQL Development > Computation valuta on firebird from diffrent tables