02 Feb 2015
Hi Samir,
150130 is 1915-01-30, which definitely doesn't fit in any partition :-)
#1 and #2 are exactly the same, but #1 is preferred because it's more readable.
But what's the datatype of Order_TS?
If it's a timestamp you might use it directly without cast...
Dieter
03 Feb 2015
Hi Diether,
I experimented yesterday and found that it was an issue as the partition was broken in between ( i had figured it out, but was just confused). In your reply, you mentioned "150130 is 1915-01-30". Is this 2015 or 1915 ?
--Samir
03 Feb 2015
Hi Samir,
Teradata stores DATEs using following formula: (year -1900) * 10000 + month * 100 + day
select cast(150130 as date), cast(1150130 as date);
150130 1150130
---------- ----------
1915-01-30 2015-01-30
Dieter
You must sign in to leave a comment.
Hello All,
I have a question regarding PPI in TD 14.10.
1. PRIMARY INDEX (customer_number)
PARTITION BY range_n (
cast(Order_TS as Date ) BETWEEN date '2014-01-31' AND date '2015-01-31'
EACH interval '1' month);
2. PRIMARY INDEX (customer_number)
PARTITION BY range_n (
cast(Order_TS as date) BETWEEN 1140131 AND 1150131
EACH interval '1' month);
I came up with a DDL with the construct as in point - 2 above. I have few questions here.
1. Is there any advantage to define a partition as in point2 above as compared to point1 defn or do they mean the same ?
2. When i try to insert a data for order_date as 150130, the insert fails saying tht invalid partition error. But, should this data not get inserted as this is a 1 month partition table ? or does it mean that only data having order_date as 150131 will be accepted ? As per my understanding, if you have a month partition, data for that defined range can be inserted and they will be grouped together as a partition in an AMP. Error should occur, if you have not defined a partition and you have not mentioned no range, unknown etc. say if the data comes for month of Jan'15, it should be inserted and kept as one partition and if one record is inserted for 150203, then it should fail.
--Samir