I am trying to build a complex query, but I am not sure if I am structuring it right, as I am getting the error: ORA-00933: SQL command not properly ended. The error is happening at line 2. Please, could you help me with this. Thank you!
SELECT CNP,ValoareTotala
FROM Vanzari AS V
RIGHT OUTER JOIN Functionar AS F
ON V.CNP= F.CNP
RIGHT OUTER JOIN Achizitii AS A
ON F.CNP= A.CNP
WHERE CNP in (SELECT CNP
FROM Achizitii
WHERE CNP =
(SELECT CNP
FROM Functionar
WHERE Nume='Alex Viseu'))
GROUP BY CNP, ValoareTotala
HAVING ValoareTotala>10
ORDER BY ValoareTotala DESC;
>Solution :
You can’t use AS for a table alias in Oracle. You can for column aliases, where it is optional, but it is not allowed for table aliases. You can see that in the syntax diagram – that shows t_alias without an optional AS keyword.
So remove that from all three references:
SELECT CNP,ValoareTotala
FROM Vanzari V
RIGHT OUTER JOIN Functionar F
ON V.CNP= F.CNP
RIGHT OUTER JOIN Achizitii A
ON F.CNP= A.CNP
WHERE CNP in (SELECT CNP
FROM Achizitii
WHERE CNP =
(SELECT CNP
FROM Functionar
WHERE Nume='Alex Viseu'))
GROUP BY CNP, ValoareTotala
HAVING ValoareTotala>10
ORDER BY ValoareTotala DESC;
But you will also need to make it clear which table each column comes from, particularly where the same column name appears in more than one table – and as you have the join conditions ON V.CNP= F.CNP and ON F.CNP= A.CNP the column CNP exists in all three tables; so the other references to CNP will have to specify which one you mean.
It’s also not really clear why you are using a subquery, particularly one that refers to the same tables again.