Home » SQL & PL/SQL » SQL & PL/SQL » To create unique id for a group
To create unique id for a group [message #291302] Thu, 03 January 2008 14:15 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
CREATE TABLE A
(
ACCT_ID NUMBER,
ACCT_NUM VARCHAR2(20 BYTE)
)

The data is

acct id acct_num
==========================
ACCT1
ACCT1
ACCT1
ACCT1
ACCT2
ACCT2
ACCT3

I have a sequence which should populate a number that is unique for the acct_num

so basically i want the data to look like this-

acct id acct_num
==========================
1 ACCT1
1 ACCT1
1 ACCT1
1 ACCT1
2 ACCT2
2 ACCT2
3 ACCT3

Not sure how to achieve this? ANy thoughts and clues. I will have to do this in an insert statement.
I can also set null acct id at insert and then do it at update. But not sure how to
to set unique acct id for the acct_num?

Thanks for your time
Re: To create unique id for a group [message #291303 is a reply to message #291302] Thu, 03 January 2008 14:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
IMO, the design is flawed.
What does "acctid" gain you over just using "acct_num"?
IOW, what is the benefit from having two unique identifiers for any single account?
Re: To create unique id for a group [message #291343 is a reply to message #291302] Thu, 03 January 2008 23:30 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Use analytic_functions to achieve this .

I hope this is Pseudo code.
Anyway I have seen such scenarion of double unique codes in applications handling hierarchical levels of subscribers.

Thumbs Up
Rajuvan.

[Updated on: Thu, 03 January 2008 23:30]

Report message to a moderator

Re: To create unique id for a group [message #291509 is a reply to message #291343] Fri, 04 January 2008 09:16 Go to previous message
bella13
Messages: 90
Registered: July 2005
Member
Rajuvan- thanks for your help. IT worked..

[Updated on: Fri, 04 January 2008 09:16]

Report message to a moderator

Previous Topic: ORA-06502: PL/SQL: numeric or value error: host bind array too small
Next Topic: How to insert a .jpg file in a table
Goto Forum:
  


Current Time: Thu Feb 13 13:52:27 CST 2025