| sql statement [message #37385] |
Mon, 04 February 2002 19:10  |
tofeks
Messages: 10
Registered: December 2001
|
Junior Member |
|
|
hello
How to write sql statement to create a colume which should be like this
Col1
-----
1
1
2
2
3
3
.
.
which is auto increament
col2
----
A
B
A
B
A
B
.
.
which print A and B automatically..
regards
tofeks
|
|
|
|
|
|
| Re: sql statement [message #37415 is a reply to message #37405] |
Wed, 06 February 2002 15:50  |
tofeks
Messages: 10
Registered: December 2001
|
Junior Member |
|
|
thanks for the reply pratap..
let me clear what actually i want to do. i want to know how to write sql statement to create a table which should be like this
Col1 col2 col3
---- ---- ----
A 1 x
B 1 x
A 2 x
B 2 x
A 3 x
B 3 x
. . .
. . .
whenever data entered at col3, col1 print A and B automatically and col2 which is auto increament
regards
tofeks
thanks again
|
|
|
|
| Re: sql statement [message #37421 is a reply to message #37385] |
Thu, 07 February 2002 00:26 |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
hi,
you have to write trigger to do that. try writing the trigger yourself as you know the logic(refer to my previous query).if u still have problem then
feel free to discuss.
cheers
pratap
|
|
|
|
| Re: sql statement [message #37464 is a reply to message #37421] |
Sun, 10 February 2002 22:52 |
tofeks
Messages: 10
Registered: December 2001
|
Junior Member |
|
|
tHIS IS THE TRIGGER I HAVE CREATED and no any error but.
SQL> create trigger testmodo
2 before insert or update on testo
3 for each row
4 begin
5 :new.A := trunc(:new.NUMO/2+0.5);
6
7 if (mod(:new.NUMO,2) = 0) then
8 :new.B := 'B';
9 else
10 :new.B := 'A';
11 end if;
12 end;
13 /
Trigger created.
When i entered data i get ERROR
SQL> insert into testo (NUMO, c) values (NUMO.NextVal, 2);
insert into testo (NUMO, c) values (NUMO.NextVal, 2)
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SCOTT.TESTMODO", line 7
ORA-04088: error during execution of trigger 'SCOTT.TESTMODO'
WHY IT IS??
PLS HELP ME
Regards
Tofeks
|
|
|
|
| Re: sql statement [message #37469 is a reply to message #37385] |
Mon, 11 February 2002 00:49 |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
hi,
here is the way to do that
create table tofeks
(
col1 number(5),
col2 varchar2(1),
col3 varchar2(10)
);
create or replace package testpack as
numrows number;
end;
create or replace trigger tr_tofeks_1
before insert
on tofeks
begin
select count(*) into testpack.numrows
from tofeks;
end;
create or replace trigger tr_tofeks_2
before insert on tofeks
for each row
declare
n number;
begin
n :=testpack.numrows+1;
:new.col1:=trunc(n/2+0.5);
if mod(n,2)=1 then
:new.col2:='A';
else
:new.col2:='B';
end if;
testpack.numrows:=n;
end;
/
insert into tofeks(col3) values ('pratap');
insert into tofeks(col3) values ('kumar');
insert into tofeks(col3) values ('tripathy');
it has been tested...working fine
cheers
pratap
|
|
|
|
| Re: sql statement [message #37546 is a reply to message #37469] |
Wed, 13 February 2002 16:26 |
tofeks
Messages: 10
Registered: December 2001
|
Junior Member |
|
|
hi prathap ..
sorry just came back from 2 days chinese new year break. thats why i could'nt reply ur msg .. i try to run you code but it still give error :
SQL> create table tofeks
2 (
3 col1 number(5),
4 col2 varchar2(1),
5 col3 varchar2(10)
6 );
Table created.
SQL>
SQL> create or replace package testpack as
2 numrows number;
3 end;
4
5 create or replace trigger tr_tofeks_1
6 before insert
7 on tofeks
8 begin
9 select count(*) into testpack.numrows
10 from tofeks;
11 end;
12
13 create or replace trigger tr_tofeks_2
14 before insert on tofeks
15 for each row
16 declare
17 n number;
18 begin
19 n :=testpack.numrows+1;
20 :new.col1:=trunc(n/2+0.5);
21 if mod(n,2)=1 then
22 :new.col2:='A';
23 else
24 :new.col2:='B';
25 end if;
26 testpack.numrows:=n;
27 end;
28 /
Warning: Package created with compilation errors.
SQL> show errors;
Errors for PACKAGE TESTPACK:
LINE/COL ERROR
-------- -----------------------------------------
5/1 PLS-00103: Encountered the Symbol "CREATE"
SQL>
regards
tofeks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
