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

gg1234

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

LenoyJ

NOT IN?
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Predicates/IN-predicate.htm
BTW, you're missing parenthesis.

gg1234

@LenoyJ said:
NOT IN?
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Predicates/IN-predicate.htm
BTW, you're missing parenthesis.

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

LenoyJ

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

mosheg

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)