Mannaging Null collumns in large tables

[madsdp]

Hi!
I have tried to search the forums and have scoured the internet to try and find some solution to this issue without success.

The databases that I have to work with have very large tables (50+ collumns). This makes analyzing data timeconsuming because many of the collumns are unused with no values, which means that I have to scroll a lot back and forth to find the relevant data for doing the work that I do.

What I am looking for is a plugin/functionality that will either remove all unused/null collumns from my queries or group them all to the right so I can focus on the columns actually containing values on the left.

**OBS: I use a gazilion different tables every day so it will not work for me to construct views or to specify my desired collumns when i write the selects. I need to be able to do this 'on the fly' as default behaviour for plsql developer.
**I only need to exclude a collumn if ALL the FIELDS in that collumn are null/empty OR ALL the FIELDS in a collumn in the RESULTSET returned by the query, are null
**I cannot change the database table design to remove the unwanted tables.

something like this as an example:

select * (not null collumns) from table MyTable where MyCondition

select * (Send all null collumns right) from table MyTable where MyCondition

I know this is not part of standard SQL but it should be fairly easy to implement in the tool displaying the data and I have been very surprised not to have found anything :S

During my searches I have come across many people wanting this functionality so if it doesnt exist I truly think it would be worthwhile to develop it

Or maybe I just missed it in an obvious place
Cheers guys!

 

[Worker]

I see two three quick ways you can get what you want without waiting for a PL/SQL enhancement.

1) Perform your query. Ensure all rows have been fetched. Click in the top-left box of the results to select the entire result set. Right-click and pick "Column Totals..." -> "Count". Now look at the numbers below the results and for any column with a number "0", move (click & drag) it to the end.

2) Run the following in a test window. (Note: Only shows the first 500 rows of data. Play with the call to tablePrint() to make it do what you want.)

declare

  b	boolean;
  s	varchar2(32767);

begin

  for r in (	SELECT	 column_name
  		FROM	 ALL_TAB_COLUMNS
  		WHERE	 owner = :owner
  		AND	 table_name = :tbl
  		ORDER BY CASE WHEN num_distinct = 0
  			 THEN NULL ELSE column_id
  			 END ASC NULLS LAST ) LOOP

    s := s || ',' || r.column_name;

  end loop;

  b := owa_util.tablePrint(
    ctable=>:owner||'.'||:tbl,
    ccolumns=>SUBSTR( s, 2 ) );

end;

3) Write/google an Excel macro that removes empty columns, copy your result set to Excel and run the macro.

 

[madsdp]

Hi worker!
Thanks for you quick response. I have been starting to write several times and then been sidetracked, Sorry for the late reply.

I've read your response and unfortunately for my database the 'null'-collumns often outnumber the data filled collumns by something like 10:1. The overhead of copying the data and moving collumns around will more than offset any advantage gained by doing this manually.
So I guess I just have to wait for an extension or additional development from AllroundAutomations.

I would be interested to know if others in this forum found the funktionality I am describing interesting and if so please leave a comment on this thread.

Thanks!
Cheers guys