September 5, 2008 04:33pm pdt

Member Login

Hall of Fame

Yearly

1schwertner 562,829
2sdstuber 530,609
3angelIII 251,505
4sujith80 159,881
5jinesh_ka... 148,880
6markgeer 119,551
7sonicefu 100,615
8DrSQL 100,582
9rolutola 95,915
10johnsone 93,974
11dbmullen 88,130
12aiklamha 80,153
13nav_kum_v 71,337
14sventhan 63,236
15MikeOM_... 50,689
16rbrooker 49,506
17awking00 43,123
18Dr_Billy 42,485
19dvz 42,149
20ee_rlee 42,125
21joebednarz 38,246
22Milleniuma... 35,793
23gatorvip 28,623
24mohamma... 26,872
25ajexpert 25,730

	[x] Attachment Details

Creating a unique ID using a cursor

Zones: Oracle Database, Oracle 8.x

Tags: Oracle

how do i insert a unique 9 digit ID where i want the first two digits to be a numeric code?

for instance, i have 2 records w/ temp_code of 28 i want the ID to be
28000000001
28000000002

then the next record in the cursor is w/ temp_code of 13 for example, i want the ID to be
13000000001
13000000002

and so on ( n_supid is the ID i want to contain the first 2 digits of temp_code and then keep incrementing for all
temp_code alike).

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

           
           
             CURSOR c1 is
SELECT DISTINCT sup_desc , temp_code FROM temptable order by temp_code ; ;
 
 
BEGIN
 
	FOR sup_rec in c1
	LOOP
	
	n_deptid := to_number ( to_char(sup_rec.temp_code) || '1');	
	n_supid := to_number (to_char( sup_rec.temp_code) ||'0000001');
			
	INSERT INTO new_table (SupID, SupDesc, DeptID)	
	  VALUES (n_supid, sup_rec.sup_desc , n_deptid);		      	 
	 n_supid++;
	 
	END LOOP;
 
END;

           
         

Start your free trial to view this solution

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

7.8

Question Stats

Zone: Database

Question Asked By: badtz7229

Solution Provided By: sdstuber

Participating Experts: 1

Solution Grade: B

Translate:

Loading Advertisement...

Open Discussion

Comment by sdstuber

what more could I have done for an A?

20080723-EE-VQP-34 / EE_QW_2_20070628