Home » SQL & PL/SQL » SQL & PL/SQL » How to remove the NULL values from SELECT clause
| How to remove the NULL values from SELECT clause [message #634390] |
Mon, 09 March 2015 07:02  |
 |
samiran_cts
Messages: 52
Registered: January 2012
|
Member |
|
|
I am creating one query:
select col1,null col2,null col3 from test1
union all
select null,col2,null col3 from test2
union all
select null,null, col3 from test2;
The query displaying the records as:
col1 col2 col3
ABC NULL NULL
NULL PQR NULL
NULL LMP NULL
NULL NULL CCC
But I want to remove the NULL and want to display as
col1 col2 col3
ABC PQR CCC
NULL LMP NULL
|
|
|
|
|
|
| Re: How to remove the NULL values from SELECT clause [message #634395 is a reply to message #634390] |
Mon, 09 March 2015 07:43  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
From one of your previous topics:
Michel Cadot wrote on Wed, 20 June 2012 15:29Michel Cadot wrote on Wed, 20 June 2012 13:02...
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...
Regards
Michel
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also feedback and thank people for their time and help otherwise you'll no more have any help.
[Updated on: Mon, 09 March 2015 07:44] Report message to a moderator |
|
|
|
| Re: How to remove the NULL values from SELECT clause [message #634411 is a reply to message #634390] |
Mon, 09 March 2015 16:06 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following arbitrarily orders by rowid, but you could order by any other column if you went the vaues in a column in a specific order. I also used 3 tables, assuming that your last test2 was actually test3, but if your data for col3 is actually in test2, then you could use test2 instead.
SCOTT@orcl12c> -- test data:
SCOTT@orcl12c> create table test1 (col1 varchar2(4))
2 /
Table created.
SCOTT@orcl12c> insert into test1 values ('ABC')
2 /
1 row created.
SCOTT@orcl12c> create table test2 (col2 varchar2(4))
2 /
Table created.
SCOTT@orcl12c> insert into test2 values ('PQR')
2 /
1 row created.
SCOTT@orcl12c> insert into test2 values ('LMP')
2 /
1 row created.
SCOTT@orcl12c> create table test3 (col3 varchar2(4))
2 /
Table created.
SCOTT@orcl12c> insert into test3 values ('CCC')
2 /
1 row created.
SCOTT@orcl12c> -- original query:
SCOTT@orcl12c> set null NULL
SCOTT@orcl12c> select col1,null col2,null col3 from test1
2 union all
3 select null,col2,null col3 from test2
4 union all
5 select null,null, col3 from test3
6 /
COL1 COL2 COL3
---- ---- ----
ABC NULL NULL
NULL PQR NULL
NULL LMP NULL
NULL NULL CCC
4 rows selected.
SCOTT@orcl12c> -- revised query:
SCOTT@orcl12c> select t1.col1, t2.col2, t3.col3
2 from (select col1, rownum rn
3 from test1
4 order by rowid) t1
5 full outer join
6 (select col2, rownum rn
7 from test2
8 order by rowid) t2
9 on (t1.rn = t2.rn)
10 full outer join
11 (select col3, rownum rn
12 from test3
13 order by rowid) t3
14 on (t2.rn = t3.rn)
15 /
COL1 COL2 COL3
---- ---- ----
ABC PQR CCC
NULL LMP NULL
2 rows selected.
SCOTT@orcl12c>
|
|
|
|
| Re: How to remove the NULL values from SELECT clause [message #634421 is a reply to message #634411] |
Tue, 10 March 2015 01:31 |
|
samiran_cts
Messages: 52
Registered: January 2012
|
Member |
|
|
create table primary_tbl
(profile id number,
primay_cust varchar2(10),
Primary_bus varchar2(10)
)/
insert into primary_tbl values(100,'Testing','Developer') /
insert into primary_tbl values(101,'Testing1','Developer1') /
create table sec_bus_tbl
(profile id number,
sec_bus varchar2(10)
)/
insert into sec_bus_tbl values(100,'Administrator') /
insert into sec_bus_tbl values(100,'Business') /
insert into sec_bus_tbl values(101,'Business1') /
create table sec_cust_tbl
(profile id number,
sec_cust varchar2(10)
)/
insert into sec_cust_tbl values(100,'Test') /
insert into sec_cust_tbl values(100,'Test_test') /
insert into sec_cust_tbl values(101,'Test_test1') /
I want the output for profile id 100 in this format:
primay_cust Primary_bus sec_bus sec_cust
-----------------------------------------------------
Testing Developer Administrator Test
NULL NULL Business Test_test
Thank you in advance,
Regards,
Samiran
|
|
|
|
|
|
| Re: How to remove the NULL values from SELECT clause [message #634424 is a reply to message #634421] |
Tue, 10 March 2015 01:42 |
|
samiran_cts
Messages: 52
Registered: January 2012
|
Member |
|
|
Sorry I made mistake while creating the script. Please ignore my previous script. I am very sorry.
create table primary_tbl
(profile_id number,
primay_cust varchar2(50),
Primary_bus varchar2(50)
)/
insert into primary_tbl values(100,'Testing','Developer') /
insert into primary_tbl values(101,'Testing1','Developer1') /
create table sec_bus_tbl
(profile_id number,
sec_bus varchar2(50)
)/
insert into sec_bus_tbl values(100,'Administrator') /
insert into sec_bus_tbl values(100,'Business') /
insert into sec_bus_tbl values(101,'Business1') /
create table sec_cust_tbl
(profile_id number,
sec_cust varchar2(50)
)/
insert into sec_cust_tbl values(100,'Test') /
insert into sec_cust_tbl values(100,'Test_test') /
insert into sec_cust_tbl values(101,'Test_test1') /
The output I want:
primay_cust Primary_bus sec_bus sec_cust
-----------------------------------------------------
Testing Developer Administrator Test
NULL NULL Business Test_test
Thanks ,
Samiran
|
|
|
|
| Re: How to remove the NULL values from SELECT clause [message #634432 is a reply to message #634424] |
Tue, 10 March 2015 02:14 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Read and answer John's post.
And you test case is still invalid: '/' should be alone on a new line or replace it by ';'.
And align the columns in your result; don't you see what a mess it is?
And where does 'Testing' come from?
And why there are no 'Test_test1', 'Business1', 'Developer1' in your result?
It is not sufficient to say "I want this output", you MUST explain the rules to go to this output.
[Updated on: Tue, 10 March 2015 02:15] Report message to a moderator |
|
|
|
| Re: How to remove the NULL values from SELECT clause [message #634437 is a reply to message #634432] |
Tue, 10 March 2015 03:04 |
|
samiran_cts
Messages: 52
Registered: January 2012
|
Member |
|
|
Basically I need a single query that will display primay_cust,Primary_bus,sec_bus,sec_cust for a profile id. The primay_cust,Primary_bus is present in the table primary_tbl, the sec_bus is present in the table sec_bus_tbl and sec_cust is present in the table sec_cust_tbl. I am again giving the script for the table structure.
create table primary_tbl
(profile_id number,
primay_cust varchar2(50),
Primary_bus varchar2(50)
);
insert into primary_tbl values(100,'Testing','Developer') ;
insert into primary_tbl values(101,'Testing1','Developer1') ;
create table sec_bus_tbl
(profile_id number,
sec_bus varchar2(50)
);
insert into sec_bus_tbl values(100,'Administrator') ;
insert into sec_bus_tbl values(100,'Business') ;
insert into sec_bus_tbl values(101,'Business1') ;
create table sec_cust_tbl
(profile_id number,
sec_cust varchar2(50)
);
insert into sec_cust_tbl values(100,'Test') ;
insert into sec_cust_tbl values(100,'Test_test') ;
insert into sec_cust_tbl values(101,'Test_test1') ;
Now I need to display the result for profile id 100. The Output should look like:
primay_cust Primary_bus sec_bus sec_cust
----------------------------------------------------------------
Testing Developer Administrator Test
NULL NULL Business Test_test
I tried in this way:select t1.primay_cust,t1.Primary_bus, t2.sec_bus, t3.sec_cust
from (select primay_cust,Primary_bus, rownum rn
from primary_tbl where profile_id=100
order by rowid) t1
full outer join
(select sec_bus, rownum rn
from sec_bus_tbl where profile_id=100
order by rowid) t2
on (t1.rn = t2.rn)
full outer join
(select sec_cust, rownum rn
from sec_cust_tbl where profile_id=100
order by rowid) t3
on (t2.rn = t3.rn)
I am getting the desired result but I don't want to use profile_id 100 3 times. So any better way to do so.
Thanks in advance
Regards,
Samiran
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to remove the NULL values from SELECT clause [message #634455 is a reply to message #634450] |
Tue, 10 March 2015 04:33 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If any order apply you don't need any ORDER BY clause in your query.
You also have to join on something else that uniquely define the row in each select.
And the latest test (t1.profile_id=100) is a WHERE clause of the outer query.
Something like:
SQL> SELECT T1.PRIMAY_CUST,T1.PRIMARY_BUS, T2.SEC_BUS, T3.SEC_CUST
2 from (select primay_cust,Primary_bus,profile_id,
3 row_number() over (partition by profile_id order by null) rn
4 from primary_tbl ) t1
5 full outer join
6 (select sec_bus, profile_id,
7 row_number() over (partition by profile_id order by null) rn
8 from sec_bus_tbl) t2
9 ON (T1.PROFILE_ID = T2.PROFILE_ID and t1.rn = t2.rn )
10 full outer join
11 (select sec_cust,profile_id,
12 row_number() over (partition by profile_id order by null) rn
13 from sec_cust_tbl) t3
14 ON (nvl(T1.PROFILE_ID,T2.profile_id) = T3.profile_id
15 and nvl(t1.rn,t2.rn) = t3.rn)
16 where coalesce(t1.profile_id,t2.profile_id,t3.profile_id)=100
17 /
PRIMAY_CUST PRIMARY_BUS SEC_BUS SEC_CUST
--------------- --------------- --------------- ---------------
Testing Developer Administrator Test
Business Test_test
2 rows selected.
You have to use analytic function because you want only one test on profile_id which is less efficient than 3.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 14:39:33 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
