OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Serial Number (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Serial Number [message #591868]

Wed, 31 July 2013 23:55

mamalik
Messages: 270
Registered: November 2008
Location: Pakistan

Senior Member

Dear All
I have follwoing Table

create table abc( prd_cod number,tax_num number, tkt_num number,shd_cod number,cnt_num number,ctn_num number);

Insert into ABC(PRD_COD, TAX_NUM, TKT_NUM, SHD_COD, CNT_NUM, CTN_NUM) Values   (1, 1, 1, 1, 1, 1);
Insert into ABC(PRD_COD, TAX_NUM, TKT_NUM, SHD_COD, CNT_NUM, CTN_NUM) Values   (1, 1, 1, 1, 2, 1);
Insert into ABC(PRD_COD, TAX_NUM, TKT_NUM, SHD_COD, CNT_NUM, CTN_NUM) Values   (1, 1, 1, 2, 1, 1);
Insert into ABC(PRD_COD, TAX_NUM, TKT_NUM, SHD_COD, CNT_NUM, CTN_NUM) Values   (1, 1, 2, 1, 1, 1);
Insert into ABC(PRD_COD, TAX_NUM, TKT_NUM, SHD_COD, CNT_NUM, CTN_NUM) Values   (1, 5, 1, 2, 1, 1);
Insert into ABC(PRD_COD, TAX_NUM, TKT_NUM, SHD_COD, CNT_NUM, CTN_NUM) Values   (2, 1, 1, 1, 1, 1);
COMMIT;

Now i have following data

PRD_COD	TAX_NUM	TKT_NUM	SHD_COD	CNT_NUM	CTN_NUM

1	1	1	1	1	1
1	1	1	1	2	1
1	1	1	2	1	1
1	1	2	1	1	1
1	5	1	2	1	1
2	1	1	1	1	1

I need to query above data with a column "Srl_Num". Its serial number and serial should like 'AA1','AB1'. In serial number is last digit 1 is "Ctn_Num" Column Value. First Letter 'A' of serial number is 'A' and second letter of serial number will change. When it reaches till 'Z' Then first letter will change.I mean if serial number is 'AZ1' Then next serial number will be 'BA1','BB1','BC1' and so on.

I need query to show data like

Srl_Num PRD_COD	TAX_NUM	TKT_NUM	SHD_COD	CNT_NUM	CTN_NUM

AA1     1	1	1	1	1	1
AB1     1	1	1	1	2	1
AC1     1	1	1	2	1	1
AD1	1       1	2	1	1	1
AE1     1	5	1	2	1	1
AF1     2	1	1	1	1	1

Thanks

Report message to a moderator

Re: Serial Number [message #591870 is a reply to message #591868]

Thu, 01 August 2013 00:31

Michel Cadot
Messages: 68764
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> with 
  2    data as (
  3      select abc.*,
  4             row_number() over (order by null)-1 rn
  5      from abc
  6    )
  7  select chr(ascii('A')+trunc(rn/26))||chr(ascii('A')+mod(rn,26))||ctn_num Srl_Num,
  8         data.*       
  9  from data
 10  /
SRL_NUM       PRD_COD    TAX_NUM    TKT_NUM    SHD_COD    CNT_NUM    CTN_NUM         RN
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
AA1                 1          1          1          1          1          1          0
AB1                 1          1          1          1          2          1          1
AC1                 1          1          1          2          1          1          2
AD1                 1          1          2          1          1          1          3
AE1                 1          5          1          2          1          1          4
AF1                 2          1          1          1          1          1          5

Regards
Michel

[Updated on: Thu, 01 August 2013 11:08]

Report message to a moderator

Re: Serial Number [message #591950 is a reply to message #591870]

Thu, 01 August 2013 10:57

thelearner
Messages: 133
Registered: April 2013
Location: INDIA

Senior Member

Sir,

Please explain to me how it works......

Thanks & Regards,
The Learner.

Report message to a moderator

Re: Serial Number [message #591956 is a reply to message #591950]

Thu, 01 August 2013 11:17

Michel Cadot
Messages: 68764
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ascii('A') gives the ascii code for A
ascii('A')+0 gives the ascii code for A
chr(ascii('A')+0) gives the character A
ascii('A')+1 gives the ascii code for B
chr(ascii('A')+1) gives the character B
ascii('A')+2 gives the ascii code for C
chr(ascii('A')+2) gives the character C
...
Based on this, execute the following:

with data as (select level rn from dual connect by level <= 100)
select chr(ascii('A')+trunc(rn/26)) "First letter", --> increment after each 26 lines --> trunc
       chr(ascii('A')+mod(rn,26)) "Second letter" --> increment at each line, reset when reach 26 -> mod
from data
order by rn
/

results

Report message to a moderator

Previous Topic:	Analytic functions - sum for last 3,6,12 month
Next Topic:	problem with function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 02 19:56:32 CDT 2025