CODEWITHSUNDEEP
sqloracle-databaseoracle11g
L4zl0w
L4zl0w

Reputation: 1097

Oracle mystery with filtering a query on numbers

I have a piece of very simple SQL that I cannot make work...

DECLARE
 RESTORE_ID NUMBER;
 counts NUMBER;
BEGIN
RESTORE_ID := 100014;
SELECT COUNT(UPDATE_ID) INTO counts FROM TB_ENT WHERE UPDATE_ID = RESTORE_ID;
DBMS_OUTPUT.PUT_LINE(counts);
END;

If I run the above, the output is 0.

If I run it without the RESTORE_ID variable replacing the SELECT line with: 

SELECT COUNT(UPDATE_ID) INTO counts FROM TB_ENT WHERE UPDATE_ID = 100014;

You guessed, the output is 1. Why??

Upvotes: 1

Views: 43

Answers (1)

kevinskio
kevinskio

Reputation: 4551

You should avoid using the names of columns for your variables. Here is your code rewritten to do that. I also like aliases for tables as I feel it keeps queries with lots of joins shorter.

DECLARE
 vRESTORE_ID NUMBER;
 vcounts NUMBER;
BEGIN
vRESTORE_ID := 100014;
SELECT COUNT(te.UPDATE_ID) 
INTO vcounts 
FROM TB_ENT te 
WHERE te.UPDATE_ID = vRESTORE_ID;
DBMS_OUTPUT.PUT_LINE(vcounts);
END;

Upvotes: 4

Related Questions