We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


ERROR 4286: Operator does not exist: date = int — Vertica Forum

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