Get the next partition

Database

Get the next partition

Hello,

If we have one ,for example , small table of two columns,where is partitioned on the second column (on a date field by each month),

and we want to get the values from the next partition which has.

Meaning...

CREATE VOLATILE TABLE  vt_tst

(col1 INT

, col2 DATE

) PRIMARY INDEX (col1)

PARTITION BY( RANGE_N (col2 between '2012-01-01' and '2012-12-31' each interval  '1' month))

ON COMMIT PRESERVE ROWS

;

;

INSERT INTO  vt_tst VALUES (1, '2012-01-01');

INSERT INTO  vt_tst VALUES (1, '2012-01-11');

INSERT INTO  vt_tst VALUES (2, '2012-01-31');

INSERT INTO  vt_tst VALUES (3, '2012-02-05');

INSERT INTO  vt_tst VALUES (4, '2012-02-28');

INSERT INTO  vt_tst VALUES (5, '2012-03-01');

INSERT INTO vt_tst VALUES (8, '2012-08-04');

when we want to select  the date clm '2012-03-01' we want all the values from the parttion of the value 2012-08-04

Thank you very much,

5 REPLIES

Re: Get the next partition

I don't think you can do it in simple SQL because partitioning the data and assigning the partition numbers to the partitioed items is internal to Teradata.

However, I would be interested to see the solution in case any group member has done something similar...

N/A

Re: Get the next partition

isn't it not just the "next months with data" data in this case?

select *
from vt_tst
where col2/100 = (
select min(col2) / 100
from vt_tst
where col2 >= (add_months(cast('2012-03-01' as date),1)
- extract(day from add_months(cast('2012-03-01' as date),1))
) + 1
)

Re: Get the next partition

Sounds easy and simple as the data is parititioned on monthly basis, so the next available months data is the one which needs to be looked for!

Why didn't I think like this :)

Re: Get the next partition

Hello,

Thank you for your replies....

Yes, we wanted to know for a given date , the next available partition .

I was trying to find an alternatice solution(better for performance) ,

solution_1 :

SELECT a.*

FROM vt_tst a

WHERE a.PARTITION = (SELECT MIN(b.PARTITION)

                     FROM vt_tst b

                     WHERE b.PARTITION > (SELECT c.PARTITION

                                         FROM vt_tst c

                                         WHERE c.col2 = '2012-02-28'))

solution_2 :

SEL * FROM

(SEL T.*, PARTITION AS PART

FROM vt_tst T) TAB

INNER JOIN (SEL DIST.PART, MIN(DIST.PART) OVER (ORDER BY  (DIST.PART) ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS NEXT_P

FROM

(SEL DISTINCT PARTITION AS PART

FROM vt_tst T

) DIST) D

ON TAB.PART = D.PART

Thank you very much again. Any other ideas...

Re: Get the next partition

I think you can further simplify the solution suggested by ulrich to get the required results...

select *
from vt_tst
where col2/100 = (
select min(col2) / 100
from vt_tst
where col2/100 >= (add_months(cast('2012-01-30' as date),1))/100
)