Home » SQL & PL/SQL » SQL & PL/SQL » plsql table partitions
plsql table partitions [message #629864] Tue, 16 December 2014 15:34 Go to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo
I would like to have some guidance regarding creating table with different partitions.
well, i have one Table, T1
with fields AID, PERIOD, PRICE
and in PERIOD is data-type number in the format (YYYYMM)and has different date/period; like (201206)
and the price field has same value in different record.

now i would like to create another table T2 with different partitions like
AID, FROM_DATE, TO_DATE, PRICE

so my question is: how can i create such table using partitions based on PERIOD of T1 Table,
which will shows just the limited rows according to the defined partitions? and how can i get the value from T1.PERIOD field into T2.FROM_DATE and T2.TO_DATE?

can anyone please help me?
thankx
Smile
Re: plsql table partitions [message #629866 is a reply to message #629864] Tue, 16 December 2014 15:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
how can i get the value from T1.PERIOD field into T2.FROM_DATE and T2.TO_DATE?


How could we know this is your rules not ours.
How do YOU define FROM_DATE and TO_DATE from PERIOD?

And what does "partition" mean for you?

Re: plsql table partitions [message #629868 is a reply to message #629866] Tue, 16 December 2014 15:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>now i would like to create another table T2 with different partitions like
>AID, FROM_DATE, TO_DATE, PRICE


what datatypes are FROM_DATE & TO_DATE?
Re: plsql table partitions [message #629869 is a reply to message #629866] Tue, 16 December 2014 15:51 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
ya sorry.. its also hard for me to define.. i am just trying to do exercise to improve my knowledge.. that's why sorry..

and i would like to get the value of PERIOD in T1 table as FROM_DATE and TO_DATE in T2, when its possible to get so, as i wish..

Re: plsql table partitions [message #629870 is a reply to message #629869] Tue, 16 December 2014 15:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> plsql table partitions

BTW - You can not partition PL/SQL tables
Re: plsql table partitions [message #629872 is a reply to message #629870] Tue, 16 December 2014 15:58 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Are you talking about partitioning a table in a database? If yes, the Oracle documentation is pretty clear on how and when you should partition a table.

If you are talking about partitioning a PL/SQL table (associative array, collection, etc) - is this not possible for one. For two, why would you want to partition something that resides in memory?
Re: plsql table partitions [message #629899 is a reply to message #629872] Wed, 17 December 2014 02:54 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
hallo @BlackSwan

ya These two coulumn will be data-type will be also the number.

PS: the two fields are FROM_WEEK, and TO_WEEK not the FROM_DATE and TO_DATE
Re: plsql table partitions [message #629900 is a reply to message #629899] Wed, 17 December 2014 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The names don't really matter what is important is their definition.
So what are these definition?

Re: plsql table partitions [message #629917 is a reply to message #629864] Wed, 17 December 2014 08:16 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
Your question looks either very poorly structured or it is highly advanced. but certainly not in my understanding bandwidth. Can you please elaborate with some test cases. Also provide exact requirement with proper description.
Re: plsql table partitions [message #629968 is a reply to message #629917] Thu, 18 December 2014 03:53 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo

Sorry for my previous post, so here i have tried to find someway to solve my Problem, but still i am not satisfied with it
my Problem is: the Output Shows two rows of same record of same item number and having same rate, (as i have made bold in my Output)
but i would like to have my Output just one row of record if they are same (including first (from_date) to last(to_date).
may be i have bit ellaborate my Problem nao Smile

can anyone please help me on this regards?
thanking you


select distinct PID, FROM_DATE, TO_DATE, RATE
from
(

    select s.*, 
    case when before_date = 0 then now else 
        lag (now, 1, 0) over (partition by rate order by pid, rate, now) end FROM_DATE, 
    case when after = 1 then now else 
        lead (now, 1, 0) over (partition by rate order by pid, rate, now) end TO_DATE
from (
        select pid, rate, 
        case when 
        lag(period,1,0) over (partition by rate order by pid, rate, period) = period - 1 then 
        lag(period,1,0) over (partition by rate order by pid, rate, period) else 0 end before,
period now, 
        case when 
        lead(period,1,0) over (partition by rate order by pid, rate, period) = period + 1 then 
        lead(period,1,0) over (partition by rate order by pid, rate, period) else 1 end after
from pruductsale
    
where pid = 999
order by pid, rate, period) s
where ((before = 0) and (after = 1)) 
or ((before = 0  and after != 1 )or (after = 1 and before != 0))
order by pid, now;



---------------
Output:

PID FROM_DATE TO_DATE RATE
----- ------------- --------- -------
999 200105 200130 55
999 200131 200131 72
999 200132 200140 55
999 200141 200152 72
999 200201 200225 72
Re: plsql table partitions [message #629969 is a reply to message #629968] Thu, 18 December 2014 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So now it is again FROM_DATE and TO_DATE?

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


Re: plsql table partitions [message #629983 is a reply to message #629969] Thu, 18 December 2014 07:56 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
hallo Michael

thankx for your reply.. Smile
wel
here is my test case.

Thank you for your guidance Smile


create table TEST_S (
    PID        Number,
    From_we    Number,
    To_we      Number,
    Rate       Number
 );   
 
 
 insert into TEST_S (pid, from_we, to_we, rate) values (10, 200101, 200111, 50);
 insert into TEST_S (pid, from_we, to_we, rate) values (10, 200102, 200102, 55);
 insert into TEST_S (pid, from_we, to_we, rate) values (10, 200103, 200120, 50);
 insert into TEST_S (pid, from_we, to_we, rate) values (10, 200121, 200122, 39);
 insert into TEST_S (pid, from_we, to_we, rate) values (10, 200123, 200152, 199);
 insert into TEST_S (pid, from_we, to_we, rate) values (10, 200201, 200212, 199);
Re: plsql table partitions [message #629984 is a reply to message #629983] Thu, 18 December 2014 08:02 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So what's the relationship between test_s and pruductsale?
Re: plsql table partitions [message #629985 is a reply to message #629984] Thu, 18 December 2014 08:04 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
ahh sorry.. it was my fault.. please just choose the one table Name,, either TEST_S or PRODUCTSALE

sorry.. :/
Re: plsql table partitions [message #629989 is a reply to message #629985] Thu, 18 December 2014 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you already have the final result?
Or what should be the result for your TEST_S table?

Re: plsql table partitions [message #629991 is a reply to message #629989] Thu, 18 December 2014 08:37 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
NO, i don't have
my Problem is,
as i am still getting two rows of records, that they have same item number, and rate ( but the time interval is different)
which i want to optimize this row into one,
in my case is
item no. 10 and rate 199 the last two records.
thank you in advance.

Output of TEST_S:

PID,FROM_WE,TO_WE,RATE
10,200101,200111,50
10,200102,200102,55
10,200103,200120,50
10,200121,200122,39
10,200123,200152,199
10,200201,200212,199
Re: plsql table partitions [message #629994 is a reply to message #629991] Thu, 18 December 2014 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, your problem is that at each post you modify what you have and want.
So restart from the beginning and specify:
- What is the input table, post a test case for it
- What are the requirement WITH WORDS, no reference to the current data
- What is the output result for the data you gave in the test case.

AND:

Michel Cadot wrote on Thu, 18 December 2014 11:02

...
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.


Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: plsql table partitions [message #630002 is a reply to message #629985] Thu, 18 December 2014 09:22 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
palpali wrote on Thu, 18 December 2014 14:04
ahh sorry.. it was my fault.. please just choose the one table Name,, either TEST_S or PRODUCTSALE

sorry.. :/


They don't have the same columns. So they aren't interchangeable.
Michel keeps asking for a detailed explanation. If you don't supply one you aren't going to get the help you need and you are just wasting everyone's time, including your own.

Previous Topic: Issue with DBMS_LOB.SubStr
Next Topic: utl_request ORA-29049 error when request salesforce.com
Goto Forum:
  


Current Time: Thu Mar 28 21:32:56 CDT 2024