All Forums Database
eejimkos 73 posts Joined 01/12
11 Sep 2012
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,

 

 

 

Qaisar Kiani 337 posts Joined 11/05
12 Sep 2012

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...

ulrich 816 posts Joined 09/09
12 Sep 2012

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
)

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Qaisar Kiani 337 posts Joined 11/05
13 Sep 2012

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 :)

eejimkos 73 posts Joined 01/12
13 Sep 2012

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...

 

Qaisar Kiani 337 posts Joined 11/05
13 Sep 2012

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
)

 

You must sign in to leave a comment.