Solved

forwarding out dates

Posted on 2012-03-26
17
292 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 74

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 74

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 74

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 74

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 74

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
 

Author Comment

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

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 74

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 74

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

730 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