Home » SQL & PL/SQL » SQL & PL/SQL » Help with Query (Oracle 11g, AIX 5.8)
Help with Query [message #589255] Wed, 03 July 2013 13:45 Go to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Hi,

I've a below tables.

1) tbl_link --> this table contains information at profile level
2) tbl_summary --> this table contains summary at parent profile level derived from tbl_link table

One parent profile contains multiple child profiles and each child profile links to a code (which is B, W, G or P) and the code is linked to a category (i.e. ONL and OFL). In this case code B is linked to category 'ONL' and codes W,G,P linked to OFL category.

ONL category needs 100 points. If it don't have enough points then i need to borrow from OFL category which i'm doing and populating into tbl_summary table at parent profile level.

Now i need to insert data into tbl_link table at profile level with howmany points used, expired based on tbl_summary table. Rule is at the end of month if we add points for each profile in tbl_link table it should come as 0.


Can someone help with the query.


with 
tbl_SUMMARY as
(
select 1 as ppid,'ONL' as catgcode, 53 as earned_points,47 BORROWED_POINTS,100 CERT_POINTS,0 DISCARD_POINTS,100 used from dual
union
select 1 as ppid,'OFL' as catgcode, 223 as earned_points,0 BORROWED_POINTS,176 CERT_POINTS,76 DISCARD_POINTS,100 used from dual
union
select 2 as ppid,'ONL' as catgcode, 39 as earned_points,61 BORROWED_POINTS,100 CERT_POINTS,0 DISCARD_POINTS,100 used from dual
union
select 2 as ppid,'OFL' as catgcode, 90 as earned_points,0 BORROWED_POINTS,29 CERT_POINTS,29 DISCARD_POINTS,100 used from dual
union
select 3 as ppid,'ONL' as catgcode, 109 as earned_points,0 BORROWED_POINTS,109 CERT_POINTS,9 DISCARD_POINTS,100 used from dual
union
select 3 as ppid,'OFL' as catgcode, 223 as earned_points,0 BORROWED_POINTS,223 CERT_POINTS,23 DISCARD_POINTS,200 used from dual
union
select 4 as ppid,'ONL' as catgcode, 109 as earned_points,0 BORROWED_POINTS,109 CERT_POINTS,9 DISCARD_POINTS,100 used from dual
union
select 4 as ppid,'OFL' as catgcode, 169 as earned_points,0 BORROWED_POINTS,169 CERT_POINTS,69 DISCARD_POINTS,100 used from dual
),
tbl_link as
(
select 1 as ppid,1 as pid, 'B' as code,'ONL' as catgcode, 53 as earned_points from dual
union
select 1 as ppid,12 as pid, 'W' as code,'OFL' as catgcode, 26 as earned_points from dual
union
select 1 as ppid,13 as pid, 'G' as code,'OFL' as catgcode, 87 as earned_points from dual
union
select 1 as ppid,14 as pid, 'P' as code,'OFL' as catgcode, 110 as earned_points from dual
union

select 2 as ppid,2 as pid, 'B' as code,'ONL' as catgcode, 39 as earned_points from dual
union
select 2 as ppid,22 as pid, 'W' ,'OFL' as catgcode, 30 as earned_points from dual
union
select 2 as ppid,23 as pid, 'G' ,'OFL' as catgcode, 29 as earned_points from dual
union
select 2 as ppid,24 as pid, 'P' ,'OFL' as catgcode, 31 as earned_points from dual
union

select 3 as ppid,3 as pid, 'B' as code,'ONL' as tier_catgcode, 109 as earned_points from dual
union
select 3 as ppid,32 as pid, 'W' ,'OFL' , 26 as earned_points from dual
union
select 3 as ppid,33 as pid, 'G' ,'OFL', 87 as earned_points from dual
union
select 3 as ppid,34 as pid, 'P' ,'OFL' , 110 as earned_points from dual
union

select 4 as ppid,4 as pid, 'B' as code,'ONL' as catgcode, 109 as earned_points from dual
union
select 4 as ppid,42 as pid, 'W' as code,'OFL' , 26 as earned_points from dual
union
select 4 as ppid,43 as pid, 'G' as code,'OFL' , 87 as earned_points from dual
union
select 4 as ppid,44 as pid, 'P' as code,'OFL' , 56 as earned_points from dual
),
final (PARENT_PROFILE_ID,PROFILE_ID,catgcode,EARNED_POINTS,BORROWED_POINTS,CERT_POINTS,DISCARD_POINTS,USED) 
as (
select A.PPID PARENT_PROFILE_ID,B.PID PROFILE_ID,A.catgcode,B.EARNED_POINTS,BORROWED_POINTS,CERT_POINTS,DISCARD_POINTS,USED
from tbl_SUMMARY a,tbl_link b where a.ppid=b.ppid AND A.catgcode=B.catgcode
ORDER BY PROFILE_ID
)
select * from final order by 1;

PARENT_PROFILE_ID  PROFILE_ID  CATGCODE  EARNED_POINTS  BORROWED_POINTS  CERT_POINTS  DISCARD_POINTS  USED
------------------ ----------- --------- -------------- ---------------  ------------ --------------  ------
1                  1           ONL       53             47               100          0               100
1                  14          OFL       110            0                176          76              100
1                  13          OFL       87             0                176          76              100
1                  12          OFL       26             0                176          76              100
2                  2           ONL       39             61               100          0               100
2                  24          OFL       31             0                29           29              100
2                  23          OFL       29             0                29           29              100
2                  22          OFL       30             0                29           29              100
3                  32          OFL       26             0                223          23              200
3                  33          OFL       87             0                223          23              200
3                  34          OFL       110            0                223          23              200
3                  3           ONL       109            0                109          9               100
4                  42          OFL       26             0                169          69              100
4                  43          OFL       87             0                169          69              100
4                  44          OFL       56             0                169          69              100
4                  4           ONL       109            0                109          9               100

For parent profile id 1 --> ONL category has 53 points, so borrowed 47 points from OFL category to make ONL points 100.
                            Now need to populate tbl_link table at child profile level (i.e. child profiles 12,13,14).
Borrowed 47 points from OFL and need to deduct this points from the profile which has highest earned points, in this case deduct from profile 14 which has 110 points. Need output like below

PARENT_PROFILE_ID  PROFILE_ID  CATGCODE  EARNED_POINTS  BORROWED_POINTS  CERT_POINTS  DISCARD_POINTS  USED  BURN_PTS  EXPIRE_PTS
------------------ ----------- --------- -------------- ---------------  ------------ --------------  ----- --------  -----------
1                  1           ONL       53             47               100          0               100   -53       0
1                  12          OFL       26             0                176          76              100   -26       0
1                  13          OFL       87             0                176          76              100   -87
1                  14          OFL       110            0                176          76              100   -63       -47


For parent profile id 2 --> ONL category has 39 points, so borrowed 61 points from OFL category to make ONL points 100.
                            Now need to populate tbl_link table at child profile level (i.e. child profiles 22,23,24).
Borrowed 61 points from OFL and need to deduct this points from the profile which has highest earned points, in this case deduct from profile 24 which has 31 points, from profile 22 which has 30 points. Need output like below

PARENT_PROFILE_ID  PROFILE_ID  CATGCODE  EARNED_POINTS  BORROWED_POINTS  CERT_POINTS  DISCARD_POINTS  USED  BURN_PTS  EXPIRE_PTS
------------------ ----------- --------- -------------- ---------------  ------------ --------------  ----- --------  -----------
2                  2           ONL       39             61               100          0               100   -39       0
2                  22          OFL       30             0                29           29              100   -30       0
2                  23          OFL       29             0                29           29              100   0         -29
2                  24          OFL       31             0                29           29              100   -31       0

For parent profile id 3 --> ONL category has 109 points, so no need to borrow points from OFL category
                            Now need to populate tbl_link table at child profile level (i.e. child profiles 32,33,34).
in this case ONL has 100 points, so move the remaining 9 points will be expired. OFL category has 223 points total. need only 200 points (i.e. mutiple of 100) for our process, 23 points will be expired and has to deduct from the profile which has highest earned points, in this case from profile 34. Output :

PARENT_PROFILE_ID  PROFILE_ID  CATGCODE  EARNED_POINTS  BORROWED_POINTS  CERT_POINTS  DISCARD_POINTS  USED  BURN_PTS  EXPIRE_PTS
------------------ ----------- --------- -------------- ---------------  ------------ --------------  ----- --------  -----------
3                  3           ONL       109            0                109          9               100   -100      -9
3                  32          OFL       26             0                223          23              200   -26       0
3                  33          OFL       87             0                223          23              200   -87       0
3                  34          OFL       110            0                223          23              200   -87       -23

For parent profile id 4 --> ONL category has 109 points, so no need to borrow points from OFL category
                            Now need to populate tbl_link table at child profile level (i.e. child profiles 42,43,44).
in this case ONL has 100 points, so move the remaining 9 points will be expired. OFL category has 169 points total. need only 100 points (i.e. mutiple of 100) for our process, 69 points will be expired and has to deduct from the profile which has highest earned points, in this case from profile 43. Output :

PARENT_PROFILE_ID  PROFILE_ID  CATGCODE  EARNED_POINTS  BORROWED_POINTS  CERT_POINTS  DISCARD_POINTS  USED  BURN_PTS  EXPIRE_PTS
------------------ ----------- --------- -------------- ---------------  ------------ --------------  ----- --------  -----------
4                  4           ONL       109            0                109          9               100   100       9
4                  42          OFL       26             0                169          69              100   -26       0
4                  43          OFL       87             0                169          69              100   -18       -69
4                  44          OFL       56             0                169          69              100   -56       0




Is it something doable in a query? Appreciate your help!

Thanks
SS
Re: Help with Query [message #589256 is a reply to message #589255] Wed, 03 July 2013 14:03 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
For parent profile 1, whatever i mentioned above is not correct. Borrowed 47 points from OFL to ONL to make ONL and also from OFL category has 176 points remaining after lending to ONL and using only 100 points, remaining 76 points are discarded. Need to deduct these 76 points also from child profiles. Output will be as below.


PARENT_PROFILE_ID  PROFILE_ID  CATGCODE  EARNED_POINTS  BORROWED_POINTS  CERT_POINTS  DISCARD_POINTS  USED  BURN_PTS  EXPIRE_PTS
------------------ ----------- --------- -------------- ---------------  ------------ --------------  ----- --------  -----------
1                  1           ONL       53             47               100          0               100   -53       0
1                  12          OFL       26             0                176          76              100   -26       0
1                  13          OFL       87             0                176          76              100   -74       -13
1                  14          OFL       110            0                176          76              100   -47       -63




Thanks
SS
Re: Help with Query [message #589257 is a reply to message #589256] Wed, 03 July 2013 15:12 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Hi,

Can someone help me on the above query?

Thanks
SS
Re: Help with Query [message #589258 is a reply to message #589257] Wed, 03 July 2013 15:40 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
If you are ever dissatisfied with the tone, tenor, completeness, correctness, or timeliness of any response,
please submit your request for a prompt & full refund to /dev/null

with free advice, you get what you paid for it sometimes.
Re: Help with Query [message #589259 is a reply to message #589258] Wed, 03 July 2013 15:53 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
@Blackswan,

Don't take me wrong. I'm working on this query from 2 days and didn't figure out a way (need to complete by thursday), so asking you guys help who are expers in Oracle. This forum helped me in the past a lot.

Thanks
Sri
Re: Help with Query [message #589262 is a reply to message #589259] Wed, 03 July 2013 17:49 Go to previous messageGo to next message
mikomi
Messages: 33
Registered: July 2013
Member
sspn2010 wrote on Wed, 03 July 2013 21:53
@Blackswan,

I'm working on this query from 2 days


..which is an indication of the extent of the task. I'm new here but it is probably unrealistic to expect people here to do this kind of stuff for you. This isn't a "can you do X in SQL" it's more like "do my day job for me".

If you can reduce the problem to its essence that would help.
Re: Help with Query [message #589263 is a reply to message #589262] Wed, 03 July 2013 19:22 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
@mikomi,

I'm working on the solution on my own, but not getting the end result, so asking SQL experts help here. Please don't think that i'm asking someone to do my job.

Thanks
SS
Re: Help with Query [message #589267 is a reply to message #589263] Wed, 03 July 2013 21:44 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>Please don't think that i'm asking someone to do my job.
What should we think when you are asking us to do your job?
Re: Help with Query [message #589271 is a reply to message #589267] Wed, 03 July 2013 23:58 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
@Blackswan

I'm asking your help.

Apologize me if my words hurts anyone!!!

Thanks
SS
Re: Help with Query [message #589325 is a reply to message #589271] Thu, 04 July 2013 07:27 Go to previous messageGo to next message
mikomi
Messages: 33
Registered: July 2013
Member
It is probably relatively trivial to do this in PL/SQL. Have you tried?
Re: Help with Query [message #589336 is a reply to message #589325] Thu, 04 July 2013 08:06 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
I've this query, but this is working for parent profile id 1 not for other profiles.


with 
tbl_SUMMARY as
(
select 1 as ppid,'ONL' as catgcode, 53 as earned_points,47 BORROWED_POINTS,100 CERT_POINTS,0 DISCARD_POINTS,100 used from dual
union
select 1 as ppid,'OFL' as catgcode, 223 as earned_points,0 BORROWED_POINTS,176 CERT_POINTS,76 DISCARD_POINTS,100 used from dual
union
select 2 as ppid,'ONL' as catgcode, 39 as earned_points,61 BORROWED_POINTS,100 CERT_POINTS,0 DISCARD_POINTS,100 used from dual
union
select 2 as ppid,'OFL' as catgcode, 90 as earned_points,0 BORROWED_POINTS,29 CERT_POINTS,29 DISCARD_POINTS,100 used from dual
union
select 3 as ppid,'ONL' as catgcode, 109 as earned_points,0 BORROWED_POINTS,109 CERT_POINTS,9 DISCARD_POINTS,100 used from dual
union
select 3 as ppid,'OFL' as catgcode, 223 as earned_points,0 BORROWED_POINTS,223 CERT_POINTS,23 DISCARD_POINTS,200 used from dual
union
select 4 as ppid,'ONL' as catgcode, 109 as earned_points,0 BORROWED_POINTS,109 CERT_POINTS,9 DISCARD_POINTS,100 used from dual
union
select 4 as ppid,'OFL' as catgcode, 169 as earned_points,0 BORROWED_POINTS,169 CERT_POINTS,69 DISCARD_POINTS,100 used from dual
),
tbl_link as
(
select 1 as ppid,1 as pid, 'B' as code,'ONL' as catgcode, 53 as earned_points from dual
union
select 1 as ppid,12 as pid, 'W' as code,'OFL' as catgcode, 26 as earned_points from dual
union
select 1 as ppid,13 as pid, 'G' as code,'OFL' as catgcode, 87 as earned_points from dual
union
select 1 as ppid,14 as pid, 'P' as code,'OFL' as catgcode, 110 as earned_points from dual
union

select 2 as ppid,2 as pid, 'B' as code,'ONL' as catgcode, 39 as earned_points from dual
union
select 2 as ppid,22 as pid, 'W' ,'OFL' as catgcode, 30 as earned_points from dual
union
select 2 as ppid,23 as pid, 'G' ,'OFL' as catgcode, 29 as earned_points from dual
union
select 2 as ppid,24 as pid, 'P' ,'OFL' as catgcode, 31 as earned_points from dual
union

select 3 as ppid,3 as pid, 'B' as code,'ONL' as tier_catgcode, 109 as earned_points from dual
union
select 3 as ppid,32 as pid, 'W' ,'OFL' , 26 as earned_points from dual
union
select 3 as ppid,33 as pid, 'G' ,'OFL', 87 as earned_points from dual
union
select 3 as ppid,34 as pid, 'P' ,'OFL' , 110 as earned_points from dual
union

select 4 as ppid,4 as pid, 'B' as code,'ONL' as catgcode, 109 as earned_points from dual
union
select 4 as ppid,42 as pid, 'W' as code,'OFL' , 26 as earned_points from dual
union
select 4 as ppid,43 as pid, 'G' as code,'OFL' , 87 as earned_points from dual
union
select 4 as ppid,44 as pid, 'P' as code,'OFL' , 56 as earned_points from dual
),
tst_sys_tier as
 (select 'ONL' as catgcode, 1 as rank, 100 min_points, 100 max_points from dual
  union
  select 'OFL' as catgcode, 2 as rank, 100 min_points, 1000 max_points from dual
  ),
final (PARENT_PROFILE_ID,PROFILE_ID,catgcode,EARNED_POINTS,BORROWED_POINTS,CERT_POINTS,DISCARD_POINTS,USED, rank, min_points, max_points) 
as (
select A.PPID PARENT_PROFILE_ID,B.PID PROFILE_ID,A.catgcode,B.EARNED_POINTS,BORROWED_POINTS,CERT_POINTS,DISCARD_POINTS,USED, c.rank, c.min_points, c.max_points
from tbl_SUMMARY a,tbl_link b, tst_sys_tier c where a.ppid=b.ppid AND A.catgcode=B.catgcode and a.catgcode = c.catgcode
ORDER BY PROFILE_ID
),
data1 as
(
select a.*,
 lag(a.borrowed_points) over(partition by a.parent_profile_id order by parent_profile_id, rank, earned_points desc) next_borrowed_pts, 
 row_number() over(partition by a.parent_profile_id, a.catgcode order by parent_profile_id, rank, earned_points desc) sub_rank
  from final a where parent_profile_id=1 order by parent_profile_id, rank, earned_points desc
),
data2 as (
select a.*,
       case when sub_rank = 1 and rank != sub_rank and next_borrowed_pts < earned_points then
                 earned_points - next_borrowed_pts
            when sub_rank = 1 and rank != sub_rank and next_borrowed_pts > earned_points then 
                 earned_points
             else 
                next_borrowed_pts
              end as balance_pts,
       case when sub_rank = 1 and rank != sub_rank and next_borrowed_pts < earned_points then
                 discard_points -(earned_points - next_borrowed_pts)
            when sub_rank = 1 and rank != sub_rank and next_borrowed_pts > earned_points then     
                 earned_points
             else next_borrowed_pts end as balance_discard_pts     
  from data1 a),
data3 as (  
select a.*, lag(balance_discard_pts) over(partition by parent_profile_id, catgcode order by parent_profile_id, rank, earned_points desc) next_r1
  from data2 a)
select a.*,  
     case when rank=1 and rank=sub_rank 
              then -1*least(earned_points, min_points)
             else
                case when sub_rank = 1 and rank != sub_rank and discard_points > balance_pts then -1*next_borrowed_pts
                  else case when next_r1 != 0
                          then -1*(earned_points - next_r1)
                       else -1*earned_points end                            
             end 
            end as balance_points,
      case when rank=1 and rank=sub_rank 
              then -1*least(earned_points, discard_points)
             else
                case when sub_rank = 1 and rank != sub_rank then -1*balance_pts
                  else -1*next_r1                            
             end 
            end as expired_points      
  from data3 a
   order by parent_profile_id, rank, earned_points;

PARENT_PROFILE_ID  PROFILE_ID  CATGCODE  EARNED_POINTS  BORROWED_POINTS  CERT_POINTS  DISCARD_POINTS  USED  BURN_PTS  EXPIRE_PTS
------------------ ----------- --------- -------------- ---------------  ------------ --------------  ----- --------  -----------
1                  1           ONL       53             47               100          0               100   -53       0
1                  12          OFL       26             0                176          76              100   -26       0
1                  13          OFL       87             0                176          76              100   -74       -13
1                  14          OFL       110            0                176      47  76              100   -47       -63





Thanks
SS
Re: Help with Query [message #589342 is a reply to message #589336] Thu, 04 July 2013 08:51 Go to previous messageGo to next message
mikomi
Messages: 33
Registered: July 2013
Member
I was looking at this last night and I couldn't see how you could programmatically do it in SQL unless you used MODEL ... ITERATE UNTIL because you seem to need to do something like:
WHILE (ONL.BORROWED_POINTS > 0)
LOOP
ONL.BORROWED_POINTS = ONL.BORROWED_POINTS minus <earned_points from some OFL row>
<EARNED_POINTS FROM SOME OFL ROW> = 0
END LOOP

I.e. you might need to take earned_points from zero OFL rows, one OFL row, two OFL rows.. n OFL rows.
Not sure you can iterate in SQL in any way outside of the MODEL clause. Perhaps the gurus here can enlighten with a way.

I had a stab at doing it in PL/SQL for you but your logic seemed to be missing sufficient details and it was late and I was tired.

Re: Help with Query [message #589366 is a reply to message #589342] Thu, 04 July 2013 13:14 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Thanks mikomi. Will google about MODEL clause. Hope gurus here will help me out.

Thanks
SS
Re: Help with Query [message #589433 is a reply to message #589366] Fri, 05 July 2013 10:42 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Gurus,

Can someone help me with the query using MODEL clause.

Thanks
SS
Re: Help with Query [message #589434 is a reply to message #589433] Fri, 05 July 2013 11:02 Go to previous message
mikomi
Messages: 33
Registered: July 2013
Member
Sorry, I could help you with PL/SQL but the MODEL clause I'm just a beginner with myself.
Previous Topic: print the columns of a table
Next Topic: Does Mutating error throw exception
Goto Forum:
  


Current Time: Sat Aug 30 23:57:46 CDT 2014

Total time taken to generate the page: 0.17073 seconds