0

For ETL operations we use SAS DI studio and then finally the tables are loaded in Teradata. DDL is dynamically generated in SAS DI Studio for the tables created. But when we want to customize the DDL to include partitions it throws an error. Can anyone suggest a workaround? Note: We cannot create the DDL in Teradata first and then register the table in SAS DI Studio to be using it.

In the table properties->Options->advanced and write custom SQL in create table option it works fine for UNIQUE PRIMARY INDEX(NOTI_DT) But when we try 

UNIQUE PRIMARY INDEX(NOTI_DT)
PARTITION BY RANGE_N(NOTI__DT BETWEEN DATE'1950-01-01'AND DATE'2022-12-31' EACH INTERVAL '1' MONTH)

it throws an error: image 1

Abasesha
  • 41
  • 7
  • Which error? Btw, partitioning a table with just a few thousand rows is usually quite useless – dnoeth May 29 '19 at 10:28
  • As of now we have a filter applied to restrict the data just to realize if the partition works. Else we have 3 million plus rows. The error is in the step where the table gets created . The error reads as follows: ERROR 22-7: Invalid option name 1950. - ERROR: Teradata execute: Object 'HC_PRD_D_RDDL_SDTB_0_1_0_0_0_0_0_0.AVG_SITE_VISIT' does not exist. – Abasesha May 29 '19 at 10:51
  • https://i.stack.imgur.com/V9q0o.png Its the reference for the task created with the error it displays – Abasesha May 29 '19 at 10:56
  • There's a typo in the PARTITION BY: `NOTI__DT` vs. `NOTI_DT` – dnoeth May 29 '19 at 11:49
  • @dnoeth sorry for the typo in there . But in the job editor it is mentioned correctly and the error is in accordance to that – Abasesha May 29 '19 at 17:48

1 Answers1

1

The "invalid option name" error appears to be due to having quotes within the option string. Try doubling the quotes:

UNIQUE PRIMARY INDEX(NOTI_ASSIGN_DT)
PARTITION BY RANGE_N(NOTI_ASSIGN_DT BETWEEN DATE''1950-01-01'' AND DATE''2022-12-31'' EACH INTERVAL ''1'' MONTH)
Fred
  • 1,916
  • 1
  • 8
  • 16