Loading ...
Sorry, an error occurred while loading the content.

computed by columns

Expand Messages
  • James
    hi guys, I have try to use the computed by when creating a table and here is what I discovered. For example if I had a table like this ... CREATE TABLE INVOICE
    Message 1 of 4 , Jun 1, 2004
    • 0 Attachment
      hi guys,

      I have try to use the computed by when creating a table and here is what
      I discovered. For example if I had a table like this ...

      CREATE TABLE INVOICE (
      ID D_ID NOT NULL,
      INVOICE_NO D_ID,
      CUSTOMER_ID D_ID,
      INVOICE_DATE D_TRANSACTION_DATE,
      TOTAL_AMOUNT D_MONEY,
      DISCOUNT_1 D_DISCOUNT_PERCENT,
      DISCOUNT_2 D_DISCOUNT_PERCENT,
      DISCOUNT_3 D_DISCOUNT_PERCENT,
      LESS D_MONEY,
      ADJUSTMENT D_MONEY,
      GRAND_TOTAL COMPUTED BY (total_amount * (1-(discount_1/100)) *
      (1-(discount_2/100)) * (1-(discount_3/100)) - less - adjustment),
      SALES_AGENT D_ID,
      LOCATION VARCHAR(15) NOT NULL,
      REMARKS VARCHAR(240),
      STATUS CHAR(3) DEFAULT 'ACT' NOT NULL,
      CREATE_BY D_CREATE_BY,
      CREATE_ON D_CREATE_ON,
      EDIT_BY D_EDIT_BY,
      EDIT_ON D_EDIT_ON
      );


      As you can see the grand_total field has computed by properties. At
      first its data type was set at numeric(15,2), but after compiling it
      became numeric(18,8) and I think it was cause by the computed by
      properties. My question is this normal? or Is this what computed by for
      or does it has other uses?

      Please guide. Thanks


      regards,
      james
    • Ivan Prenosil
      What are definitions of your domains ? I guess D_MONEY and D_DISCOUNT_PERCENT are Numeric(x,2) ? In release notes for IB6 you can find some rules about
      Message 2 of 4 , Jun 2, 2004
      • 0 Attachment
        What are definitions of your domains ? I guess
        D_MONEY and D_DISCOUNT_PERCENT are Numeric(x,2) ?

        In release notes for IB6 you can find some rules about arithmetic operations:
        "... If both operands are exact numeric, then multiplying or dividing the operands produces
        an exact numeric with a precision of 18 and a scale equal to the sum of the scales of the
        operands. ..."

        i.e. multiplying of four Numeric(x,2) values will produce Numeric(18,8) result.


        Also, what do you mean by "At first its data type was set at numeric(15,2),
        but after compiling it became numeric(18,8)" ? Did you use this syntax :
        CREATE TABLE INVOICE ( ...
        GRAND_TOTAL NUMERIC(15,2) COMPUTED BY ( ...
        ?


        Ivan


        ----- Original Message -----
        From: "James" <james_027@...>
        To: <firebird-support@yahoogroups.com>
        Sent: Tuesday, June 01, 2004 9:25 AM
        Subject: [firebird-support] computed by columns


        > hi guys,
        >
        > I have try to use the computed by when creating a table and here is what
        > I discovered. For example if I had a table like this ...
        >
        > CREATE TABLE INVOICE (
        > ID D_ID NOT NULL,
        > INVOICE_NO D_ID,
        > CUSTOMER_ID D_ID,
        > INVOICE_DATE D_TRANSACTION_DATE,
        > TOTAL_AMOUNT D_MONEY,
        > DISCOUNT_1 D_DISCOUNT_PERCENT,
        > DISCOUNT_2 D_DISCOUNT_PERCENT,
        > DISCOUNT_3 D_DISCOUNT_PERCENT,
        > LESS D_MONEY,
        > ADJUSTMENT D_MONEY,
        > GRAND_TOTAL COMPUTED BY (total_amount * (1-(discount_1/100)) *
        > (1-(discount_2/100)) * (1-(discount_3/100)) - less - adjustment),
        > SALES_AGENT D_ID,
        > LOCATION VARCHAR(15) NOT NULL,
        > REMARKS VARCHAR(240),
        > STATUS CHAR(3) DEFAULT 'ACT' NOT NULL,
        > CREATE_BY D_CREATE_BY,
        > CREATE_ON D_CREATE_ON,
        > EDIT_BY D_EDIT_BY,
        > EDIT_ON D_EDIT_ON
        > );
        >
        >
        > As you can see the grand_total field has computed by properties. At
        > first its data type was set at numeric(15,2), but after compiling it
        > became numeric(18,8) and I think it was cause by the computed by
        > properties. My question is this normal? or Is this what computed by for
        > or does it has other uses?
        >
        > Please guide. Thanks
        >
        >
        > regards,
        > james
      • James
        Hi Ivan ... Since the Firebird behaves that way, are there some work around or do we just leave it as is? What if I really want it to be numeric(15,20)? Yes I
        Message 3 of 4 , Jun 2, 2004
        • 0 Attachment
          Hi Ivan

          Ivan Prenosil wrote:

          > What are definitions of your domains ? I guess
          > D_MONEY and D_DISCOUNT_PERCENT are Numeric(x,2) ?
          >
          > In release notes for IB6 you can find some rules about arithmetic
          > operations:
          > "... If both operands are exact numeric, then multiplying or dividing
          > the operands produces
          > an exact numeric with a precision of 18 and a scale equal to the sum
          > of the scales of the
          > operands. ..."
          >
          > i.e. multiplying of four Numeric(x,2) values will produce
          > Numeric(18,8) result.
          >
          >
          > Also, what do you mean by "At first its data type was set at
          > numeric(15,2),
          > but after compiling it became numeric(18,8)" ? Did you use this syntax :
          > CREATE TABLE INVOICE ( ...
          > GRAND_TOTAL NUMERIC(15,2) COMPUTED BY ( ...
          > ?
          >
          >
          > Ivan
          >
          Since the Firebird behaves that way, are there some work around or do we
          just leave it as is? What if I really want it to be numeric(15,20)?
          Yes I use the syntax

          CREATE TABLE INVOICE ( ...
          GRAND_TOTAL NUMERIC(15,2) COMPUTED BY ( ...

          but after compiling it the grand_total numeric(15,2) became
          grand_total(18,8). You're right that both D_money and
          d_discount_percent are numeric(x,2)


          james
        • Ivan Prenosil
          I can t reproduce what you describe. Here is screenshot of test script: CREATE DATABASE c: test.fdb USER SYSDBA PASSWORD masterkey ; CREATE TABLE T ( A
          Message 4 of 4 , Jun 3, 2004
          • 0 Attachment
            I can't reproduce what you describe.
            Here is screenshot of test script:

            CREATE DATABASE 'c:\test.fdb' USER 'SYSDBA' PASSWORD 'masterkey';
            CREATE TABLE T (
            A NUMERIC(8,2),
            B NUMERIC(8,2),

            X COMPUTED BY (A*B),
            Y NUMERIC(8,2) COMPUTED BY (A*B)
            );
            INSERT INTO T(A,B) VALUES (12.34, 100.01);
            INSERT INTO T(A,B) VALUES (56.78, 100.01);
            SELECT * FROM T;

            A B X Y
            ============ ============ ===================== ============
            12.34 100.01 1234.1234 1234.12
            56.78 100.01 5678.5678 5678.57

            DROP DATABASE;

            ----- Original Message -----
            From: "James" <james_027@...>
            To: <firebird-support@yahoogroups.com>
            Sent: Thursday, June 03, 2004 3:10 AM
            Subject: Re: [firebird-support] computed by columns


            > Hi Ivan
            >
            > Ivan Prenosil wrote:
            >
            > > What are definitions of your domains ? I guess
            > > D_MONEY and D_DISCOUNT_PERCENT are Numeric(x,2) ?
            > >
            > > In release notes for IB6 you can find some rules about arithmetic
            > > operations:
            > > "... If both operands are exact numeric, then multiplying or dividing
            > > the operands produces
            > > an exact numeric with a precision of 18 and a scale equal to the sum
            > > of the scales of the
            > > operands. ..."
            > >
            > > i.e. multiplying of four Numeric(x,2) values will produce
            > > Numeric(18,8) result.
            > >
            > >
            > > Also, what do you mean by "At first its data type was set at
            > > numeric(15,2),
            > > but after compiling it became numeric(18,8)" ? Did you use this syntax :
            > > CREATE TABLE INVOICE ( ...
            > > GRAND_TOTAL NUMERIC(15,2) COMPUTED BY ( ...
            > > ?
            > >
            > >
            > > Ivan
            > >
            > Since the Firebird behaves that way, are there some work around or do we
            > just leave it as is? What if I really want it to be numeric(15,20)?
            > Yes I use the syntax
            >
            > CREATE TABLE INVOICE ( ...
            > GRAND_TOTAL NUMERIC(15,2) COMPUTED BY ( ...
            >
            > but after compiling it the grand_total numeric(15,2) became
            > grand_total(18,8). You're right that both D_money and
            > d_discount_percent are numeric(x,2)
            >
            >
            > james
          Your message has been successfully submitted and would be delivered to recipients shortly.