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

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
mehul bhaktaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Markováprogrammer-analystCommented:
I would try
to_date('02-01-2016','DD-MM-YYYY') instead of to_date('02-Jan-2016','DD-MM-YYYY')
0
Ganesh GuruduSenior ConsultantCommented:
You should use to_date('02-Jan-2016','DD-MON-YYYY')
This will work for sure
0
Helena Markováprogrammer-analystCommented:
I think that main problem is here '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'
It ought to be something like this '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'
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

mehul bhaktaAuthor Commented:
hi all,

if I write
execute immediate
'create table snap_product_data1 compress parallel 16 nologging pctfree 0 as
 select a.*
   from base a
  where a.base_type = ''S''
    and to_date(''02-Jan-2016'',''DD-MON-YYYY'') = a.from_date
    and a.product_status is not null';
commit;

it works but its not using the for loop date

then if I try this:
execute immediate
'create table snap_product_data1 compress parallel 16 nologging pctfree 0 as
 select a.*
   from base a
  where a.base_type = ''S''
    and to_date(x.full_date,''DD-MON-YYYY'') = a.from_date
    and a.product_status is not null';
commit;

I get the original error
0
Helena Markováprogrammer-analystCommented:
Please, look at my last comment.
0
mehul bhaktaAuthor Commented:
@Helena Markova

I just tried this and I get an error:
ORA-00936: missing expression
ORA-06512: at line 11
0
Helena Markováprogrammer-analystCommented:
I would try this:
 'create table snap_product_data 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 compress parallel 16 pctfree 0'

Base is a table ? (... from base a ...)
0
mehul bhaktaAuthor Commented:
yes "base" is a table, I want to extract all the data out of the "base" table for a day using a for loop.

is your last method trying to create an empty table??
0
johnsoneSenior Oracle DBACommented:
Using dates, you should never rely in implicit conversion.  Always use explicit conversion.
execute immediate
'create table snap_product_data compress parallel 16 pctfree 0 as
 select a.*
   from base a
  where a.base_type = ''S''
    and to_date(''' || to_char(x.full_date, 'mmddyyyy') || ''',''mmddyyyy'') = a.from_date
    and a.product_status is not null';

Open in new window

Also, there is no need for a commit after a CREATE TABLE statement.  A CREATE TABLE has an implied commit.  The two commit statements in the sample code can be removed.

Ideally, you would want to use bind variables, as it would make this much easier, but those are not allowed in DDL statements.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mehul bhaktaAuthor Commented:
@johnsone, wow that worked and thanks for the advice too.

I saw on another site that somebody did this:
Where to_char(X.Datetime, ''yyyy'') = '''|| idx||'''';

but wasn't sure why I'd need to to_char a date when it's in a format that should be able to link back to the source table.

THANK YOU ALL

Also Merry Christmas
0
mehul bhaktaAuthor Commented:
a big thank you goes to johnsone for their help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.