Oracle query assistance

SELECT 'col1'
FROM table1
WHERE col3 = 'x'
AND  (SELECT Count(*) FROM table1 WHERE col1 IS NULL) = 0
UNION
SELECT 'col2'
FROM table1
WHERE col3 = 'x'
AND (SELECT Count(*) FROM table1 WHERE col2 IS NULL) = 0

Hi Fomand: I have more than 30 columns will this work?

Yes, it will. You need to add

UNION
SELECT 'col2'
FROM table1
WHERE col3 = 'x'
AND (SELECT Count(*) FROM table1 WHERE col2 IS NULL) = 0

for every column.

You can generate the query dynamically, but it is quite complex.
To select column list from systables requires some privileges.
Create the list on your own and process it - it is almost the same as to add and to edit UNION...

I'm not sure you need to UNION all 30 columns to obtain what you want but I also do not fully understand your requirement.

>>return column name of all column which are "NOT NULL" and where col3=x

So if you add two additional rows to the table what is the expected output:
col1 col2 col3
1      (null)  x
2        3      y
(null)        4       x
5         6          x

I agree with slightwv, the requirement is not clear enough.

Assuming the the solution from formand do what you need, there is room for a little improvement:

each select could be written as below:

SELECT 'col1'
FROM table1
WHERE col3 = 'x'
AND col1 is not null 

Select allOpen in new window

Regards,
Walter.

here is sample data: https://docs.google.com/spreadsheet/ccc?key=0As8ubhOj3mzLdDVMMlhJYzE2WjBFa3BzSk1aWmdkeXc&usp=sharing

- need to find all columns where data is present ... group by column F

>>here is sample data

Any reason you didn't upload it to experts-exchange and this question?

>>need to find all columns where data is present ... group by column F

Requirement still not clear.

Is that data supposed to be a table with 7 columns containing 4 sample rows of data?

Given that data, what should the results look like?

I can't share the real data and its private information this is just a sample.. Uploading EE is not working ...

I don't how to put the requirements but in plain english

I need column names of all columns where data is present - for each unique entry in column F in the sample.

Using following query I am getting results but I want to optimize it

select max(col1), max(col2) ... group by col6

>>I can't share the real data and its private information this is just a sample

I don't need real data.  I just need to understand the requirement and I can apply that to the sample data.

You didn't answer my question:
Is that data supposed to be a table with 7 columns containing 4 sample rows of data?

>>I don't how to put the requirements but in plain english

That is my problem.  I'm not understanding from the typed description.

Try not explaining it.  Show me the expected output.  If I cannot deduce the requirement from the inputs and outputs, I'll ask for clarification.

>>select max(col1), max(col2) ... group by col6

See, this is nothing like what I was understanding from any previous post.

If you want the MAX value for ALL columns except one or two, I think that is about as optimized as you can get.

If you get full table scans and very few columns have data then indexes on those columns might help but you will need to try it.  Indexes that fix one query can hurt others.

You didn't answer my question:
Is that data supposed to be a table with 7 columns containing 4 sample rows of data?

Real data has 30 columns in total... and 1000 records

Sample Output from the sample data provided above:

Coffee - E
Tea - B,C,D,E
Green Tea - A,D

Thanks.  I understand the requirement as far as output.

how do you want those results?

Three rows with two columns and a CSV for the hits:

category       not null columns
-----------    -------------------------
Coffee         E
Tea            B,C,D,E
Green Tea      A,D

Select allOpen in new window

Or something else?

Correct - Just like you have showed .. thats perfect

Sorry but two more questions:

What is your Oracle version (all 4 numbers please: 11.2.0.4)?
What is the maximum length of the resulting CSV string?  Can it EVER exceed 4000 characters?

What is your Oracle version (all 4 numbers please: 11.2.0.4)?
I have oracle 8 - I am using toad data point - its connects to the database but via warning using 10g drivers. I don't how to get complete version via toad ... do you know?

What is the maximum length of the resulting CSV string?  Can it EVER exceed 4000 characters?
No it will not increase 4000 in lenght