Solved

forwarding out dates

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with Oracle syntax 4 56
FRM-40735:KEY-COMMIT trigger raised unhandled exception ORA-01422 7 56
Clone Oracle 12c Database 5 43
Oracle sql query 7 48
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

920 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

15 Experts available now in Live!

Get 1:1 Help Now