Home » SQL & PL/SQL » SQL & PL/SQL » global temporary table with formula field (Oracle 10g,XP pro)
icon5.gif  global temporary table with formula field [message #317212] Wed, 30 April 2008 01:01 Go to next message
surabhi_vyas
Messages: 2
Registered: April 2008
Location: Jaipur rajasthan India
Junior Member
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 :confused:


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
);


Confused pls help me in converting this Query
thx in advance
Surabhi

[Edit MC: Courier font for table description]

[Updated on: Wed, 30 April 2008 01:19] by Moderator

Report message to a moderator

Re: global temporary table with formula field [message #317220 is a reply to message #317212] Wed, 30 April 2008 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have to use color in code, as you can't use code tags, you can use Courier font.

Virtual columns only exist from Oracle 11g.

By the way, isNull function is NVL in Oracle.

Regards
Michel

[Updated on: Wed, 30 April 2008 01:20]

Report message to a moderator

Re: global temporary table with formula field [message #317230 is a reply to message #317220] Wed, 30 April 2008 02:43 Go to previous messageGo to next message
surabhi_vyas
Messages: 2
Registered: April 2008
Location: Jaipur rajasthan India
Junior Member
yes code is in Sql so null is used in plcae of nvl and
as i told i have to convert it in PL/SQL

the colored code is my actual problem i m not getting way to describe a column in table which content is formula of other fields

as far i converted it

CREATE GLOBAL TEMPORARY TABLE sAct
(
seAct CHAR(35) NULL,
Decopbal Numeric(10,0) NULL,
DecDebit Numeric(10,0) NULL,
DecCredit Numeric(10,0) NULL,
Decclbal Numeric(10,0) As NVL(Decopbal,0) + NVL(DecCredit,0) - NVL(Decdebit,0),
sHact NVARCHAR2(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
);
Re: global temporary table with formula field [message #317232 is a reply to message #317230] Wed, 30 April 2008 02:49 Go to previous message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the colored code is my actual problem i m not getting way to describe a column in table which content is formula of other fields

I already answered:
Quote:
Virtual columns only exist from Oracle 11g.

Before, you can use a trigger that fills the column with your formula.

Regards
Michel
Previous Topic: Restart DB after adding memory
Next Topic: PLS-00801 internal error
Goto Forum:
  


Current Time: Mon Nov 04 05:55:44 CST 2024