Solved

oracle query help

Posted on 2014-03-04
10
228 Views
Last Modified: 2014-03-11
I am looking for Oracle query with following requirements

- Find all columns where data is present

col1	        col2	        col3	       col4	      col5	      col6

								aa		x
aw		dsa		ds						y
						as				z	

Open in new window


OUTPUT

x col5
y col1,col2,col3
z col4

Open in new window

0
Comment
Question by:CalmSoul
  • 4
  • 4
  • 2
10 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39905543
select col6, case when col1 <> '' then 'col1, ' else '' end ||
                    case when col2 <> '' then 'col2, ' else '' end ||
                    case when col3 <> '' then 'col3, ' else '' end ||
                    case when col4 <> '' then 'col4, ' else '' end ||
                    case when col5 <> '' then 'col5, ' else '' end  "Cols"
from <table>
where
col1 <> '' or
col2 <> '' or
col3 <> '' or
col4 <> '' or
col5 <> ''

note you may need to wrap the columns in isnull  if they are nulls and not blanks.
0
 
LVL 5

Author Comment

by:CalmSoul
ID: 39905566
My data has nulls and blanks? where the wapper should go?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39906220
Just use the syntax above which is pretty much the same thing I provided in your previous question:
http://www.experts-exchange.com/Database/Oracle/Q_28375718.html

No need for a wrapper.  Just remove the where clause above.

Just like the code above and taken from your previous question (setup for tables is still in there):
select mytag, 
	rtrim(
		case when store1 is not null then 'store1,' end || 
		case when store2 is not null then 'store2,' end || 
		case when store3 is not null then 'store3,' end || 
		case when store4 is not null then 'store4,' end || 
		case when store5 is not null then 'store5' end 
	,',')
	mycsv
from tab1
/ 

Open in new window

0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39906653
Slight,
I'm assuming if a row has no data then he doesn't want to see said row.
select col6, rtrim(case when nvl(col1,'') <> '' then 'col1, ' else '' end || 
                    case when nvl(col2,'')<> '' then 'col2, ' else '' end ||
                    case when nvl(col3,'')<> '' then 'col3, ' else '' end ||
                    case when nvl(col4,'')<> '' then 'col4, ' else '' end ||
                    case when nvl(col5,'')<> '' then 'col5, ' else '' end ,',') "Cols"
from <table>
where 
nvl(col1,'') <> '' or 
nvl(col2,'') <> '' or 
nvl(col3,'') <> '' or 
nvl(col4,'') <> '' or 
nvl(col5,'') <> '' 

Open in new window

0
 
LVL 5

Author Comment

by:CalmSoul
ID: 39906841
thanks Kyle, let me see your solution
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39908011
>> then he doesn't want to see said row.

Unfortunately "nvl(col1,'') <> '' or ""  will not work in Oracle.

An empty string is considered a null value and nothing can be '=' or '!=' to a null.  You MUST use 'is null' and 'is not null'.

Try it:
select 'hello' from dual where nvl(null,'') = '';


If they have that requirement, personally I would do:
when coalesce(col1,col2,col3,col4,col5) is not null
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39908142
NVL converts the null to an empty string making it comparable . . . same thing as

isnull(column, '') != ''

no?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39908160
>>no?

No.   Try it from what I posted with the DUAL table.

NVL converts a null to an actual value.  An empty string is the same thing as a null in Oracle and not an actual value.

Oracle Database treats a character value with a length of zero as null. However, do not use null to represent a numeric value of zero, because they are not equivalent.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements005.htm#SQLRF30037
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39908179
Alright I stand corrected (it's been a few years since I worked with oracle),

change the

NVL (XX, '') <> ''
to NVL(XX, ' ') <> ' '


if there are more than one blank you may have to do:
/*Translate 2 or more blanks to one blank */
NVL( REGEXP_REPLACE(XX,'( ){2,}', ' '), ' ') <> ' '



As the original poster has said there are both null and blanks so just worrying about the null is not enough.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39909174
>>As the original poster has said there are both null and blanks so just worrying about the null is not enough.

Then "to NVL(XX, ' ') <> ' '"  will return a column with '           '.  Still not the requirement?

If you want to treat spaces in a column as 'no data' and a null then:
when coalesce(trim(col1),trim(col2),trim(col3),trim(col4),trim(col5)) is not null

regexp calls are very expensive and should only be used when necessary.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create file system directory from Oracle 10g 4 25
SQL Syntax 24 46
format dd/mm/yyyy parameter 16 31
Current Month Filter in Visual Studio 10 23
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

831 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