-
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
-
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;
/
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|