Solved

forwarding out dates

Posted on 2012-03-26
17
287 Views
Last Modified: 2012-06-21
Hi
Using Oracle 11g and really stuck on this one.


Fowarding out dates subject to value in "Repeats" column- if no value then leave as is (single row)
The values in Period will be:
Daily,Weekly,Monthly,Quarterly,Bi-Annual,Yearly

Original data set ..
select * from mytable
Product      Supplier Date            Period       Repeats
A            AA            04/30/2012      Monthly            4
A            AB            04/30/2012      Monthly            
B            BB            02/28/2012      Daily            2
C            CC                              n/a                  
C            CA            05/31/2012      Bi-Annual      3
C            CA            05/31/2012      Monthly            

To be able to return the following data set with two extra columns and additional rows..

Product      Supplier Date            Period       Repeats      Tranche Date2
A            AA            04/30/2012      Monthly            4            1            04/30/2012  --original
A            AA            04/30/2012      Monthly            4            2            05/31/2012      --added row
A            AA            04/30/2012      Monthly            4            3            06/30/2012  --added row
A            AA            04/30/2012      Monthly            4            4            07/31/2012  --added row
A            AB            04/30/2012      Monthly                                                      -- original - no change
B            BB            02/28/2012      Daily            2            1            02/28/2012  --original
B            BB            02/28/2012      Daily            2            2            03/01/2012  --added row
C            CC                              n/a                                                            -- original - no change
C            CA            05/31/2012      Bi-Annual      3            1            05/31/2012  --original
C            CA            05/31/2012      Bi-Annual      3            2            11/30/2012  --added row
C            CA            05/31/2012      Bi-Annual      3            3            05/31/2013  --added row
C            CA            05/31/2012      Monthly                                        -- original - no change
0
Comment
Question by:philsivyer
  • 9
  • 8
17 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 37766189
B            BB            02/28/2012      Daily            2            2            03/01/2012  --added row


is that correct?

2012 is a leap year,  why isn't that 2/29/2012?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37766193
assuming 2/29/2012 is correct, then try this...



SELECT product,
       supplier,
       mydate,
       period,
       repeats,
       nvl2(repeats,
       CASE period
           WHEN 'Daily' THEN mydate + COLUMN_VALUE
           WHEN 'Weekly' THEN mydate + (7 * COLUMN_VALUE)
           WHEN 'Monthly' THEN ADD_MONTHS(mydate, COLUMN_VALUE)
           WHEN 'Quarterly' THEN ADD_MONTHS(mydate, 3 * COLUMN_VALUE)
           WHEN 'Bi-Annual' THEN ADD_MONTHS(mydate, 6 * COLUMN_VALUE)
           WHEN 'Yearly' THEN ADD_MONTHS(mydate, 12 * COLUMN_VALUE)
       END,null)
           tranche_date2
  FROM mytable,
       TABLE(SELECT     COLLECT(LEVEL - 1)
                   FROM DUAL
             CONNECT BY LEVEL <= repeats)
0
 

Author Comment

by:philsivyer
ID: 37766393
For this to work does 'FROM mytable' have to be a table or can it be a query
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37766400
mytable can be a query, just put it in ()
0
 

Author Comment

by:philsivyer
ID: 37766497
In my query which returns the base data it is using a package - when I add your logic then it seems to not like the package - any ideas.
The error is:

 wrong number or types of arguments in call to

Regards
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37766505
I'd have to see the code
0
 

Author Comment

by:philsivyer
ID: 37766599
Hello
Tried this on another query and column date2 seems to be working (great) - what about the column "tranche" - is that easy to add in?

Regards
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37766612
oops, sorry, I thought that that was all one column

I see you have the counter in there now


SELECT product,
       supplier,
       mydate,
       period,
       repeats,
       nvl2(repeats,column_value+1,null) tranche,
       nvl2(repeats,
       CASE period
           WHEN 'Daily' THEN mydate + COLUMN_VALUE
           WHEN 'Weekly' THEN mydate + (7 * COLUMN_VALUE)
           WHEN 'Monthly' THEN ADD_MONTHS(mydate, COLUMN_VALUE)
           WHEN 'Quarterly' THEN ADD_MONTHS(mydate, 3 * COLUMN_VALUE)
           WHEN 'Bi-Annual' THEN ADD_MONTHS(mydate, 6 * COLUMN_VALUE)
           WHEN 'Yearly' THEN ADD_MONTHS(mydate, 12 * COLUMN_VALUE)
       END,null)
           date2
  FROM mytable,
       TABLE(SELECT     COLLECT(LEVEL - 1)
                   FROM DUAL
             CONNECT BY LEVEL <= repeats)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:philsivyer
ID: 37766693
I have never used this logic before - do you mind explaining the logic please.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37766799
I hope this helps.  It is a little arcane, even if not all that complicated

SELECT  level  FROM DUAL CONNECT BY LEVEL <= 10

This is just a counter query.


SELECT     COLLECT(LEVEL - 1)
                   FROM DUAL
             CONNECT BY LEVEL <= repeats

This is the same query except instead of a hardcoded 10, I use your repeats column.
Also, the COLLECT function is needed to "gather" or "collect" all the values with respect to each row.  Sort of like doing a SUM/GROUP BY, except instead of adding the values, I'm just "collecting" them to be used later.

TABLE(SELECT     COLLECT(LEVEL - 1)
                   FROM DUAL
             CONNECT BY LEVEL <= repeats)

Using the TABLE function on the collection acts as a join,  so, for each row in mytable, you will get a cartesian product of counters.


So, I'll have 4 rows with column_values 0,1,2,3  for repeats=4
1 row with column_value=0 for repeats NULL
2 rows with column_values 0,1 for repeats=2
etc.

Then it's just a matter of checking the period to use the proper date math
0
 

Author Comment

by:philsivyer
ID: 37771883
Last question
why  COLLECT(LEVEL - 1)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37771910
because I want to include the "base" date so I need to start from 0.

I could have used COLLECT(LEVEL)  and then used (column_value - 1)  in the CASE statement.

I have to do the 0-based range somewhere.  So I did it in the collecting.
0
 

Author Comment

by:philsivyer
ID: 37771948
Thanks - great work
0
 

Author Comment

by:philsivyer
ID: 37781087
Hello
Just one more question - when I wrap this in a package it throws an error ORACLE 22905
Something to do with Table function
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37781621
you'll need to declare a collection type to support the collection within pl/sql


create or replace type number_table as table of number;

then change

collect(level-1)

to

cast(collect(level-1) as number_table)


sql will handle it implicitly,
but within pl/sql, you need to give the collection a specific type to bind to.
0
 

Author Comment

by:philsivyer
ID: 37781668
thanks
0
 

Author Closing Comment

by:philsivyer
ID: 37781670
Great work - thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now