SQL Error [2788] [0A000]: [Vertica]VJDBC ERROR: operator <> is not supported

gg1234gg1234 Vertica Customer
edited August 2020 in General Discussion

I'm getting an error
"SQL Error [2788] [0A000]: [Vertica]VJDBC ERROR: Correlated subquery in expression with operator <> is not supported" When I try to execute this statement on Vertenter code hereica, but I can used this stetement on the SQL Server.

SELECT test.ID ,test.Nameenter code here
FROM test inner join test2 f on f.ID = test.ID
WHERE (test.Status IN ('PASS', 'COMPLETED'))
AND ( test.PLANID = 0 or test.ProjectID <> (select a.ProjectID from test a where test.PLANID = a.ID ))

Could anyone suggest me the correct statement?

Thank you

Answers

  • gg1234gg1234 Vertica Customer

    Dear LenoyJ
    I tried to use "NOT IN", but it return the same error about "NOT IN" is not supported.

  • LenoyJLenoyJ - Select Field - Employee

    Whoops, sorry. Should've paid attention to what your query was trying to do.
    Correlated sub-queries (i.e queries that requires data from the outer query) are not supported with NOT IN.
    See: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AnalyzingData/Queries/Subqueries/SubqueryRestrictions.htm

  • moshegmosheg Vertica Employee Administrator
    edited August 2020

    Consider this example:

    CREATE TABLE test1 (ID INT, Nameenter VARCHAR(20), Status VARCHAR(20), PLANID INT, ProjectID INT);
    CREATE TABLE test2 (ID INT, Nameenter VARCHAR(20), Status VARCHAR(20), PLANID INT, ProjectID INT);
    COPY test1 FROM STDIN DELIMITER ',' NULL '' ABORT ON ERROR;
    1,name1,unknown,1,1
    2,name2,PASS,2,2
    3,name3,COMPLETED,3,3
    4,name4,FAIL,4,4
    \.
    
    COPY test2 FROM STDIN DELIMITER ',' NULL '' ABORT ON ERROR;
    1,name1,unknown,1,1
    2,name2,PASS,2,20
    3,name3,COMPLETED,3,3
    4,name4,FAIL,4,40
    \.
    
    SELECT * FROM test1 ORDER BY 1;
     ID | Nameenter |  Status   | PLANID | ProjectID
    ----+-----------+-----------+--------+-----------
      1 | name1     | unknown           |      1 |         1
      2 | name2     | PASS                 |      2 |         2
      3 | name3     | COMPLETED |      3 |         3
      4 | name4     | FAIL                   |      4 |         4
    (4 rows)
    
    SELECT * FROM test2 ORDER BY 1;
     ID | Nameenter |  Status   | PLANID | ProjectID
    ----+-----------+-----------+--------+-----------
      1 | name1     | unknown           |      1 |         1
      2 | name2     | PASS                  |      2 |        20
      3 | name3     | COMPLETED  |      3 |         3
      4 | name4     | FAIL                    |      4 |        40
    (4 rows)
    
    SELECT test1.*
    FROM   test1
            LEFT JOIN test2
                ON test1.ProjectID IN (test2.ProjectID)
    WHERE   test2.ProjectID IS NULL AND test1.Status IN ('PASS', 'COMPLETED');
    
     ID | Nameenter | Status | PLANID | ProjectID
    ----+-----------+--------+--------+-----------
      2  | name2       | PASS   |       2      |         2
    (1 row)
    

Leave a Comment

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