Home » SQL & PL/SQL » SQL & PL/SQL » Sequence in SQL
Sequence in SQL [message #237859] Wed, 16 May 2007 00:33 Go to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

Hi guys,
I have 2 querries:
1/. create sequence Onum start with 1000;
2/. create table OrderDetail (Onum number(8,0),
Odate date,
Rdate date,
Eonum varchar2(3),
Ernum varchar2(3),
Inum number(3,0),
Spnum number(3,0),
Orderqty number(10,0),
Unitprice number(5,2),
Amtdue number(7,2),
Orderstatus varchar2(12),
primary key (Onum),
constraint FK_OrderDetail_Eonum foreign key (Eonum) references Employee (Emnum),
constraint FK_OrderDetail_Ernum foreign key (Ernum) references Employee (Emnum),
constraint FK_OrderDetail_Ispnum foreign key (Inum,Spnum) references Price (Inum,Spnum));

which one should I create first and if drop, which one should I drop first? I try to insert the record into the OrderDetail table, but it always get this error:

SQL> insert into OrderDetail (Onum, Odate, Eonum, Inum, Spnum, Orderqty, Unitprice, Amtdue, Ordersta
2 select (Onum.Currval), sysdate, '&vEmnum', Price.Inum, Price.Spnum, '&vOrderqty', Price.U
old 2: select (Onum.Currval), sysdate, '&vEmnum', Price.Inum, Price.Spnum, '&vOrderqty', Price.Un
new 2: select (Onum.Currval), sysdate, 'E1', Price.Inum, Price.Spnum, '30', Price.Unitprice, (Pri
insert into OrderDetail (Onum, Odate, Eonum, Inum, Spnum, Orderqty, Unitprice, Amtdue, Orderstatus)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C006386) violated


Attach is my spool file.

Thank you so much for your help.
Re: Sequence in SQL [message #237861 is a reply to message #237859] Wed, 16 May 2007 00:49 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try changing

select (Onum.Currval), ...


to

select (Onum.Nextval), ...


HTH.
Michael
Re: Sequence in SQL [message #237862 is a reply to message #237859] Wed, 16 May 2007 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ It does not matter
2/ currval gives you the current value, so you always get the same value and so you violate your primary key. Use nextval to get the next value.
SQL> create sequence s;

Sequence created.

SQL> select s.nextval from user_objects where rownum<5;
   NEXTVAL
----------
         1
         2
         3
         4

4 rows selected.

Regards
Michel
Re: Sequence in SQL [message #237870 is a reply to message #237861] Wed, 16 May 2007 01:02 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

Thanks alot Micheal.
It works now.
However, I only run the CreateOrder program only one time. It suppose to insert only 1 record. But it comes up with 5 records, how can I fix it?

Thank a lot for you help.
Re: Sequence in SQL [message #237872 is a reply to message #237870] Wed, 16 May 2007 01:09 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Where can we see the "CreateOrder" program? How are we supposed to know how many records it inserts if we don't see it?
Re: Sequence in SQL [message #237881 is a reply to message #237870] Wed, 16 May 2007 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your "insert select" has no restriction in selection so your insert the number of rows there are in Price tables.
How many rows do you have in this table?

Regards
Michel
Re: Sequence in SQL [message #237894 is a reply to message #237881] Wed, 16 May 2007 01:45 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

thank you so much Micheal. I just get off from school. I fixed that problem. However, another problem comes up is that:
-- After insert the record into OrderDetail table. I use the select statement to display the record in the table. All other records are fine exept the Onum. Instead of follow the order like 1000, 1001, 1002. It skips the number eg: 1001 is the first number, then 1003, then 1005 and so on.
Re: Sequence in SQL [message #237905 is a reply to message #237894] Wed, 16 May 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't post the "create sequence" statement but I bet there is an "increment by 2" inside.

Regards
Michel
Re: Sequence in SQL [message #237906 is a reply to message #237905] Wed, 16 May 2007 02:01 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

this is my Create Sequence statement:
create sequence Onum start with 1000;

and this the statement that I have in CreatOrder program:

select '***** Order number is ----->' ||rtrim(Onum.Nextval)||'' from dual;

insert into OrderDetail (Onum, Odate, Eonum, Inum, Spnum, Orderqty, Unitprice, Amtdue, Orderstatus)
select (Onum.Nextval), sysdate, '&vEmnum', '&vInum', '&vSpnum', '&vOrderqty', Price.Unitprice, (Price.Unitprice*'&vOrderqty'), 'Open' from dual, Price where Price.Inum='&vInum' and Price.Spnum='&vSpnum';
commit;

[Updated on: Wed, 16 May 2007 02:03]

Report message to a moderator

Re: Sequence in SQL [message #237908 is a reply to message #237906] Wed, 16 May 2007 02:06 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

If you create your sequence just like "create sequence seq_name start with 1000;" then DEFAULT INCREMENT VALUE is 1.. anyway you can post below query output.

ORCL@TAJ>select increment_by 
from user_sequences 
where sequence_name = 'ONUM';




[Updated on: Wed, 16 May 2007 02:07]

Report message to a moderator

Re: Sequence in SQL [message #237911 is a reply to message #237906] Wed, 16 May 2007 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the execution of your script now fixed.
We can't know what is going on with a script that is no more the one you execute.

Regards
Michel
Re: Sequence in SQL [message #237917 is a reply to message #237911] Wed, 16 May 2007 02:29 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

SQL> select '***** Order number is ----->' ||rtrim(Onum.Nextval)||'' from dual;

'*****ORDERNUMBERIS----->'||RTRIM(ONUM.NEXTVAL)||''
--------------------------------------------------------------------
***** Order number is ----->1002

1 row selected.

SQL>
SQL> insert into OrderDetail (Onum, Odate, Eonum, Inum, Spnum, Orderqty, Unitprice, Amtdue, Ordersta
2 select (Onum.Nextval), sysdate, '&vEmnum', '&vInum', '&vSpnum', '&vOrderqty', Price.Unitp
old 2: select (Onum.Nextval), sysdate, '&vEmnum', '&vInum', '&vSpnum', '&vOrderqty', Price.Unitpr
new 2: select (Onum.Nextval), sysdate, 'E2', '102', '088', '20', Price.Unitprice, (Price.Unitpric

1 row created.

SQL> commit;

Commit complete.

SQL> spool off
SQL> select * from OrderDetail;

Item Supplier
ONUM ODATE RDATE EON ERN Number Number ORDERQTY
---------- -------------- -------------- --- --- ------ -------- ----------
Unit
Price AMTDUE ORDERSTATUS
---------- ---------- ------------
1001 16-5¤ -07 E1 101 99 30
$2.00 60 Open

1003 16-5¤ -07 E2 102 88 20
$2.50 50 Open


2 rows selected.
Re: Sequence in SQL [message #237930 is a reply to message #237917] Wed, 16 May 2007 02:49 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi vickie,

The problem is coming because you are running this statement before you insert statement

Quote:
select '***** Order number is ----->' ||rtrim(Onum.Nextval)||'' from dual;


When you will run the insert then ofcourse the value in the onum will be the next value.

I have checked it at my end it is running fine.I am sending you the attachment.

Ashu
  • Attachment: onum.txt
    (Size: 0.60KB, Downloaded 125 times)
Re: Sequence in SQL [message #237936 is a reply to message #237917] Wed, 16 May 2007 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now that you have fixed your error in insert statement to make it insert only 1 row then you have to use "currval" in it to take the value generated by the previous select.

Regards
Michel
Re: Sequence in SQL [message #237940 is a reply to message #237936] Wed, 16 May 2007 03:09 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

My program works well now.

Thank you so much for all your help. Smile
Re: Sequence in SQL [message #237948 is a reply to message #237940] Wed, 16 May 2007 03:32 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

sorry guys for keep asking too much Sad
How can it does not display $2.00 and $40.00 as I format the column format.

SQL> column Unitprice format $9,999.99
SQL> column Amtdue format $9,999.99

SQL> accept vOrderqty prompt ' Enter the Quantity to be Ordered: ';
Enter the Quantity to be Ordered: 20
SQL> select ' Unit Price: ' ||rtrim(Unitprice) ||'' from Price where Price.Inum='&vInum';
old 1: select ' Unit Price: ' ||rtrim(Unitprice) ||'' from Price where Price.Inum='&vInum'
new 1: select ' Unit Price: ' ||rtrim(Unitprice) ||'' from Price where Price.Inum='101'

'UNITPRICE:'||RTRIM(UNITPRICE)||''
-----------------------------------------------------
Unit Price: 2

1 row selected.

SQL> select ' Amount Due: ' ||rtrim(Unitprice*'&vOrderqty')||'' from Price where Price.Inum='&vInum'
old 1: select ' Amount Due: ' ||rtrim(Unitprice*'&vOrderqty')||'' from Price where Price.Inum='&vI
new 1: select ' Amount Due: ' ||rtrim(Unitprice*'20')||'' from Price where Price.Inum='101'

'AMOUNTDUE:'||RTRIM(UNITPRICE*'20')||''
-----------------------------------------------------
Amount Due: 40
Re: Sequence in SQL [message #237954 is a reply to message #237948] Wed, 16 May 2007 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"column" is output column (not table or view column), you don't have any output column with these names so these statements are useless.
Put your format in a to_char function in the queries.

Regards
Michel
Re: Sequence in SQL [message #237973 is a reply to message #237954] Wed, 16 May 2007 04:05 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

Thanks a lot.

Sincerely,

Vickie
Re: Sequence in SQL [message #238231 is a reply to message #237973] Thu, 17 May 2007 02:50 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

It is so wierd.
Last night, I fixed the program and it run very well. Somehow today, I run the program again. It is strange.

If I run the CreateOrder the 1st time, the sequence for Order Number is 1000. After the 1st run, the insert statement okie, it displays 1 row created.
If I run the second time, the sequence for Order Number suppose to be 1001. However, it shows 1000 again. After the 2nd run, the insert statement is not okie, it displays 0 row created.

Is there something wrong with my code?

The code below is after 2nd run.
SQL> insert into OrderDetail
  2                  (Onum, Odate, Eonum, Inum, Spnum, Orderqty, Unitprice, Amtdue, Orderstatus)
  3          select (Onum.Nextval),
  4                  sysdate,
  5                  '&vEmnum',
  6                  '&vInum',
  7                  '&vSpnum',
  8                  '&vOrderqty',
  9                  Price.Unitprice,
 10                  (Price.Unitprice*'&vOrderqty'), 'Open'
 11          from dual, Price
 12          where Price.Inum='&vInum'
 13                  and Price.Spnum='&vSpnum';
old   5:                '&vEmnum',
new   5:                'E2',
old   6:                '&vInum',
new   6:                '102',
old   7:                '&vSpnum',
new   7:                '099',
old   8:                '&vOrderqty',
new   8:                '30',
old  10:                (Price.Unitprice*'&vOrderqty'), 'Open'
new  10:                (Price.Unitprice*'30'), 'Open'
old  12:        where Price.Inum='&vInum'
new  12:        where Price.Inum='102'
old  13:                and Price.Spnum='&vSpnum'
new  13:                and Price.Spnum='099'

0 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> select '***** Order number is ----->' ||rtrim(Onum)||'' from OrderDetail;

'*****ORDERNUMBERIS----->'||RTRIM(ONUM)||''
--------------------------------------------------------------------
***** Order number is ----->1000

1 row selected.
Re: Sequence in SQL [message #238238 is a reply to message #238231] Thu, 17 May 2007 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it returns "0 row created" then this is because your select query does not return anything.
In this case nextval is not executed and so the sequence value does not change.

Regards
Michel
Re: Sequence in SQL [message #238239 is a reply to message #238238] Thu, 17 May 2007 03:07 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

I changed the code into this:
SQL> select '***** Order number is ----->' ||rtrim(Onum.Currval)||'' from OrderDetail;


instead of:
SQL> select '***** Order number is ----->' ||rtrim(Onum)||'' from OrderDetail;


The result after the 2nd run is:

SQL> insert into OrderDetail
  2                  (Onum, Odate, Eonum, Inum, Spnum, Orderqty, Unitprice, Amtdue, Ord
  3          select (Onum.Nextval),
  4                  sysdate,
  5                  '&vEmnum',
  6                  '&vInum',
  7                  '&vSpnum',
  8                  '&vOrderqty',
  9                  Price.Unitprice,
 10                  (Price.Unitprice*'&vOrderqty'), 'Open'
 11          from dual, Price
 12          where Price.Inum='&vInum'
 13                  and Price.Spnum='&vSpnum';
old   5:                '&vEmnum',
new   5:                'E2',
old   6:                '&vInum',
new   6:                '101',
old   7:                '&vSpnum',
new   7:                '099',
old   8:                '&vOrderqty',
new   8:                '30',
old  10:                (Price.Unitprice*'&vOrderqty'), 'Open'
new  10:                (Price.Unitprice*'30'), 'Open'
old  12:        where Price.Inum='&vInum'
new  12:        where Price.Inum='101'
old  13:                and Price.Spnum='&vSpnum'
new  13:                and Price.Spnum='099'

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> select '***** Order number is ----->' ||rtrim(Onum.Currval)||'' from OrderDetail;

'*****ORDERNUMBERIS----->'||RTRIM(ONUM.CURRVAL)||''
--------------------------------------------------------------------
***** Order number is ----->1001
***** Order number is ----->1001

2 rows selected.


However, in the display Order Number is..., it comes out 2 results. How to fix it?

and the results of the select statement for the table in order to test is:
SQL> select * from OrderDetail;

                                                   Item Supplier
      ONUM ODATE          RDATE          EON ERN Number   Number   ORDERQTY
---------- -------------- -------------- --- --- ------ -------- ----------
      Unit
     Price     AMTDUE ORDERSTATUS
---------- ---------- ------------
      1000 17-5¤ -07                    E1         101       99         20
     $2.00         40 Open

      1001 17-5¤ -07                    E2         101       99         30
     $2.00         60 Open


2 rows selected.
Re: Sequence in SQL [message #238245 is a reply to message #238239] Thu, 17 May 2007 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You missed something but as we are not behind you to see what you've done we can't help you.

Restart from the beginning and post ALL statements you do then we can explain.

Regards
Michel
Re: Sequence in SQL [message #238249 is a reply to message #238245] Thu, 17 May 2007 03:31 Go to previous message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

Yay, I got it lah. Thanks a lot.

Sincerely,

Vickie
Previous Topic: order by the value from column
Next Topic: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Goto Forum:
  


Current Time: Thu Dec 08 12:27:42 CST 2016

Total time taken to generate the page: 0.14857 seconds