CODEWITHSUNDEEP
oracle-database
Moudiz
Moudiz

Reputation: 7377

select count always giving while a condition exists

I have the below block 

declare TABLE_NAME varchar(200);
ALTCOUNT number(2);
TABLE_NAME ='TEST';
begin
ALTCOUNT :=0;
SELECT COUNT(1) INTO ALTCOUNT FROM A_TABLES A WHERE A.TABLE_NAME = ''||UPPER(TABLE_NAME)||'';
IF ALTCOUNT =0 THEN
EXECUTE IMMEDIATE ('insert into A_TABLES (TABLE_NAME ,A_TABLE_NAME) values('a','w');
end;

my problem is in this query

SELECT COUNT(1) INTO ALTCOUNT FROM A_TABLES A WHERE A.TABLE_NAME = ''||UPPER(TABLE_NAME)||'';

even if a.table name ='TEST and table_name ='TEST' the count(1) is always = 0 . Why?

Upvotes: 1

Views: 104

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

Change this:

WHERE A.TABLE_NAME = ''||UPPER(TABLE_NAME)||''

to 

WHERE A.TABLE_NAME = UPPER(table_name)

And in the newer versions, COUNT(1) is same as COUNT(*).

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191415

You're querying for a table called 'TEST', including the quotes. At least I think you are, though what you've shown won't run. You don't need to quote the variable. But you've created a scoping problem by giving the variable the same name as the table column, so use something else - a prefix is often used for this:

declare
  l_table_name varchar2(200);
  l_count number(2);
begin
  l_table_name := 'TEST';

  select count(1) into l_count
  from a_tables a
  where a.table_name = l_table_name;

  if l_count = 0 then
    insert into a_tables (table_name, a_table_name) values ('a','w');
  end if;
end;
/

You had several other problems that would have prevented this running at all as shown, so I guess it wasn't your actual code. You also don't need the dynamic SQL so I've removed that.

Upvotes: 4

Related Questions