DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: global temorary table with formula field

  1. #1
    Join Date
    Apr 2008
    Posts
    2

    global temorary table with formula field

    hello
    i m new to oracle
    i have to change some sql procedure into oracle

    here i m trying to create global temporary table
    with field which is formula of othr fields


    CREATE GLOBAL TEMPORARY TABLE sAct
    (
    seAct CHAR(35) NULL,
    Decopbal DECIMAL(18,2) NULL,
    DecDebit DECIMAL(18,2) NULL,
    DecCredit DECIMAL(18,2) NULL,
    Decclbal As isNull(Decopbal,0) + isNull(DecCredit,0) - isNull(Decdebit,0),
    sHact NVARCHAR(70) NULL,
    parentid Numeric(10,0) NULL,
    sActCode CHAR(10) NULL,
    sPcode CHAR(10) NULL,
    sBehave CHAR(1) NULL,
    Actid Numeric(10,0) NULL,
    ssort Numeric(2,0) NULL
    );


    pls help me in converting this Query
    thx in advance
    Surabhi

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You need to use NVL instead of isnull, change numeric to number, and you might as well use varchar2. You also need to put the computation into a trigger.

    Did you also want to specify on commit delete rows, or on commit preserve rows?

    Code:
    CREATE GLOBAL TEMPORARY TABLE sAct
         ( seAct     VARCHAR2(35) NULL,
           Decopbal  NUMBER  (18,2) NULL,
           DecDebit  NUMBER  (18,2) NULL,
           DecCredit NUMBER  (18,2) NULL,
           Decclbal  NUMBER  (18,2) NULL,
           sHact     VARCHAR (70)   NULL,
           parentid  NUMBER  (10,0) NULL,
           sActCode  VARCHAR2(10)   NULL,
           sPcode    VARCHAR2(10)   NULL,
           sBehave   VARCHAR2(1)    NULL,
           Actid     NUMBER  (10,0) NULL,
           ssort     NUMBER  (2,0)  NULL)
        ON COMMIT DELETE ROWS;
    
    CREATE OR REPLACE TRIGGER sAct_biu
       BEFORE INSERT OR UPDATE ON sAct
          FOR EACH ROW 
    DECLARE
    BEGIN
       :NEW.Decclbal := NVL(:NEW.Decopbal, 0) + 
                        NVL(:NEW.DecCredit,0) - 
                        NVL(:NEW.Decdebit, 0);
    END sAct_biu;
    /

  3. #3
    Join Date
    Apr 2008
    Posts
    2
    Thanks
    its helps me lot commit delete rows is fine in my case

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width