Dmitry Klishin Dmitry Klishin - 3 months ago 9
SQL Question

Using of variable gives an error, but using of direct value is correct

Good afternoon! I have a table with one string and this block of code:

GAME_DATE FIRST_PART_ID SECOND_PART_ID GAME_ID
21.08.16 18:00:00,000000000 1000 1001 1000007




declare
game_date varchar2(16) := '21.08.2016 15:00';
varch varchar2(100);
begin
select g.game_id into varch FROM games g WHERE to_char(g.game_date - 3/24, 'DD.MM.YYYY HH24:MI') = game_date;
select g.game_id into varch FROM games g WHERE to_char(g.game_date - 3/24, 'DD.MM.YYYY HH24:MI') = '21.08.2016 15:00';
dbms_output.put_line(varch);
end;


If i am using first query in "begin" I get an error:

Error report:
ORA-01403: no data found


But the second query returns right answer:

anonymous block completed
1000007


Why is there such a big difference?

Kindly assist.

Answer

The table column game_date and variable game_date are potentially getting confused.

Consider renaming the variable to v_game_date.

e.g.

declare
  v_game_date varchar2(16) := '21.08.2016 15:00';
  varch varchar2(100);
begin
  select g.game_id into varch FROM games g WHERE to_char(g.game_date - 3/24, 'DD.MM.YYYY HH24:MI') = v_game_date;
  select g.game_id into varch FROM games g WHERE to_char(g.game_date - 3/24, 'DD.MM.YYYY HH24:MI') = '21.08.2016 15:00';
  dbms_output.put_line(varch);
end;
Comments