How to make time dimension in netezza?

Question

I found a example sql to build time dimension, I get from this link https://gist.github.com/johngrimes/408559

everything is perfect in mysql,

but, when I tried in netezza, the code not work correctly, some error is appear, such as "expecting USING' or')'' or `',''",etc

I have tried modify

CREATE TABLE numbers_small (number INT);
INSERT INTO numbers_small VALUES (0);
INSERT INTO numbers_small VALUES (1);
INSERT INTO numbers_small VALUES (2);
INSERT INTO numbers_small VALUES (3);
INSERT INTO numbers_small VALUES (4);
INSERT INTO numbers_small VALUES (5);
INSERT INTO numbers_small VALUES (6);
INSERT INTO numbers_small VALUES (7);
INSERT INTO numbers_small VALUES (8);
INSERT INTO numbers_small VALUES (9);

CREATE TABLE numbers (number BIGINT);
INSERT INTO numbers
SELECT thousands.number * 1000 + hundreds.number * 100 + tens.number * 10 + ones.number
  FROM numbers_small thousands, numbers_small hundreds, numbers_small tens, numbers_small ones
LIMIT 1000000;



CREATE TABLE D_TIME_DAILY_TES (
  TIME_ID          BIGINT PRIMARY KEY, 
  TANGGAL             DATE NOT NULL,
  TAHUN             INT NOT NULL,
  BULAN            CHAR(10) NOT NULL,
  HARI      CHAR(10) NOT NULL,
  LAST_UPDATE timestamp NOT NULL,
  constraint D_TIME_DAILY_TES UNIQUE KEY (TANGGAL)
);


INSERT INTO D_TIME_DAILY_TES (TIME_ID, TANGGAL, LAST_UPDATE)
SELECT number, DATE_ADD( '2010-01-01', INTERVAL number DAY ), CURRENT_TIMESTAMP
  FROM numbers
  WHERE DATE_ADD( '2010-01-01', INTERVAL number DAY ) BETWEEN '2014-01-01' AND '2014-12-31'
  ORDER BY number;


UPDATE D_TIME_DAILY_TES SET
  HARI = DATE_FORMAT( TANGGAL, "%W" ),
  BULAN =       DATE_FORMAT( TANGGAL, "%M"),
  TAHUN =        DATE_FORMAT( TANGGAL, "%Y" ),
  LAST_UPDATE = CURRENT_TIMESTAMP;

but it's still not work :(

how can I fix it?

score 0 · Answer 1 · answered Oct 22 '14 at 23:31

There are two problems I see with your code, from a syntax perspective.

The first is in the creation of the table D_TIME_DAILY_TES.

Remove the unique key constraint on TANGGAL and it will run fine.

CREATE TABLE D_TIME_DAILY_TES
   (
      TIME_ID BIGINT PRIMARY KEY,
      TANGGAL DATE NOT NULL,
      TAHUN   INT NOT NULL,
      BULAN   CHAR(10) NOT NULL,
      HARI    CHAR(10) NOT NULL,
      LAST_UPDATE TIMESTAMP NOT NULL
   );

The second is in the INSERT to populate the same table. DATE_ADD is not required to add integers to a date, and indeed doesn't exist. You can use this form instead. Note that this insert will actually fail because we are only inserting three columns, while all of the columns you have definied for D_TIME_DAILY_TES are NOT NULL, so you'll need to fix that as well .

  INSERT INTO D_TIME_DAILY_TES
     (
        TIME_ID,
        TANGGAL,
        LAST_UPDATE
     )
  SELECT           NUMBER,
     '2010-01-01'::DATE + NUMBER,
     CURRENT_TIMESTAMP
  FROM numbers
  WHERE '2010-01-01'::DATE + NUMBER BETWEEN '2014-01-01'::DATE AND '2014-12-31'::DATE
  ORDER BY NUMBER;

How to make time dimension in netezza?

1 Answers1