Contents
Which statement Cannot be used with WHERE clause?
We cannot use the HAVING clause without SELECT statement whereas the WHERE clause can be used with SELECT, UPDATE, DELETE, etc. WE can use aggregate functions like sum, min, max, avg, etc with the HAVING clause but they can never be used with WHERE clause.
Is it possible to use both a WHERE clause and a HAVING clause in a select statement?
A query can contain both a WHERE clause and a HAVING clause. In that case: Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set.
Can a select be used without an INTO clause?
In the “body” of an Oracle procedure, you just can’t use a SELECT without an INTO clause. (In fact you can have, but in a cursor). Caution : if no result is found, this will raise a NO_DATA_FOUND exception. Your real problem is NOT about using variable (you got that right), but about returning cursors from PL/SQL blocks.
Can you pass a parameter to the in clause?
You can’t pass a parameter to the IN clause in that fashion. There a number of ways you can solve this problem. You could take the parameter list and us a function to turn it into a table and join it against the rest of your code. You could also use Jeff Moden’s approach to breaking down the list through a query.
Do you want to put the result into a variable?
The question is not “Do you want to put the result into a variable”. In the “body” of an Oracle procedure, you just can’t use a SELECT without an INTO clause. (In fact you can have, but in a cursor). Caution : if no result is found, this will raise a NO_DATA_FOUND exception.
When to use a variable in a statement?
A variable can be used for a single value, not multiple values. If you want the variable to be translated as part of the statement, so that the comma is a delimiter and not part of the value itself, use dynamic SQL.