Here's an answer attempt for the real question, based on Marks's suggestion.

First, generate some test data, i.e. a table T_Test with 5 columns of which Col1 is NOT NULL by design, Col2-Col4 may have NULLs, and Col5 is always NULL. You may filter the table to include more or less all-NULL columns, say by using "where Col4 is null".

with
   S1 as (select row_num as Nr, case when mod(row_num, 4) = 0 then null else row_num end as NrOrNull from sa_rowgenerator(1,100)),
   S2 as (select row_num as Nr, case when mod(row_num, 3) = 0 then null else row_num end as NrOrNull from sa_rowgenerator(1,100)),
   S3 as (select row_num as Nr, case when mod(row_num, 2) = 0 then null else row_num end as NrOrNull from sa_rowgenerator(1,100))
select S1.Nr as Col1, S1.NrOrNull as Col2, S2.NrOrNull as Col3, S3.NrOrNull as Col4, null as Col5
into T_Test
from S1 inner join S2 on S1.Nr = S2.Nr inner join S3 on S1.Nr = S3.Nr
order by 1, 2, 3, 4, 5

Then use a FOR statement based on the system catalog to enumerate all column names, and then use a count query for each to count the NOT NULL values. Note, if your original query uses a WHERE condition, the count query needs to do so, too.

begin
   declare strQuery long varchar;
   declare strCntQuery long varchar;
   declare nCnt int;
   for forCrs as crs cursor for
      select SC.column_name
      from systabcol SC key join systab ST where table_name = 'T_Test' order by column_id
   do
      -- build the "count query"
      set strCntQuery = 'select count(' || column_name || ') into nCnt from T_Test';
      execute immediate strCntQuery;
      message 'Column/Count : ' || column_name ||  '/' || nCnt to client;
      if nCnt > 0 then 
         set strQuery = strQuery || ', ' || column_name;
      end if;
   end for;
   -- finally complete the query and use it with EXECUTE IMMEDIATE
   if strQuery is not null then
      set strQuery = substr(strQuery, 3); -- drop the initial comma
      set strQuery = 'select ' || strQuery || ' from T_Test order by 1';
      message 'Null-free statement: ' || strQuery to client;
      execute immediate with result set on strQuery;
   end if;
end;

The sample will return a result set that omits Col5.

Variation: If you add a WHERE clause within the two queries on T_Test, say by replacing "from T_Test" with "from T_Test where Col2 Is null", the result set will omit Col2 and Col4, as well.

Apparently, that could easily be enhanced to

• use an arbitrary dynamic query with joins/filters instead of the hard-coded sample table
• use sa_describe_query() instead of the system catalog to get the column names