Home » SQL & PL/SQL » SQL & PL/SQL » Inserting records in Table using cursor (Oracle 11g)
| Inserting records in Table using cursor [message #637071] |
Sun, 10 May 2015 00:46  |
 |
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
Hi Guys,
I need to insert records from one table_1 to master_table by referencing in another ref_table which falls under condition. for e.g..
Table_1
col_1 col_2 col_3 col_4
12 123 200 11-Jun-14
12 123 3000 12-Jun-14
12 123 100 13-Jun-14
Ref_table
Start date End date Min Max Percentage Type
10-Jul-2001 15-Jun-2014 0 250 14.90% T1
10-Jul-2001 15-Jun-2014 250 2500 17% T2
10-Jul-2001 15-Jun-2014 2500 3500 29.90% T3
I need to insert into Master_table like this for e.g. for second record in table_1 where col_3 is 3000 that record col_4 falling between start date and end date of Ref_table and split the value like below
Master_table
Col_1 col_2 col_4 T1 T2 T3 T1 % T2% T3 %
12 123 12-Jun-14 250 2250 500 14.90% 17% 29.90%
create table master_table(col_1 number,col_2 number,col_4 date,T1 number,t2 number,t3 number,t1_per number,t2_per number,t3_per number);
create table Ref_table(start_date date,end_date date,min number,max number,percentage number,type varchar2(5));
create table table_1(col_1 number,col_2 number,col_3 number,col_4 date);
insert into ref_table
select '10-Jul-2001','15-Jun-2014',0,250,14.9,T1 from dual;
insert into ref_table
select '10-Jul-2001','15-Jun-2014',250,2500,17,T2 from dual;
insert into ref_table
select '10-Jul-2001','15-Jun-2014',2500,3500,29.9,T3 from dual;
insert into Table_1
select 12,123,200,'11-Jun-2014' from dual;
insert into Table_1
select 12,123,3000,'12-Jun-2014' from dual;
insert into Table_1
select 12,123,100,'13-Jun-2014' from dual;
[Edit MC: add code tags and align columns]
[Updated on: Sun, 10 May 2015 00:59] by Moderator Report message to a moderator |
|
|
|
| Re: Inserting records in Table using cursor [message #637073 is a reply to message #637071] |
Sun, 10 May 2015 01:03  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Use code tags for your result too.
Post a working test case.
For date column use TO_DATE with format as we have not the same default format than you:
SQL> select to_date('10-Jul-2001') from dual;
select to_date('10-Jul-2001') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
SQL> insert into ref_table
2 select '10-Jul-2001','15-Jun-2014',0,250,14.9,T1 from dual;
select '10-Jul-2001','15-Jun-2014',0,250,14.9,T1 from dual
*
ERROR at line 2:
ORA-00904: "T1": invalid identifier
There is no col3 colun in your result but there is one in your master_table; what should be its value.
Explain the value in each column of your result.
|
|
|
|
| Re: Inserting records in Table using cursor [message #637074 is a reply to message #637073] |
Sun, 10 May 2015 01:11 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
What i exactly require is consider the record in table_1
col_1 col_2 col_3 col_4
12 123 3000 12-Jun-14
I need to insert the above record into master_table by transforming col_3 value by looking in ref_table whether col_4 is between start date and end date and min and max and split the value accordingly in master_table
here 3000 - 250+2250 remaning 500(whatever) not required to fall between 2500-3500 in T3, but 0-250 and 250-2500 needs to be considered for splitting the values
Ref_table
Start date End date Min Max Percentage Type
10-Jul-2001 15-Jun-2014 0 250 14.90% T1
10-Jul-2001 15-Jun-2014 250 2500 17% T2
10-Jul-2001 15-Jun-2014 2500 3500 29.90% T3
Col_1 col_2 col_4 T1 T2 T3 T1 % T2% T3 %
12 123 12-Jun-14 250 2250 500 14.90% 17% 29.90%
[Updated on: Sun, 10 May 2015 01:12] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Inserting records in Table using cursor [message #637081 is a reply to message #637079] |
Sun, 10 May 2015 04:01 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
HI michel,
Please find my queries
create table master_table(col_1 number,col_2 number,col_4 date,T1 number,t2 number,t3 number,t1_per number,t2_per number,t3_per number);
create table Ref_table(start_date date,end_date date,min number,max number,percentage number,type varchar2(5));
create table table_1(col_1 number,col_2 number,col_3 number,col_4 date);
insert into ref_table
select to_date('10-Jul-2001'),to_date('15-Jun-2014'),0,250,14.9,'T1' from dual;
insert into ref_table
select to_date('10-Jul-2001'),to_date('15-Jun-2014'),250,2500,17,'T2' from dual;
insert into ref_table
select to_date('10-Jul-2001'),to_date('15-Jun-2014'),2500,3500,29.9,'T3' from dual;
insert into Table_1
select 12,123,200,to_date('11-Jun-2014') from dual;
insert into Table_1
select 12,123,3000,to_Date('12-Jun-2014') from dual;
insert into Table_1
select 12,123,100,to_date('13-Jun-2014') from dual;
SQL> select * from table_1
2 ;
COL_1 COL_2 COL_3 COL_4
---------- ---------- ---------- ---------
12 123 200 11-JUN-14
12 123 3000 12-JUN-14
12 123 100 13-JUN-14
SQL> select * from ref_table;
START_DAT END_DATE MIN MAX PERCENTAGE TYPE
--------- --------- ---------- ---------- ---------- -----
10-JUL-01 15-JUN-14 0 250 14.9 T1
10-JUL-01 15-JUN-14 250 2500 17 T2
10-JUL-01 15-JUN-14 2500 3500 29.9 T3
SQL>
i need the output to be inserted into master table from table_1 by looking up into ref_table based on below derivation logic
for eg 3000 needs to be split into
250(0-250) - T1 (check between Min and Max of ref_table)
2250(250-2500) - T2(check between Min and Max of ref table)
500(2500-3500)(remining not required to check between for this case - T3)
insert into master_table(col_1,col_2,col_4,t1,t2,t3,t1_per,t2_per,t3_per)
select col_1,
col2,
col_4,
250, -- derivation logic
2250, -- derivation logic
500,-- derivation logic
14.9,-- derivation logic
17,-- derivation logic
29.9-- derivation logic
from
table_1;
master table
Col_1 col_2 col_4 T1 T2 T3 T1 % T2% T3 %
12 123 12-Jun-14 250 2250 500 14.90% 17% 29.90%
|
|
|
|
| Re: Inserting records in Table using cursor [message #637085 is a reply to message #637081] |
Sun, 10 May 2015 04:58 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
You didn't read what I said.
SQL> insert into ref_table
2 select to_date('10-Jul-2001'),to_date('15-Jun-2014'),0,250,14.9,'T1' from dual;
select to_date('10-Jul-2001'),to_date('15-Jun-2014'),0,250,14.9,'T1' from dual
*
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected
|
|
|
|
| Re: Inserting records in Table using cursor [message #637087 is a reply to message #637071] |
Sun, 10 May 2015 05:55 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
Hi Michel,
I can able to insert thats what i have shown in output
create table master_table(col_1 number,col_2 number,col_4 date,T1 number,t2 number,t3 number,t1_per number,t2_per number,t3_per number);
create table Ref_table(start_date date,end_date date,min number,max number,percentage number,type varchar2(5));
create table table_1(col_1 number,col_2 number,col_3 number,col_4 date);
insert into ref_table
select to_date('10-Jul-2001'),to_date('15-Jun-2014'),0,250,14.9,'T1' from dual;
[Updated on: Sun, 10 May 2015 05:56] Report message to a moderator |
|
|
|
|
|
| Re: Inserting records in Table using cursor [message #637091 is a reply to message #637088] |
Sun, 10 May 2015 06:15 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
Hope now you can use the below cases
insert into ref_table
select to_date('10-Jul-2001','DD-MON-YYYY'),to_date('15-Jun-2014','DD-MON-YYYY'),0,250,14.9,'T1' from dual;
insert into ref_table
select to_date('10-Jul-2001','DD-MON-YYYY'),to_date('15-Jun-2014','DD-MON-YYYY'),250,2500,17,'T2' from dual;
insert into ref_table
select to_date('10-Jul-2001','DD-MON-YYYY'),to_date('15-Jun-2014','DD-MON-YYYY'),2500,3500,29.9,'T3' from dual;
insert into Table_1
select 12,123,200,to_date('11-Jun-2014','DD-MON-YYYY') from dual;
insert into Table_1
select 12,123,3000,to_Date('12-Jun-2014','DD-MON-YYYY') from dual;
insert into Table_1
select 12,123,100,to_date('13-Jun-2014','DD-MON-YYYY') from dual
There is mistake from my master table output it should be like this
3000 = 250 (max for T1) + 2500 (max for T2) + 250 (remaining for T3)
master table
Col_1 col_2 col_4 T1 T2 T3 T1 % T2% T3 %
12 123 12-Jun-14 250 2500 250 14.90% 17% 29.90%
[Updated on: Sun, 10 May 2015 06:43] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Inserting records in Table using cursor [message #637103 is a reply to message #637102] |
Sun, 10 May 2015 13:27 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
i Have tried something like this in sql but getting error
SELECT col_1,
col_2,
col_4,
t1,
t2,
t3,
t1per,
t2per,
t3per
FROM (SELECT col_1,
col_2,
col_4,
( CASE
WHEN col_3 > t1val THEN t1val
ELSE col_3
END ) T1,
( CASE
WHEN col_3 - t1val > 0 THEN col_3 - t1val
ELSE 0
END ) RESULT1,
( CASE
WHEN result1 > t2val THEN t2val
ELSE result1
END ) T2,
( CASE
WHEN result1 - t2val > 0 THEN result1 - t2val
ELSE 0
END ) T3,
t1per,
t2per,
t3per
FROM (SELECT col_1,
col_2,
col_4,
col_3,
Max(CASE
WHEN ref.TYPE = 'T1' THEN REF.max
END) T1VAL,
Max(CASE
WHEN ref.TYPE = 'T2' THEN REF.max
END) T2VAL,
Max(CASE
WHEN ref.TYPE = 'T3' THEN REF.max
END) T3VAL,
Max(CASE
WHEN ref.TYPE = 'T1' THEN REF.percentage
END) T1PER,
Max(CASE
WHEN ref.TYPE = 'T2' THEN REF.percentage
END) T2PER,
Max(CASE
WHEN ref.TYPE = 'T3' THEN REF.percentage
END) T3PER
FROM table_1 TB
left outer join ref_table ref
ON TB.col_4 BETWEEN REF.start_date AND
REF.end_date
GROUP BY col_1,
col_2,
col_4) A)B
stating that result1 is invalid identifier because result1 alias is not treating as alias
*BlackSwan formatted the SQL. Please do so yourself in the future.
http://www.orafaq.com/forum/t/174502/
[Updated on: Sun, 10 May 2015 13:42] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Inserting records in Table using cursor [message #637107 is a reply to message #637103] |
Sun, 10 May 2015 16:05 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl> SELECT * FROM table_1
2 /
COL_1 COL_2 COL_3 COL_4
---------- ---------- ---------- -----------
12 123 200 11-Jun-2014
12 123 3000 12-Jun-2014
12 123 100 13-Jun-2014
3 rows selected.
SCOTT@orcl> SELECT * FROM ref_table
2 /
START_DATE END_DATE MIN MAX PERCENTAGE TYPE
----------- ----------- ---------- ---------- ---------- -----
10-Jul-2001 15-Jun-2014 0 250 14.9 T1
10-Jul-2001 15-Jun-2014 250 2500 17 T2
10-Jul-2001 15-Jun-2014 2500 3500 29.9 T3
3 rows selected.
SCOTT@orcl> SELECT col_1, col_2, col_4,
2 LEAST (t1val, col_3) t1,
3 LEAST (t2val, GREATEST (col_3 - t1val, 0)) t2,
4 LEAST (t3val, GREATEST (col_3 - t1val - t2val, 0)) t3,
5 t1per, t2per, t3per
6 FROM (SELECT tb.col_1, tb.col_2, tb.col_4, tb.col_3,
7 MAX (DECODE (ref.type, 'T1', ref.max)) t1val,
8 MAX (DECODE (ref.type, 'T2', ref.max)) t2val,
9 MAX (DECODE (ref.type, 'T3', ref.max)) t3val,
10 MAX (DECODE (ref.type, 'T1', ref.percentage)) t1per,
11 MAX (DECODE (ref.type, 'T2', ref.percentage)) t2per,
12 MAX (DECODE (ref.type, 'T3', ref.percentage)) t3per
13 FROM table_1 tb
14 LEFT OUTER JOIN ref_table ref
15 ON tb.col_4 BETWEEN ref.start_date AND ref.end_date
16 GROUP BY tb.col_1, tb.col_2, tb.col_4, tb.col_3)
17 ORDER BY col_1, col_2, col_4
18 /
COL_1 COL_2 COL_4 T1 T2 T3 T1PER T2PER T3PER
---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
12 123 11-Jun-2014 200 0 0 14.9 17 29.9
12 123 12-Jun-2014 250 2500 250 14.9 17 29.9
12 123 13-Jun-2014 100 0 0 14.9 17 29.9
3 rows selected.
|
|
|
|
|
|
|
|
| Re: Inserting records in Table using cursor [message #637143 is a reply to message #637136] |
Mon, 11 May 2015 13:09 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
Actually this is just the enhancement of my previous requirement where i need to insert from master_table to monthly_table by referring another ref_table1
create ref_table1(col_1 number,col_2 number,start_date date, end_date date;
insert into ref_table1
select 12,123,to_date(12-Jun-2014,'DD-MON-YYYY'),to_date(13-Jun-2014,'DD-MON-YYYY') from dual;
insert into ref_table1
select 12,123,to_date(13-Jun-2014,'DD-MON-YYYY'),to_date(14-Jun-2014,'DD-MON-YYYY') from dual;
col_1,col_2,start_date, end_date
12,123,12-Jun-2014 , 13-Jun-2014
12,123,13-Jun-2014, 14-Jun-2014
[code
COL_1 COL_2 COL_4 T1 T2 T3 T1PER T2PER T3PER
---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
12 123 11-Jun-2014 200 0 0 14.9 17 29.9
12 123 12-Jun-2014 250 2500 250 14.9 17 29.9
12 123 13-Jun-2014 100 0 0 14.9 17 29.9
][/code]
i need to transform the data into monthly_table by referring ref_table1 where col_4 is between start_date and end_date of ref_table1 and sum the values and count the number of respective types where not equal to zero.
T1_days - 2 T1 values i.e 250 and 100 hence it will be 2 days
T1_days - one T1 values not to consider 0, hence it will be 1 day
Monthly_table
col_1,col_2,start_date,end_date,T1,T2,T3,T1_days,T2_days,T3_days
12,123,12-jun-2014,13-jun-2014,350,2500,250,2,1,1
I tried like below
SELECT a.col_1,
a.col_2,
b.start_date,
b.end_date,
Sum(a.t1),
Sum(a.t2),
Sum(a.t3),
Count(*)
FROM master_table a,
ref_table1 b
WHERE a.col_4_date BETWEEN b.start_date AND b.end_date
AND a.col_1 = b.col_1
AND a.col_2 = b.col_2
[Updated on: Mon, 11 May 2015 13:11] Report message to a moderator |
|
|
|
|
|
| Re: Inserting records in Table using cursor [message #637145 is a reply to message #637144] |
Mon, 11 May 2015 13:29 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
I have formatted the SQL, i believe thats what i have read from the guide lines
create ref_table1(col_1 number,col_2 number,start_date date, end_date date;
insert into ref_table1
select 12,123,to_date(12-Jun-2014,'DD-MON-YYYY'),to_date(13-Jun-2014,'DD-MON-YYYY') from dual;
insert into ref_table1
select 12,123,to_date(13-Jun-2014,'DD-MON-YYYY'),to_date(14-Jun-2014,'DD-MON-YYYY') from dual;
ref_table1
col_1,col_2,start_date, end_date
12,123,12-Jun-2014 , 13-Jun-2014
12,123,13-Jun-2014, 14-Jun-2014
Master_table
COL_1 COL_2 COL_4 T1 T2 T3 T1PER T2PER T3PER
---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
12 123 11-Jun-2014 200 0 0 14.9 17 29.9
12 123 12-Jun-2014 250 2500 250 14.9 17 29.9
12 123 13-Jun-2014 100 0 0 14.9 17 29.9
i need to transform the data into monthly_table by referring ref_table1 where col_4 is between start_date and end_date of ref_table1 and sum the values and count the number of respective types where not equal to zero.
T1_days - 2 T1 values i.e 250 and 100 hence it will be 2 days
T1_days - one T1 values not to consider 0, hence it will be 1 day
Monthly_table
col_1,col_2,start_date,end_date,T1,T2,T3,T1_days,T2_days,T3_days
12,123,12-jun-2014,13-jun-2014,350,2500,250,2,1,1
Query used
SELECT a.col_1,
a.col_2,
b.start_date,
b.end_date,
Sum(a.t1),
Sum(a.t2),
Sum(a.t3),
Count(*)
FROM master_table a,
ref_table1 b
WHERE a.col_4_date BETWEEN b.start_date AND b.end_date
AND a.col_1 = b.col_1
AND a.col_2 = b.col_2
[Updated on: Mon, 11 May 2015 13:38] Report message to a moderator |
|
|
|
|
|
| Re: Inserting records in Table using cursor [message #637147 is a reply to message #637146] |
Mon, 11 May 2015 13:41 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
I have formatted the SQL, i believe thats what i have read from the guide lines
create ref_table1(col_1 number,col_2 number,start_date date, end_date date;
insert into ref_table1
select 12,123,to_date(12-Jun-2014,'DD-MON-YYYY'),to_date(13-Jun-2014,'DD-MON-YYYY') from dual;
insert into ref_table1
select 12,123,to_date(13-Jun-2014,'DD-MON-YYYY'),to_date(14-Jun-2014,'DD-MON-YYYY') from dual;
ref_table1
col_1,col_2,start_date, end_date
12,123,12-Jun-2014 , 13-Jun-2014
12,123,13-Jun-2014, 14-Jun-2014
Master_table
COL_1 COL_2 COL_4 T1 T2 T3 T1PER T2PER T3PER
12 123 11-Jun-2014 200 0 0 14.9 17 29.9
12 123 12-Jun-2014 250 2500 250 14.9 17 29.9
12 123 13-Jun-2014 100 0 0 14.9 17 29.9
i need to transform the data into monthly_table by referring ref_table1 where col_4 is between start_date and end_date of ref_table1 and sum the values and count the number of respective types where not equal to zero.
T1_days - 2 T1 values i.e 250 and 100 hence it will be 2 days
T1_days - one T1 values not to consider 0, hence it will be 1 day
Monthly_table
col_1,col_2,start_date,end_date,T1,T2,T3,T1_days,T2_days,T3_days
12,123,12-jun-2014,13-jun-2014,350,2500,250,2,1,1
Query used
SELECT a.col_1,
a.col_2,
b.start_date,
b.end_date,
Sum(a.t1),
Sum(a.t2),
Sum(a.t3),
Count(*)
FROM master_table a,
ref_table1 b
WHERE a.col_4_date BETWEEN b.start_date AND b.end_date
AND a.col_1 = b.col_1
AND a.col_2 = b.col_2
|
|
|
|
|
|
| Re: Inserting records in Table using cursor [message #637153 is a reply to message #637147] |
Mon, 11 May 2015 19:50 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl> SELECT * FROM ref_table1 ORDER BY col_1, col_2, start_date
2 /
COL_1 COL_2 START_DATE END_DATE
---------- ---------- ----------- -----------
12 123 12-Jun-2014 13-Jun-2014
12 123 13-Jun-2014 14-Jun-2014
2 rows selected.
SCOTT@orcl> SELECT * FROM master_table ORDER BY col_1, col_2, col_4
2 /
COL_1 COL_2 COL_4 T1 T2 T3 T1_PER T2_PER T3_PER
---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
12 123 11-Jun-2014 200 0 0 14.9 17 29.9
12 123 12-Jun-2014 250 2500 250 14.9 17 29.9
12 123 13-Jun-2014 100 0 0 14.9 17 29.9
3 rows selected.
SCOTT@orcl> SELECT a.col_1,
2 a.col_2,
3 b.start_date,
4 b.end_date,
5 SUM (a.t1) t1,
6 SUM (a.t2) t2,
7 SUM (a.t3) t3,
8 SUM (DECODE (a.t1, 0, 0, 1)) t1_days,
9 SUM (DECODE (a.t2, 0, 0, 1)) t2_days,
10 SUM (DECODE (a.t3, 0, 0, 1)) t3_days
11 FROM master_table a,
12 ref_table1 b
13 WHERE a.col_4 BETWEEN b.start_date AND b.end_date
14 AND a.col_1 = b.col_1
15 AND a.col_2 = b.col_2
16 GROUP BY a.col_1, a.col_2, b.start_date, b.end_date
17 ORDER BY col_1, col_2, start_date
18 /
COL_1 COL_2 START_DATE END_DATE T1 T2 T3 T1_DAYS T2_DAYS T3_DAYS
---------- ---------- ----------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
12 123 12-Jun-2014 13-Jun-2014 350 2500 250 2 1 1
12 123 13-Jun-2014 14-Jun-2014 100 0 0 1 0 0
2 rows selected.
|
|
|
|
| Re: Inserting records in Table using cursor [message #637157 is a reply to message #637153] |
Mon, 11 May 2015 22:25 |
|
rohit_shinez
Messages: 139
Registered: January 2015
|
Senior Member |
|
|
Hi, is it possible to change the value dynamically or i need to give update after insert query
for eg
so it would be dynamically
T1_avg - T1(after sum)/2(T1_days)
if divide by zero then i need to display as 0 to avoid divide by zero error
this can be achieved in same query or i need to update later this table
[cod
alter table monthly_table add t1_avg number;
alter table monthly_table add t2_avg number;
alter table monthly_table add t3_avg number;
alter table monthly_table add T1_sum_per number;
alter table monthly_table add T2_sum_per number;
alter table monthly_table add t3_sum_per number;
[/code]
COL_1 COL_2 START_DATE END_DATE T1 T2 T3 T1_DAYS T2_DAYS T3_DAYS ,T1_avg,T2,AVG,T3_AVG,T1_SUM_PER,T2_SUM_PER,T3_SUM_PER
---------- ---------- ----------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
12 123 12-Jun-2014 13-Jun-2014 350 2500 250 2 1 1 350/2,2500/1,250/1 , (14.9+14.9),34,31.9
12 123 13-Jun-2014 14-Jun-2014 100 0 0 1 0 0,100/1,0,0,14.9,17,29.9
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Inserting records in Table using cursor [message #637371 is a reply to message #637369] |
Fri, 15 May 2015 13:31 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
CREATE TABLE EMPLOYEE
(
empl_id NUMBER,
empl_nm VARCHAR2(50),
monthly_sal NUMBER(10,2),
bonus NUMBER(10,2),
);
CREATE OR replace VIEW vw_employee
AS
SELECT empl_id,
empl_nm,
monthly_sal,
bonus,
( monthly_sal * 12 + bonus ) total_sal
FROM employee;
or just include VIRTUAL column
http://viralpatel.net/blogs/oracle-11g-new-feature-virtual-column/
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 20:43:20 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
