Solved

forwarding out dates

Posted on 2012-03-26
17
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

717 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