mehul bhakta
asked on
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.ful l_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.ful l_date);
end loop;
end;
/
Thanks
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.ful
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-
where a.full_date = to_date('02-Jan-2016','DD-
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.ful
end loop;
end;
/
Thanks
You should use to_date('02-Jan-2016','DD- MON-YYYY')
This will work for sure
This will work for sure
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'
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'
ASKER
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-YYY Y'') = 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-M ON-YYYY'') = a.from_date
and a.product_status is not null';
commit;
I get the original error
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'',''
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-M
and a.product_status is not null';
commit;
I get the original error
Please, look at my last comment.
ASKER
@Helena Markova
I just tried this and I get an error:
ORA-00936: missing expression
ORA-06512: at line 11
I just tried this and I get an error:
ORA-00936: missing expression
ORA-06512: at line 11
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 ...)
'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 ...)
ASKER
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??
is your last method trying to create an empty table??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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
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
ASKER
a big thank you goes to johnsone for their help :)
to_date('02-01-2016','DD-M