Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL question (Oracle 9.2.0.8 on Windows)
PL/SQL question [message #378937] Fri, 02 January 2009 15:33 Go to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Hello, I need to create a stored procedure that will populate a business data table using an insert and select statement. I am trying to construct the select statement first and this is what I have so far. I need to return a full years worth of data to be inserted into the table. When I run this in SQL Developer it returns only 50 rows. In SQL*Plus it returns 1 row. Any ideas how I can make this do what I need it to do?


select 
   to_date('01-01-2008','MM-DD-YYYY')-1 + level as CALENDAR_DATE,
   to_char(to_date('20080101','YYYYMMDD')-1 + level, 'q') as CALENDAR_QUARTER,
   to_char(to_date('20080101','YYYYMMDD')-1 + level, 'ww') as CALENDAR_WEEK
from 
   dual
where
   (to_date('01 2008','MM YYYY')-1 + level) <= last_day(to_date('12 2008','MM YYYY'))
connect by level <= 366;



Once this is working I would like to put it in a stored procedure and if any of the rows already exist in the businessP_calendar table I want it to continue and insert the remaining rows.

Thank you for any assistance you can provide.

-- David
Re: PL/SQL question [message #378938 is a reply to message #378937] Fri, 02 January 2009 16:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
In older versions, like 9i, for row generation to work with connect by level, it was necessary to wrap the whole thing in an outer query. So, instead of something like:

select level from dual connect by level <= 366;

you would use something like:

select * from
(select level from dual connect by level <= 366);

To insert only rows that do not already exist, you could use a number of methods such as:

an outer join and is null or

not exists or

merge.


Re: PL/SQL question [message #378940 is a reply to message #378938] Fri, 02 January 2009 16:27 Go to previous messageGo to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Excellent! That works beautifully. Thank you kindly.

-- David
Re: PL/SQL question [message #379260 is a reply to message #378940] Mon, 05 January 2009 13:11 Go to previous messageGo to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
There is just one other tweak that I need for this to work perfectly. The below statement selects from dual and inserts into business_calendar. It filters out those rows in business calendar that match on CALENDAR_DATE, CALENDAR_QUATER and CALENDAR_WEEK. The issue this poses is there could be an attempt to insert a row with a duplicate CALENDAR_DATE value into business_calendar. This actually fails because the CALENDAR_DATE column has a primary key constraint on it. I need to somehow filter on JUST CALENDAR_DATE to prevent this. Here is the statement that I am referring to:



insert into business_calendar
(
   CALENDAR_DATE,
   CALENDAR_QUARTER,
   CALENDAR_WEEK
)   
select * from
(select   
   to_date('01-01-2009','MM-DD-YYYY')-1 + level as CALENDAR_DATE,
   to_char(to_date('20090101','YYYYMMDD')-1 + level, 'q') as CALENDAR_QUARTER,
   (case 
    when 
       to_char(to_date('20090101','YYYYMMDD')-1 + level, 'iw') >= '52' and
       to_char(to_date('20090101','YYYYMMDD')-1 + level, 'q') = '1' 
          then '1'
    when
       to_char(to_date('20090101','YYYYMMDD')-1 + level, 'MM') = '12' and
       ltrim(to_char(to_date('20090101','YYYYMMDD')-1 + level, 'iw'), '0') = '1' 
          then to_char(to_number(to_char(to_date('20090101','YYYYMMDD')-1 + level, 'iw') + '52'))     
    else   
       ltrim(to_char(to_date('20090101','YYYYMMDD')-1 + level, 'iw'), '0')
    end) as CALENDAR_WEEK
from 
   dual
where
   (to_date('01 2009','MM YYYY')-1 + level) <= last_day(to_date('12 2019','MM YYYY'))
connect by level <= 4390
minus
select
   CALENDAR_DATE,
   TO_CHAR(CALENDAR_QUARTER),
   TO_CHAR(CALENDAR_WEEK)
from
   business_calendar
);




-- David
Re: PL/SQL question [message #379265 is a reply to message #379260] Mon, 05 January 2009 14:04 Go to previous messageGo to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Okay, sorry but it appears to be working as expected with the following query.



insert into business_calendar
(
   CALENDAR_DATE,
   CALENDAR_QUARTER,
   CALENDAR_WEEK
)   
select * from
(select   
   to_date('01-01-2009','MM-DD-YYYY')-1 + level as CALENDAR_DATE,
   to_char(to_date('20090101','YYYYMMDD')-1 + level, 'q') as CALENDAR_QUARTER,
   (case 
    when 
       to_char(to_date('20090101','YYYYMMDD')-1 + level, 'iw') >= '52' and
       to_char(to_date('20090101','YYYYMMDD')-1 + level, 'q') = '1' 
          then '1'
    when
       to_char(to_date('20090101','YYYYMMDD')-1 + level, 'MM') = '12' and
       ltrim(to_char(to_date('20090101','YYYYMMDD')-1 + level, 'iw'), '0') = '1' 
          then to_char(to_number(to_char(to_date('20090101','YYYYMMDD')-1 + level, 'iw') + '52'))     
    else   
       ltrim(to_char(to_date('20090101','YYYYMMDD')-1 + level, 'iw'), '0')
    end) as CALENDAR_WEEK
from 
   dual
where
   (to_date('01 2009','MM YYYY')-1 + level) <= last_day(to_date('12 2019','MM YYYY'))
connect by level <= 4390) a
where a.calendar_date not in (select b.calendar_date from business_calendar b)
;




-- David
Re: PL/SQL question [message #379274 is a reply to message #379265] Mon, 05 January 2009 16:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
This could be simplified a little and usually an outer join and is null is more efficient that not in.

insert into business_calendar
(
   CALENDAR_DATE,
   CALENDAR_QUARTER,
   CALENDAR_WEEK
)   
SELECT new_date,
       new_quarter,
       CASE 
         WHEN new_week = '52' AND new_quarter = '1' THEN  '1'
         WHEN new_week =  '1' AND new_quarter = '4' THEN '53'
         ELSE new_week
       END AS new_week
FROM   (SELECT  start_date + LEVEL                 AS new_date,
                TO_CHAR (start_date + LEVEL, 'Q')  AS new_quarter,
                TO_CHAR (start_date + LEVEL, 'IW') AS new_week 
        FROM    (SELECT TO_DATE ('01-01-2009', 'MM-DD-YYYY') - 1 AS start_date 
                 FROM   DUAL)
        CONNECT BY LEVEL <= ADD_MONTHS (start_date, 120) - start_date) new_calendar,
       business_calendar
WHERE  new_calendar.NEW_date = business_calendar.calendar_date (+)
AND    business_calendar.calendar_date IS NULL
/

Re: PL/SQL question [message #379424 is a reply to message #379274] Tue, 06 January 2009 07:04 Go to previous message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Barbara, there are some instances where there is actually a 53 week year and in order to accomodate that I had to modify the the script as follows. Thank you for your input. It was very helpful.


insert into business_calendar
(
   CALENDAR_DATE,
   CALENDAR_QUARTER,
   CALENDAR_WEEK
)   
SELECT new_date,
       new_quarter,
       CASE 
         WHEN new_week = '52' AND new_quarter = '1' THEN  '01'
         WHEN new_week = '53' AND new_quarter = '1' THEN  '01'
         WHEN new_week =  '01' AND new_quarter = '4' THEN to_char(new_week + '52')
         ELSE new_week
       END AS new_week
FROM   (SELECT  start_date + LEVEL                 AS new_date,
                TO_CHAR (start_date + LEVEL, 'Q')  AS new_quarter,
                TO_CHAR (start_date + LEVEL, 'IW') AS new_week 
        FROM    (SELECT TO_DATE ('01-01-2009', 'MM-DD-YYYY') - 1 AS start_date 
                 FROM   DUAL)
        CONNECT BY LEVEL <= ADD_MONTHS (start_date, 120) - start_date) new_calendar,
       business_calendar
WHERE  new_calendar.NEW_date = business_calendar.calendar_date (+)
AND    business_calendar.calendar_date IS NULL
/






-- David

[Updated on: Tue, 06 January 2009 07:40]

Report message to a moderator

Previous Topic: Can One Anyone help regarding this simple issue
Next Topic: How to write a script to call a procedure after checking the highest ID..??
Goto Forum:
  


Current Time: Sat Dec 03 01:06:50 CST 2016

Total time taken to generate the page: 0.09019 seconds