RE: How to select only columns having values..
- From: Jeff Smith <jeff.d.smith@xxxxxxxxxx>
- To: ric.van.dyke@xxxxxxxxxx, Kim Berg Hansen <kibeha@xxxxxxxxx>
- Date: Tue, 18 Feb 2014 06:31:25 -0800 (PST)
>> Don't use Excel. J
Ha ha ha.
Sorry, couldn't help myself.
From: Ric Van Dyke [mailto:ric.van.dyke@xxxxxxxxxx]
Sent: Tuesday, February 18, 2014 9:29 AM
To: Kim Berg Hansen
Cc: Mark W. Farnham; mark.powell2@xxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: How to select only columns having values..
Don't use Excel. J
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@xxxxxxxxx]
Sent: Tuesday, February 18, 2014 9:21 AM
To: Ric Van Dyke
Cc: Mark W. Farnham; HYPERLINK "mailto:mark.powell2@xxxxxx"mark.powell2@xxxxxx;
HYPERLINK "mailto:oracle-l@xxxxxxxxxxxxx"oracle-l@xxxxxxxxxxxxx
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
HYPERLINK "mailto:kibeha@xxxxxxxxx"; \nkibeha@xxxxxxxxx
@kibeha
On Tue, Feb 18, 2014 at 2:56 PM, Ric Van Dyke <HYPERLINK
"mailto:ric.van.dyke@xxxxxxxxxx"; \nric.van.dyke@xxxxxxxxxx> 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.
Other related posts:
