ERROR 4286: Operator does not exist: date = int

Nicola_Cambara

I get this error, what's wrong?
this is the query:

dbvertica=> select Sum(dbvertica.LINEORDER.l_extendedprice*dbvertica.LINEORDER.l_discount) as revenue from 

dbvertica.LINEORDER, dbvertica.DATE where dbvertica.LINEORDER.l_orderdate = <BR />dbvertica.DATE.d_datekey and dbvertica.DATE.d_year = '1993' and <BR />dbvertica.LINEORDER.l_discount between 1 and 3 and dbvertica.LINEORDER.l_quantity < 25;

and those are the tables:

CREATE TABLE dbvertica.PART  ( P_PARTKEY INTEGER NOT NULL, P_NAME 
VARCHAR(22) NOT NULL, P_MFGR CHAR(6) NOT NULL, P_CATEGORY CHAR(7) NOT 
NULL,P_BRAND CHAR(9) NOT NULL, P_COLOR VARCHAR(11) NOT NULL, P_TYPE   VARCHAR(25) NOT NULL, P_SIZE INTEGER NOT NULL, P_CONTAINER CHAR(10) NOT   NULL);    CREATE TABLE dbvertica.SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL, S_NAME   CHAR(25) NOT NULL, S_ADDRESS VARCHAR(25) NOT NULL, S_CITY CHAR(10) NOT   NULL, S_NATION VARCHAR(15) NOT NULL, S_REGION VARCHAR(12) NOT NULL,   S_PHONE CHAR(15) NOT NULL);    CREATE TABLE dbvertica.LINEORDER ( L_ORDERKEY INTEGER NOT NULL,   L_LINENUMBER INTEGER NOT NULL, L_CUSTKEY INTEGER NOT NULL, L_PARTKEY   INTEGER NOT NULL, L_SUPPKEY INTEGER NOT NULL, L_ORDERDATE DATE NOT NULL,   L_ORDERPRIORITY  CHAR(15) NOT NULL, L_SHIPPRIORITY   INTEGER NOT   NULL,L_QUANTITY    DECIMAL(15,2) NOT NULL, L_EXTENDEDPRICE    DECIMAL(15,2) NOT NULL, L_ORDTOTALPRICE     DECIMAL(15,2) NOT NULL,   L_DISCOUNT    DECIMAL(15,2) NOT NULL, L_REVENUE    DECIMAL(15,2) NOT   NULL, L_SUPPLYCOST   DECIMAL(15,2)NOT NULL, L_TAX         DECIMAL(15,2)   NOT NULL,L_COMMITDATE  DATE NOT NULL,L_SHIPMODE     CHAR(10) NOT NULL);    CREATE TABLE dbvertica.CUSTOMER ( C_CUSTKEY INTEGER NOT NULL,C_NAME   VARCHAR(25) NOT NULL, C_ADDRESS VARCHAR(25) NOT NULL,C_CITY CHAR(10) NOT   NULL, C_NATION CHAR(15) NOT NULL,C_REGION CHAR(12) NOT NULL,C_PHONE   CHAR(15) NOT NULL,C_MKTSEGMENT  CHAR(10) NOT NULL);    CREATE TABLE dbvertica.DATE ( D_DATEKEY INTEGER NOT NULL, D_DATE   CHAR(18) NOT NULL, D_DAYOFWEEK CHAR(8) NOT NULL, D_MONTH CHAR(9) NOT   NULL, D_YEAR INT NOT NULL, D_YEARMONTHNUM INT NOT NULL, D_YEARMONTH   CHAR(7) NOT NULL, D_DAYNUMINWEEK INT NOT NULL, D_DAYNUMINMONTH INT NOT   NULL, D_DAYNUMINYEAR INT NOT NULL, D_MONTHNUMINYEAR INT, D_WEEKNUMINYEAR   INT, D_SELLINGSEASON VARCHAR(12) NOT NULL, D_LASTDAYINWEEKFL BOOLEAN   NOT NULL, D_LASTDAYINMONTHFL BOOLEAN NOT NULL, D_HOLIDAYFL BOOLEAN NOT   NULL, D_WEEKDAYFL BOOLEAN NOT NULL);

Shobhit_Garg

Hi,

I think the problem could be with this join
dbvertica.LINEORDER.l_orderdate = <br>dbvertica.DATE.d_datekey

The column dbvertica.LINEORDER.l_orderdate has datatype  DATE and dbvertica.DATE.d_datekey has dataype INTEGER, so there is dataype miss match .
Once you correct this you will not face this problem.

Regards,
Shobhit

Massimo_Loporch

Apply to_char fun to the integer field

Nicola_Cambara

thanks a lot guys!!!