Home » SQL & PL/SQL » SQL & PL/SQL » Help with sql code (10 G)
Help with sql code [message #664667] Wed, 26 July 2017 21:30 Go to next message
ramkumar10
Messages: 19
Registered: March 2017
Junior Member
Hi I have a scenerio which I am not able to wrap my head around

I have a table with data like below
Acc    Pol  
A1     b1
A1     b2
A1     b3
A2     b4
A2     b5
and so on
Note: We have a maximum of 300 Pol for one acc i.e A1 can have B1,B2,.....upto 300

The requirement is I have to migrate this data as 1000 rows maximum as batch one and mark colc as 0 and the next 1000(max) rows the colC as 1 and so on. But the catch is all the pol under one acc should be migrated together.

example if there are 300 pol under acc A1,A2,A3 respectively and 50 pol under acc A4 and 60 pol under acc A5 then only 950 pol should be marked as 0 and remaining 60 pol of A5 should be marked as 1

expected output
acc      pol        id
A1       B1         0 
A1       B2         0 
.         .         0 
.         .         0 
.         .         0 
.         .         0 
A1      B300        0 
A2      b301        0 
.         .         .
.         .         . 
A3      B300        0 
.         .         0 
.         .         0 
.         .         0 
.         .         0 
A4      B300        0 
.         .         0 
.         .         0
.         .         0
.         .         0
A5      B50         0
.         .         0
.         .         0
.         .         0
.         .         0
A6      B60         1

Also sorry for the above formatting I tried my best to do it but this is how far I could do.

In the above o/p I only migrated 960 rows as batch 0 and remaining 60 rows as batch one cuz I can only have a maximum of 1000 records in each batch and all the pol for a record shuld be migrated together
please advice how to do it

thanks for any inputs.


[EDITED by LF: applied [code] tags to improve formatting]

[Updated on: Thu, 27 July 2017 01:06] by Moderator

Report message to a moderator

Re: Help with sql code [message #664668 is a reply to message #664667] Wed, 26 July 2017 21:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Help with sql code [message #664671 is a reply to message #664667] Thu, 27 July 2017 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Following the text and not the expected output which are inconsistent:

Quote:
example if there are 300 pol under acc A1,A2,A3 respectively and 50 pol under acc A4 and 60 pol under acc A5 then only 950 pol should be marked as 0 and remaining 60 pol of A5 should be marked as 1
SQL> col acc format a3
SQL> col pol format a4
SQL> col batch format 99990
SQL> with acc as (select level acc from dual connect by level <= 5)
  2  select 'A'||to_char(acc) acc,
  3         'B'||to_char(column_value) pol,
  4         trunc(count(*) over (order by acc range between unbounded preceding and current row)
  5               / 1000) batch
  6  from acc,
  7       table(cast(multiset(select level from dual
  8                           connect by level <= case
  9                                                 when acc in (1,2,3) then 300
 10                                                 when acc = 4 then 50
 11                                                 when acc = 5 then 60
 12                                               end)
 13             as sys.odciNumberList))
 14  order by acc, column_value
 15  /
result

[Updated on: Thu, 27 July 2017 01:11]

Report message to a moderator

Re: Help with sql code [message #664672 is a reply to message #664671] Thu, 27 July 2017 02:25 Go to previous messageGo to next message
ramkumar10
Messages: 19
Registered: March 2017
Junior Member
Thank-you so much for taking time to write that sql code. Though I will probably take a life time to understand it completely. (I am at least until now I thought I was above average in sql but today u proved it wrong)

I have re-iterated the scenerio again below hopefully this time better than the last time

also pol and acc is varchar in my table.
Acc	Pol	Cycle id
A1	P1	0
A1	P2	0
A1	P3	0
A2	P4	0
A2	P5	0
A2	P6	0
A3..........	P7	0
A59	P995	0
A59	P996	0
A60	P997	1
A60	P998	1
A60	P999	1
A60	P1000	1
A60	P1001	1

The number of accounts is not static i.e it is just not 5 accounts it can be any number of accounts.
Note:The max number of policies in one batch is 1000 and maximum number of policies under one account is 300.
(and each record is a policy)

The issue I am facing is say my 996th policy(record) is from account say A60 so ID column is marked 0 for this policy, But A60 has 5 more policies under it so the next 4 policies under it is marked as 0 and the last policy is marked as 1.This is wrong.

Id column should be 0 till account A59 that is until 995th record and A60 th account policies should be marked as 1 (ie these policies will be migrated in next batch). Same for the next batch(ie ideally next thousand records) and so on

Hope I could explain better this time.

Again I appreciate the time and effort.
Re: Help with sql code [message #664681 is a reply to message #664671] Thu, 27 July 2017 22:45 Go to previous message
ramkumar10
Messages: 19
Registered: March 2017
Junior Member
Hey thanks for your time I have done the scenerio using ETL tool. To be honest my sql skills are intermediate level so If u get some time and are feeling like it I would appreciate if you could show me how to do this scenerio in sql.

Again thank-you.
Previous Topic: String manipulation
Next Topic: Count , Per Week Group by
Goto Forum:
  


Current Time: Fri Mar 29 08:00:01 CDT 2024