Home » SQL & PL/SQL » SQL & PL/SQL » Problem in incrementing the column value (Oracle 10g)
Problem in incrementing the column value [message #434382] Wed, 09 December 2009 01:56 Go to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Hi everyone

I've got a problem in which I have to increment certain values against duplicate values in a table. I have a Table which has 3 columns - NTN, Employer_NTN and Business_Serial. Now what I want is that if the Employer_NTN is different for same NTN, the Business_Serial should be incremented by 1. For example

NTN Employer_NTN Business_Serial
--------------------------------------------------
1234567 0000012 1
1234567 0000013 1
1234567 0000014 1
4567890 0000025 0
4567890 0000026 0
4567890 0000027 0
4567890 0000028 0

Now what I want is a query which does the following.

NTN Employer_NTN Business_Serial
--------------------------------------------------
1234567 0000012 1
1234567 0000013 2
1234567 0000014 3
4567890 0000025 0
4567890 0000026 1
4567890 0000027 2
4567890 0000028 3

I used rownum() over partition but as I have to perform 2 checks now, one with NTN and the other with Employer_NTN, I am not quite sure how to do that. I have tried several queries with rownum() over partition but the Business_Serial is not incremented.

Please help me with this.

Thanks

Hammad
Re: Problem in incrementing the column value [message #434387 is a reply to message #434382] Wed, 09 December 2009 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried.
Post a working Test case: create table and insert statements along with the result you want with these data.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Problem in incrementing the column value [message #434389 is a reply to message #434382] Wed, 09 December 2009 02:19 Go to previous messageGo to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Ok I tried this

update IT_PATCH_091209 a
set a.business_serial = (select a.business_serial + Rn from
select ntn, employer_ntn, row_number() over (partition by ntn order by ntn) Rn 
from IT_PATCH_091209)f where f.rowid = a.rowid and f.employer_ntn <> a.employer_ntn)
Re: Problem in incrementing the column value [message #434393 is a reply to message #434389] Wed, 09 December 2009 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you think this is a properly indented SQL statement?
Sorry but I can't read it like this.

And you didn't post a test case.

Regards
Michel
Re: Problem in incrementing the column value [message #434397 is a reply to message #434389] Wed, 09 December 2009 02:45 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And the result was?

Well, it was probabvly wrong - your posted requried results don't want you to add a number to the business_serial, they want you to replace the business serial with another value.

Plus your code won't compile as you're missing a bracket.

Plus this condition
where f.rowid = a.rowid and f.employer_ntn <> a.employer_ntn
means you'll get no rows returned, as if the rowids of the two rows match then they're the same row, and all the other values will match too.

Here's a couple of ways of doing it:
create table test_107 (col_1 number, col_2 number,col_3 number);

insert into test_107 values (1,1,null);
insert into test_107 values (1,2,null);
insert into test_107 values (1,3,null);
insert into test_107 values (1,4,null);
insert into test_107 values (2,1,null);
insert into test_107 values (2,2,null);
insert into test_107 values (2,3,null);
insert into test_107 values (2,4,null);

commit;

update test_107 t1
set col_3 = (select rnum
             from   (select col_1
                           ,col_2
                           ,row_number() over (partition by col_1 order by col_2) rnum
                     from   test_107) t2
             where  t1.col_1 = t2.col_1
             and    t1.col_2 = t2.col_2);

             
merge into test_107 tgt
using (select col_1
             ,col_2
             ,row_number() over (partition by col_1 order by col_2) rnum
       from   test_107) src
on (tgt.col_1 = src.col_1 and tgt.col_2 = src.col_2)
when matched then update set tgt.col_3 = src.rnum;
Previous Topic: Other Possibilities - SQL Query
Next Topic: Wrapped procedure help please
Goto Forum:
  


Current Time: Sat Dec 03 01:16:21 CST 2016

Total time taken to generate the page: 0.18909 seconds