#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    12
    Rep 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. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8
    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    12
    Rep Power
    0
    Originally Posted by costin_mentor
    in your trigger you can do someth
    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    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
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    12
    Rep Power
    0
    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
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8

    Thumbs up


    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;
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    14
    Rep Power
    0
    Buna Costin!

    Esti Roman?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8
    Originally Posted by mrtblt
    Buna Costin!

    Esti Roman?
    Da

IMN logo majestic logo threadwatch logo seochat tools logo