Home » SQL & PL/SQL » SQL & PL/SQL » Selec not null colmn only (oracle 10g)
Selec not null colmn only [message #322278] Fri, 23 May 2008 00:01 Go to next message
gaganzk
Messages: 36
Registered: May 2008
Member
Hi All,

I need the help regarding sql query can i select the columns from a table where value is not null only.

In brief i have a table with 88 column and i dont want to see null column in output so could you please help me select not null column only

Regards:
Gagan deep Kaushal
Re: Selec not null colmn only [message #322280 is a reply to message #322278] Fri, 23 May 2008 00:03 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Selec not null colmn only [message #322292 is a reply to message #322278] Fri, 23 May 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't you think you'll get a faster posting a SQL question in SQL forum and not in Backup and Recovery one? (How does it come in this forum?)

Regards
Michel
Re: Selec not null colmn only [message #322418 is a reply to message #322278] Fri, 23 May 2008 07:55 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
What if a column in only a few rows are NULL?
Re: Selec not null colmn only [message #322420 is a reply to message #322278] Fri, 23 May 2008 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want to have a result like this (assuming there is only one not null column in each row):
1
20/07/2008 12:54:45
michel.cadot@orafaq.com
This is your data
12487ADF4587DF
457
gaganzk@gmail.com
The world is divided into 3 types of people. Those who can do maths, and those who can't.
AAAAECAABAAAAgiAAA
23/05/2008 15:14:51.923 +02:00

What can you do with that?

Regards
Michel
Re: Selec not null colmn only [message #322686 is a reply to message #322292] Sun, 25 May 2008 23:06 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
Hi Michel Cadot i want to clear you my question what i need exactly as i am not able to get you from your post.

I have 88 column and 1789 rows in my testing table .Now i did not defined all the columns as not null.so 15-20 columns are always null.now i want to retrieve particular row suppose with customer_id=20 .So now i need only 40 column for this customer as all others (48) columns are null .
So how to retrieve exactly 40 column for this particular row.

i think it may be clear to you now?

i Appreciate you response toward my posts Michel Cadot.

Thanks
Gagan Deep Kaushal

[Updated on: Sun, 25 May 2008 23:08]

Report message to a moderator

Re: Selec not null colmn only [message #322694 is a reply to message #322686] Sun, 25 May 2008 23:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why? What is the purpose of thie?
Why do you want to hide the fact that those 40 columns have no value?
Why did you define those 40(!) columns anyway, if you don't plan to use them?
Re: Selec not null colmn only [message #322697 is a reply to message #322694] Sun, 25 May 2008 23:57 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
I never said i did'nt use these columns .i also need them but according to our application it varies customer to customer.

So it might not be a good option you are providing to me
Cool

Do you have query which can resolve my problem;

Thanks:
Gagan Deep Kaushal

[Updated on: Sun, 25 May 2008 23:57]

Report message to a moderator

Re: Selec not null colmn only [message #322698 is a reply to message #322686] Sun, 25 May 2008 23:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you know which columns are null then just select the other ones.
If you don't know which columns are null then it might be important to know which ones are null otherwise, as I said, how could you know to which columns belong the values you return?

Regards
Michel
Re: Selec not null colmn only [message #322723 is a reply to message #322697] Mon, 26 May 2008 01:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What kind of application is able to display (in an understandable way) an unknown number (and set) of columns?
How do you handle that?
Re: Selec not null colmn only [message #322751 is a reply to message #322723] Mon, 26 May 2008 02:35 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hi gagan, perhaps this may help you,

13:03:32 SQL> select * from x;

A          B                   C
---------- ---------- ----------
           ddd                 1
                               2
           ddd                 
value 1    a                   0

13:03:35 SQL> select nvl(a,'') || ' ' ||  nvl(b,'') || ' ' ||   nvl(c,'') output from x;

OUTPUT
--------------------------------------------------------------
 ddd 1
  2
 ddd 
value 1 a 0

13:03:43 SQL> 
Re: Selec not null colmn only [message #322757 is a reply to message #322751] Mon, 26 May 2008 02:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You seem to be missing an important point here.
What exactly does your output show? If you don't know which columns are null, you cannot tell what you selected!
Re: Selec not null colmn only [message #322779 is a reply to message #322757] Mon, 26 May 2008 03:56 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

@gagan
Or else, you need to apply login in your front end application to identify which columns are returing with NULL and make it invisible.



@frank,
from his post, it seems like hi needs such output.

Regards.

[Updated on: Mon, 26 May 2008 03:56]

Report message to a moderator

Re: Selec not null colmn only [message #322869 is a reply to message #322779] Mon, 26 May 2008 08:57 Go to previous message
gaganzk
Messages: 36
Registered: May 2008
Member
HI vithalani dipali,
Your post seem to very close.Little modification help me a lot.

Thanks a lot to all the user for their valuable replies i got through this forum.


Cheers Smile :
Gagan deep Kaushal
Previous Topic: Date data type
Next Topic: Optimise Query
Goto Forum:
  


Current Time: Sun Dec 04 20:53:16 CST 2016

Total time taken to generate the page: 0.08717 seconds