• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

forwarding out dates

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
philsivyer
Asked:
philsivyer
  • 9
  • 8
1 Solution
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
philsivyerAuthor Commented:
For this to work does 'FROM mytable' have to be a table or can it be a query
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
sdstuberCommented:
mytable can be a query, just put it in ()
0
 
philsivyerAuthor Commented:
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
 
sdstuberCommented:
I'd have to see the code
0
 
philsivyerAuthor Commented:
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
 
sdstuberCommented:
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
 
philsivyerAuthor Commented:
I have never used this logic before - do you mind explaining the logic please.
0
 
sdstuberCommented:
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
 
philsivyerAuthor Commented:
Last question
why  COLLECT(LEVEL - 1)
0
 
sdstuberCommented:
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
 
philsivyerAuthor Commented:
Thanks - great work
0
 
philsivyerAuthor Commented:
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
 
sdstuberCommented:
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
 
philsivyerAuthor Commented:
thanks
0
 
philsivyerAuthor Commented:
Great work - thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now