CODEWITHSUNDEEP
sqloracle-databaseplsql
BFF
BFF

Reputation: 396

Adding a new column that auto increments based on existing field values

Not sure how to do this, but I want to add a new column "Jersey" that will increment by 1 depending on the "Sport" value in both tab_A and tab_B. So if a "Sport" already exists in tab_B, then just grab the max jersey_no and add 1 for the new column. That one is easy.

Now if the "Sport" does not exist in tab_B, then give it a "Jersey" value of 100 for the new column. However, if there is more than one of the same "Sport" in tab_A (but do not exist in tab_B), then it should start with 100 and increment by 1 for the next same Sport, and so on (e.g. see Garcia example below).

I created a sequence "seqnce" but, that really didn't help at all. Is there another way to accomplish this? Thanks in advance!

Tab_A

 Name       State       Sport           
 Garcia      CA         Basketball      
 Garcia      AL         Basketball      
 Garcia      NY         Basketball      
 McGee       CA         Swimming        
 Tontou      CA         Football        
 Tontou      AL         Swimming

Tab_B

 Name     Sport         Jersey_No
 Garcia   Swimming      100
 Garcia   Football      100
 McGee    Swimming      101
 Tontou   Swimming      101
 Tontou   Swimming      102

Expected Output

 Name       State       Sport           Jersey
 Garcia      CA         Basketball      100
 Garcia      AL         Basketball      101
 Garcia      NY         Basketball      102
 McGee       CA         Swimming        102    
 Tontou      CA         Football        100
 Tontou      AL         Swimming        103

My Code

select name, state, sport
      ,nvl ((select max(b.jersey_no + 1) from tab_b b
                where b.sport = a.sport
                and   b.name = a.name),
        (case
        when not exists (select 1 from tab_b b 
                where b.sport = a.sport
                and   b.name = a.name
                having count(a.sport) > 1) 
        then seqnce.nextval 
        else '100'
        end )
        ) Jersey
from tab_a

Upvotes: 1

Views: 70

Answers (1)

Rustam Pulatov
Rustam Pulatov

Reputation: 665

If it's only select result then using row_number(). If you need update column in table, then write trigger

Example this:

WITH taba AS
(SELECT 'Garcia'  Name,     'CA'  State,       'Basketball'  Sport  from dual
UNION ALL
SELECT 'Garcia'  Name,     'AL'  State,       'Basketball'  Sport  from dual
UNION ALL
SELECT 'Garcia'  Name,     'NY'  State,       'Basketball'  Sport  from dual 
UNION ALL
SELECT 'McGee'  Name,     'CA'  State,       'Swimming'  Sport  from dual 
UNION ALL
SELECT 'Tontou'  Name,     'CA'  State,       'Football'  Sport  from dual
UNION ALL
SELECT 'Tontou'  Name,     'AL'  State,       'Swimming'  Sport  from dual),
tabb AS
(SELECT 'Garcia'  Name,  'Swimming'  Sport, 100 Jersey   from dual
UNION ALL
SELECT 'Garcia'  Name,  'Football',   100 from dual
UNION ALL
SELECT 'McGee'  Name,  'Swimming',   101 from dual 
UNION ALL
SELECT 'Tontou'  Name,  'Swimming', 101 from dual
UNION ALL
SELECT 'Tontou'  Name,  'Swimming', 102 from dual)
SELECT taba.Name, 
taba.State ,  
taba.Sport,  
row_number() over(partition by taba.Name,  taba.Sport ORDER BY taba.State) 
            + nvl((SELECT MAX(tabb.Jersey) 
                    FROM tabb
                    WHERE taba.name = tabb.name 
                    AND taba.sport = tabb.sport), 99) 
FROM taba

result:

Garcia  AL  Basketball  100
Garcia  CA  Basketball  101
Garcia  NY  Basketball  102
McGee   CA  Swimming    102
Tontou  CA  Football    100
Tontou  AL  Swimming    103

Upvotes: 1

Related Questions