Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

ERROR 4286: Operator does not exist: date = int

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


Comments

  • 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
  • Apply to_char fun to the integer field
  • thanks a lot guys!!!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.