Date comparison error when storing with into

Question

Im almost done with a procedure i started yesterday, but on the last step i have found a problem that happens only if a comparison is done with an if clause. The procedure shows all the information of a match if you introduce a football league round and the name of a team. Im storing the football match id on idp for later comparisons when the team is either the home or the away team and the date matches with one of the two rounds available (the if clause sets a different date to a variable called FECHA if it is either one or two). The thing is that, if i try to check the FECHA or date in spanish manually, it works, but if i try using the if path, an error appears claiming: Could you help me solve that? Thank you so much!

create or replace PROCEDURE COMPROBARPARTIDO(JORNADA IN NUMBER, EQUIPO IN VARCHAR2) AS 
FECHA DATE;
IDLOCAL NUMBER;

IDP NUMBER;
NUMAUX NUMBER;
NUMAUX2 NUMBER;
GOLAUX NUMBER;
GOLOC NUMBER;
GOLVI NUMBER;
BEGIN
NUMAUX:=0;
NUMAUX2:=0;
  IF JORNADA = 1 THEN
FECHA := TO_DATE('2021-03-04','yyyy-mm-dd');
ELSIF JORNADA = 2 THEN
FECHA := TO_DATE('2021-03-13','yyyy-mm-dd');
ELSE
    DBMS_OUTPUT.PUT_LINE('ERROR');
END IF;

SELECT DISTINCT P.ID INTO IDP
FROM PARTIDO P
INNER JOIN EQUIPO EL 
ON P.ID_LOCAL =EL.ID
INNER JOIN EQUIPO EV
ON P.ID_VISITANTE = EV.ID
WHERE P.FECHA = FECHA AND (EV.NOMBRE =EQUIPO OR EL.NOMBRE=EQUIPO);

score 1 · Accepted Answer · answered Jun 01 '21 at 13:02

You are doing:

WHERE P.FECHA = FECHA

From the documentation:

If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.

So that means that condition is equivalent to:

WHERE P.FECHA = P.FECHA

which is always true (unless P.FECHA is null). That means you are finding all rows where (EV.NOMBRE =EQUIPO OR EL.NOMBRE=EQUIPO), regardless of the P.FECHA value. That is giving you more rows than you expect; as you are using select ... into ... the query has to return exactly one row, and presumably does when you hard-code a date.

You should either explicitly prefix your variable name with the procedure name to give it context:

WHERE P.FECHA = COMPROBARPARTIDO.FECHA

or rename you local variable; it's fairly common to add a L_ prefix to local variable names and P_ for parameter names, for example, to distinguish them from column names:

create or replace PROCEDURE COMPROBARPARTIDO(P_JORNADA IN NUMBER, P_EQUIPO IN VARCHAR2) AS 
L_FECHA DATE;
...

and change all the references to match:

WHERE P.FECHA = L_FECHA

Date comparison error when storing with into

1 Answers1