CalmSoul
asked on
oracle query help
I am looking for Oracle query with following requirements
- Find all columns where data is present
OUTPUT
- Find all columns where data is present
col1 col2 col3 col4 col5 col6
aa x
aw dsa ds y
as z
OUTPUT
x col5
y col1,col2,col3
z col4
ASKER
My data has nulls and blanks? where the wapper should go?
Just use the syntax above which is pretty much the same thing I provided in your previous question:
https://www.experts-exchange.com/questions/28375718/Oracle-query-assistance.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):
https://www.experts-exchange.com/questions/28375718/Oracle-query-assistance.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
/
Slight,
I'm assuming if a row has no data then he doesn't want to see said row.
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,'') <> ''
ASKER
thanks Kyle, let me see your solution
>> 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,co l4,col5) is not null
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,co
NVL converts the null to an empty string making it comparable . . . same thing as
isnull(column, '') != ''
no?
isnull(column, '') != ''
no?
>>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.
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements005.htm#SQLRF30037
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
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.