RE: How to select only columns having values..

Seriously. With all the functionality that is in the DB now there isn't much need to export to excel like there used to be. Analytics and the Model clause really give you all you need that for the most part folks used excel for.  

From: Kim Berg Hansen [mailto:kibeha_at_gmail.com] Sent: Tuesday, February 18, 2014 9:21 AM To: Ric Van Dyke
Cc: Mark W. Farnham; mark.powell2_at_hp.com; oracle-l_at_freelists.org Subject: Re: How to select only columns having values..  

Hi, all  

I know it's dangerous to guess at the grander picture rather than getting it from the OP, but just for fun and the sake of argument, here's a guess at a use case ;-)    

Suppose there is no app? No local structure to load data into and parse through?  

Suppose we are talking adhoc queries with some data analyst getting requests for querying data and return an Excel file.

So the data analyst writes a query in SQL Developer or Toad or whatever and then saves/exports the data grid to Excel.  

Suppose said data analyst retrieves data from a 200 column table/view/query and the enduser would not like him to return an Excel file with 200 columns.

Instead he must return only non-null columns in the Excel file.  

If said data analyst does not know beforehand which columns will be non-null, he could select *, export everything to Excel, and then spend a couple minutes deleting columns in Excel that are entirely empty.

If that adds a couple minutes work for every adhoc query he has to make Excel files for his endusers, that could add up ;-)  

Given such a scenario - what would be the alternative to dynamic SQL?    

Regards    

Kim Berg Hansen  

http://dspsd.blogspot.com

kibeha_at_gmail.com

_at_kibeha    

On Tue, Feb 18, 2014 at 2:56 PM, Ric Van Dyke <ric.van.dyke_at_hotsos.com> wrote:

Roger that Mark.  

But why do any of that? Why not just read the entire row, really the whole table, into some local structure in the app and parse thru looking for what you need? Now you hit the DB once and done. No dynamic SQL no hacking thru the DD, and one parse/execute/fetch(s) cycle. Even using a PL/SQL block that processes each row one at a time would likely take a couple of seconds at worse.  

I agree that this part of the code shouldn't take minutes of time for what appears to be a small amount of data. Of course "above 1000 records" is vague enough to mean just about any number of rows, 1001? 10,000,000?  

We don't know the grander picture of all this so it's really hard to say what is exactly the right solution. I just don't see the need for such complex SQL at this point.  

--
http://www.freelists.org/webpage/oracle-l