Re: How to select only columns having values..
- From: Maaz Anjum <maazanjum@xxxxxxxxx>
- To: mark.powell2@xxxxxx
- Date: Mon, 17 Feb 2014 15:35:00 -0500
A slightly prettier pig though :)
On Mon, Feb 17, 2014 at 3:01 PM, Powell, Mark <mark.powell2@xxxxxx> wrote:
> I missed the earlier part of the thread so I did not see the underlying
> design. I just thought your idea was to eliminate the use of dynamic SQL
> by using a static select of the entire row and then having the program
> format the data and heading labels in code. This seems a lot easier that
> tying to dynamically construct and execute select that do not reference
> columns with null data.
>
>
>
> If the underlying design is non-relational and holds multiple table rows
> in one table then you are absolutely correct. Putting lipstick on the pig
> still leaves you with a pig.
>
>
>
>
>
> *From:* Ric Van Dyke [mailto:ric.van.dyke@xxxxxxxxxx]
> *Sent:* Monday, February 17, 2014 2:11 PM
> *To:* mwf@xxxxxxxx; Powell, Mark; oracle-l@xxxxxxxxxxxxx
>
> *Subject:* RE: How to select only columns having values..
>
>
>
> Cool as that sounds Mark, I doubt it would work any "better" (as in
> faster) which is the posters original concern. This is basically just a
> different way to do the same thing his block of code already does. The
> problem in both cases is the creation of dynamic SQL which is never going
> to be faster than just running a statement.
>
>
>
> The base of this issue is some horrible design decisions. No matter how
> much "bling" you put on this, it's still a terrible way to store the data.
> Which in turn makes it very difficult to retrieve. Maybe it was done to
> make the insert of the date "easier", never a good idea. We generally
> insert once and query a qua-zillion times.
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Mark W. Farnham
> *Sent:* Monday, February 17, 2014 1:33 PM
> *To:* mark.powell2@xxxxxx; oracle-l@xxxxxxxxxxxxx
> *Subject:* RE: How to select only columns having values..
>
>
>
> While I'm not taking the time to do it, and I'm not claiming this is
> efficient (in other words it is possibly saving analyst time at the expense
> of computer time), I **think**
>
> you could generate a dynamic select clause using the <table> with the
> particular where clause joined and pivoted on column_name <c> from
> user_tab_columns for the <table> having
>
> sum(decode(nvl(<c>,0),<c>,1,0,0)) "<c>" for each column <c> having > 0,
>
> plucking the rows from the result set as the column names to create the
> desired select statement with the original <table> and predicate.
>
>
>
> This, of course, would only work on types for which the nvl function is
> valid. For any <c> in your table for which nvl is not valid you would have
> to use some other means to determine whether any of the rows for your
> current predicate have non-null values.
>
>
>
> The text of your generated queries for a 200 column table is going to be
> pretty long.
>
>
>
> There is probably a reasonable way to code this up in PL/SQL other than
> using the pivot, but if you're citing an arbitrary where clause and no data
> monitoring, I agree there is no way to specify which columns might have
> only nulls without running the query. On top of this, understand that since
> this is a two stage process you would need to construct this internal to a
> transaction boundary lest some column changes from status from having some
> non-nulls to only containing nulls between the first and second queries.
>
>
>
> I suppose you could reduce the number of candidate columns and invariantly
> include any columns in the query for which non-null is a column attribute.
>
>
>
> Getting the syntax all correct and doing this double dynamic select
> statement generation seems pretty tricky to me, but I **think** you could
> do it.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Powell, Mark
> *Sent:* Monday, February 17, 2014 10:23 AM
> *To:* oracle-l@xxxxxxxxxxxxx
> *Subject:* RE: How to select only columns having values..
>
>
>
> I also agree with Ric. Select the entire row and let the application
> front-end deal with displaying the data. Stored PL/SQL could be used
> between the front-end and the database, but it is going to be real
> interesting when the situation arises that one of the rows that is returned
> does not have data in all the same columns as the other rows returned in
> the target set. I expect that this situation is bound to happen sooner or
> later.
>
>
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Maaz Anjum
> *Sent:* Monday, February 17, 2014 8:52 AM
> *To:* ric.van.dyke@xxxxxxxxxx
> *Cc:* rajakannan@xxxxxxxxxx; Chitale, Hemant K; oracle-l@xxxxxxxxxxxxx
> *Subject:* Re: How to select only columns having values..
>
>
>
> I agree with Ric.
>
>
>
> I assume you need these populated rows at execution time. This is just a
> thought, but if statistics are up-to-date on the table then perhaps
> checking the user_tab_columns for the num_nulls column compared against the
> total number of rows (user_tables.num_rows) might help you. The point
> being, you query meta data that is less costly than the actual table. I
> might be wrong, but it's just an idea.
>
>
>
> Either way, it doesn't seem you can avoid a complicated SQL statement.
>
>
>
> Cheers,
>
> Maaz
>
>
>
> On Mon, Feb 17, 2014 at 8:40 AM, Ric Van Dyke <ric.van.dyke@xxxxxxxxxx>
> wrote:
>
> Offhand this certainly has the sound of some very bad data modeling but
> that likely is beyond your control. Is this some sort of "generic table"?
> If so that will just lead to problems and you're only just starting to have
> them. Simply put, this table breaks just about every rule of relational
> theory and hence is not going to perform well no matter what you do.
>
>
>
> It seems to me the "best" thing would be just select the whole row(s) and
> then have the application dissect the row(s) and pull out where there is
> data and where there isn't. Creating the dynamic SQL as you do works, but
> will always be slow. Getting the entire row and then parsing thru it to
> find data is likely going to be faster.
>
>
>
>
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Raja Kannan Sengoden
> *Sent:* Monday, February 17, 2014 2:36 AM
> *To:* Chitale, Hemant K
>
>
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *Subject:* RE: How to select only columns having values..
>
>
>
> Thank a lot Hemant K Chitale.
>
>
>
> As per the real scenario, all the columns will have the value.
>
>
>
> But for the particular condition, only limited columns will have value.
>
>
>
> At this time, I don't want to do analyze the data and maintain.
>
>
>
> Hence I requested, is there any simple way..
>
>
>
> Attached query helped me to get the expected result.
>
>
>
> Thanks to Tony who helped me to get the expected result.
>
>
>
> Thanks & Regards,
>
> Raja
>
>
>
> *From:* Chitale, Hemant K
> [mailto:Hemant-K.Chitale@xxxxxx<Hemant-K.Chitale@xxxxxx>]
>
> *Sent:* Monday, February 17, 2014 3:12 PM
> *To:* Raja Kannan Sengoden
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *Subject:* RE: How to select only columns having values..
>
>
>
> > If I don't know exactly, Is there any way to select only those 10
> columns?
>
> Without actually querying the table, you cannot identify the 10 columns.
>
>
>
> You could periodically query the table for all the rows and identify
> columns with NOT NULL values and then maintain a list of such columns as
> "meta data". However, there is no guarantee that a column that had NULL
> values for all 1000 rows yesterday or even a minute ago still has NULL
> values because there might have been an INSERT or UPDATE that set one row's
> value to a non-NULL.
>
>
>
> How do you know that the 190 columns have NULL values ?
>
>
>
> Hemant K Chitale
>
>
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Raja Kannan Sengoden
> *Sent:* Monday, February 17, 2014 12:38 PM
> *To:* oracle-l@xxxxxxxxxxxxx
> *Subject:* How to select only columns having values..
>
>
>
> Dear Experts,
>
>
>
> I have a table with 200 columns and having above 1000 Records.
>
>
>
> But for a particular where clause, there is only 3 records fetched.
>
>
>
> As the table having 200 columns, but the particular 3 record having only
> 10 columns with some value, and 190 columns having null value.
>
>
>
> If I am sure, which columns I want or which column having value, then I
> can select only those columns.
>
>
>
> If I don't know exactly, Is there any way to select only those 10 columns?
>
>
>
> If I get the data like this, It will save few minutes for the data
> arrangement.
>
>
>
>
>
> Thanks in advance.
>
>
>
> Raja.
>
>
> This email and any attachments are confidential and may also be
> privileged. If you are not the intended recipient, please delete all copies
> and notify the sender immediately. You may wish to refer to the
> incorporation details of Standard Chartered PLC, Standard Chartered Bank
> and their subsidiaries at https://www.sc.com/en/incorporation-details.html
> .
>
>
>
>
>
> --
> A life yet to be lived...
>
--
A life yet to be lived...
Other related posts:
