troubleshooting Question

for loop using a date to create a table based on that date

Avatar of mehul bhakta
mehul bhakta asked on
SQL
11 Comments1 Solution57 ViewsLast Modified:
hello,

I'm trying to use a for loop to eventually cycle through a date range to create a table based on a given day I figured out how to do a loop to insert data but I really want to create a table directly from the source table eliminating the insert function, for I'm just interested in getting the for loop to pick up '02-JAN-2016'. however when I try to run my code I get this error:
ORA-00904; "X"."FULL_DATE": invalid identifier
ORA-06512: at line 11

I used DBMS_OUTPUT.PUT_LINE(x.full_date) to display how the date would look but it didn't help.

I'm running PL/SQL developer v11 running 11g and here is my code.

declare      
         
begin
 
for x in (select   a.full_date
          from     dim_t_date a
          --where    a.full_date between to_date('01-Jan-2016','DD-MM-YYYY') and to_date('04-Jan-2016','DD-MM-YYYY')
          where    a.full_date = to_date('02-Jan-2016','DD-MM-YYYY')
          order by a.full_date) loop

execute immediate
'create table snap_product_data compress parallel 16 pctfree 0 as
 select a.*
   from base a
  where a.base_type = ''S''
    and x.full_date = a.from_date
    and a.product_status is not null';
commit;

execute immediate
'create table load_product_data compress parallel 16 pctfree 0 as
    select a.from_date,
           nvl(to_date, ''31-DEC-9999'') to_date,
           a.premise_id,
           a.account_reference,
           case when a.key_type in (''1'',''15'') then ''E''
                when a.key_type in (''2'',''14'') then ''G''
                when a.key_type in (''67'') then ''F'' else null end product_code,
           trunc(a.start_date) start_date,
           trunc(a.end_date) end_date,
           product_status
      from (select a.*,
                   rank() over(partition by premise_id, account_reference order by case_statement asc) ranked
              from (select a.*,
                           case when a.to_date is null and a.end_date is null then 1
                                when a.to_date is not null and a.end_date is null then 2
                                when a.to_date is null and a.end_date is not null then 3
                                when a.to_date is not null and a.end_date is not null then 4 end case_statement
                      from snap_product_data1 a) a) a
     where ranked = 1';
 commit;

DBMS_OUTPUT.PUT_LINE(x.full_date);

 end loop;

end;
/

Thanks
ASKER CERTIFIED SOLUTION
johnsone
Senior Oracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros