SQL Error [2788] [0A000]: [Vertica]VJDBC ERROR: operator <> is not supported
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
Tagged:
0
Answers
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.
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
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)