Oracle function Returning 1 even if the condition is not satisfied-CodePudding

Create or replace function abc(row_id in number) return number
as
  id_count number;
Begin

Select count (distinct column_name)
into id_count
from students_table
where class='A1' and row_id=row_id and grade='a' ;

Return id_count ;

End abc;

Now even if I pass a row_id whose class and grade is 'b1' and 'b' respectively, still the function returns 1 but it should return 0 as with that row_id there is no matching class and grade conditions.

Can anyone suggest

CodePudding user response：

Don't name PL/SQL variables (or, in this case, function arguments) with the same name as the column.

Create or replace function abc(
  p_row_id in STUDENTS_TABLE.ROW_ID%TYPE -- use a different name.
) return number
as
  id_count number;
Begin
  Select count (distinct column_name)
  into   id_count
  from   students_table
  where  class='A1'
  and    row_id=p_row_id        -- don't use "row_id = row_id"
  and    grade='a';

  Return id_count;
End abc;
/

If you use row_id = row_id then its the same as a 1 = 1 condition as Oracle does not know that you intended the left-hand side to be the column value and the right-hand side as the function argument and will use the same value on both sides of the condition.

CodePudding user response：

Rename the parameter of the function so that it does not match the column name.

e.g.

Create or replace function abc(P_row_id in number) return number

The predicate

where class='A1' and row_id=row_id and grade='a'

is interpreted as

where class='A1' and students_table.row_id=students_table.row_id and grade='a'

So the function return all rows with the restricted class and grade where the row_id IS NOT NULL - which is basically what you observe.