RE: How to select only columns having values..
- From: "Powell, Mark" <mark.powell2@xxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 17 Feb 2014 15:23:08 +0000
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] 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<mailto: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>
[mailto: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<mailto: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]
Sent: Monday, February 17, 2014 3:12 PM
To: Raja Kannan Sengoden
Cc: oracle-l@xxxxxxxxxxxxx<mailto: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>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Raja Kannan Sengoden
Sent: Monday, February 17, 2014 12:38 PM
To: oracle-l@xxxxxxxxxxxxx<mailto: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...
Other related posts:
