cursor values in SQL query

Question

I have two tables  
1.
shiv_tab_col

TABLENAME     | COLUMNNAME
--------------------------  
 SER_SHIV_SYN | TABLENAME    
 SER_SHIV_SYN | COLUMNNAME


AND SER_SHIV_SYN

TABLENAME   | COLUMNNAME
--------------------------  
 A          |  B  
 E          | NULL  
NULL        | NULL  
NULL        | NULL  
NULL        | NULL  
NULL        | NULL  

BUT WHEN I M TRYING TO RUN THE BELOW CODE IT IS GIVING ME '0' AS VALUE OF TNAME

DECLARE
tname varchar(20):=null;

 CURSOR C1 IS
    SELECT  *
    FROM SHIV_TAB_COL;


BEGIN
    for rec in C1

loop
    select count(*) into tname from (select nvl(rec.columnname,1)b from ser_shiv_syn) where b ='1';
    dbms_output.put_line(tname);
    dbms_output.put_line(rec.columnname);
END LOOP;
END;

Actually i m looking to count the no. of null values in each column which will be stored in variable TNAME
for first column it should give 4
for 2 column it should give 5

Please improve formatting, this is really tough to read. And please add at least the value for TNAME you expected. It is really hard to improve/correct something when you don't know the desired outcome. — Martin Schapendonk
– Martin Schapendonk, Commented Nov 23, 2010 at 8:38
Sorry for being harsh, but your code makes very little sense. It doesn't look at all like you know what you are doing, more like a wild copy-paste-session with a bit of trial and error. — Erich Kitzmueller
– Erich Kitzmueller, Commented Nov 23, 2010 at 8:49
When you execute select count(*) into tname then it's reasonable that you get tname='0'. You can't join data in a table with the names of tables/columns — smirkingman
– smirkingman, Commented Nov 23, 2010 at 9:47

Martin Schapendonk · Accepted Answer · 2010-11-23 09:59:32Z

1

What you are doing now is counting all empty occurrences of the column COLUMNNAME in the table shiv_tab_col. Which is 0 (you already found that out).

The solution is a bit more complicated, you need dynamic SQL to accomplish what you want to do:

declare
  l_count pls_integer;
begin
  for r_cur in (
    select columnname
    from shiv_tab_col
  )
  loop
    execute immediate 
      'select count(1) from ser_shiv_syn where '||r_cur.columnname||' is null' 
    into l_count;
    dbms_output.put_line(l_count);
    dbms_output.put_line(r_cur.columnname);
  end loop;
end;
/

answered Nov 23, 2010 at 9:59

Martin Schapendonk

13.6k3 gold badges22 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Schapendonk Over a year ago

Your query determined if the string 'TABLENAME' is null (it isn't) and if 'COLUMNNAME' is null (it isn't either). Your query is based on the wrong assumption that Oracle will actually translate the string 'TABLENAME' to mean "the column TABLENAME from the table ser_shiv_syn".

Modafuka · Accepted Answer · 2010-11-23 12:18:57Z

1

Just a small hint: For your cursor use this:

SELECT  column_name 
FROM    all_tab_columns
WHERE   table_name = 'SER_SHIV_SYN'

Instead of shiv_tab_col.

This table is automatically updated and it's always in there. It also has field type, size, etc. Everything and more for 0 cost. Win-Win.

answered Nov 23, 2010 at 12:18

Modafuka

311 bronze badge

Collectives™ on Stack Overflow

cursor values in SQL query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related