Solved

Oracle query assistance

Posted on 2014-02-26
15
397 Views
Last Modified: 2014-02-27
I am looking for oracle query to return column name only if there is data in the column cell  and also need to do where on column with certain value. For example

(table 1)

col1 col2 col3
1      (null)  x
2        3      y

OUTPUT will be col1 because its not null and col3=x

i.e. return column name of all column which are "NOT NULL" and where col3=x

Please assist.... I hope I explained it correctly
0
Comment
Question by:CalmSoul
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 8

Expert Comment

by:Andrei Fomitchev
Comment Utility
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
0
 
LVL 5

Author Comment

by:CalmSoul
Comment Utility
Hi Fomand: I have more than 30 columns will this work?
0
 
LVL 8

Expert Comment

by:Andrei Fomitchev
Comment Utility
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...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
0
 
LVL 15

Expert Comment

by:Walter Ritzel
Comment Utility
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 

Open in new window


Regards,
Walter.
0
 
LVL 5

Author Comment

by:CalmSoul
Comment Utility
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 5

Author Comment

by:CalmSoul
Comment Utility
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 
LVL 5

Author Comment

by:CalmSoul
Comment Utility
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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

Open in new window


Or something else?
0
 
LVL 5

Author Comment

by:CalmSoul
Comment Utility
Correct - Just like you have showed .. thats perfect
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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?
0
 
LVL 5

Author Comment

by:CalmSoul
Comment Utility
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
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
>>I have oracle 8

OK, what are the other 3 numbers?  8.0.0.1, 8.2.0.4, what?

>>I don't how to get complete version via toad

Toad version doesn't matter.  Just the database  version.

>>Can it EVER exceed 4000 characters?  No it will increase 4000 in lenght

I do not understand this.  Can the resulting string be greater than 4000 characters once concatenated together?  If so, we might have some problems.


While I wait:
Here is a complete test case that provides the expected results using your provided sample data.

If it doesn't work, please add rows/columns to the tables and update your expected results.

It should run on Oracle 8 no matter the version (I forget when the CASE statement came out) but I don't have anything near that old still around to confirm it.

If the case statement doesn't run, we can switch to the older DECODE.

drop table tab1 purge;

create table tab1(
Store1 char(1), Store2 char(1), Store3 char(1), Store4 char(1), Store5 char(1), mytag varchar2(10)
);


insert into tab1 values(null,null,null,null,'1','Coffee');
insert into tab1 values(null,'1','1','1','1','Tea');
insert into tab1 values('1',null,null,'1',null,'Green Tea');
commit;

select mytag, 
	rtrim(
		case when store1 is not null then 'A,' end || 
		case when store2 is not null then 'B,' end || 
		case when store3 is not null then 'C,' end || 
		case when store4 is not null then 'D,' end || 
		case when store5 is not null then 'E' end 
	,',')
	mycsv
from tab1
/

Open in new window

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 43
MSSQL 2014 Query Synthax 8 36
how to fix this error 14 45
total hours between two times grouped by type 6 29
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now