Using upper() function in outerjoin [message #321384] |
Tue, 20 May 2008 02:24  |
prashas_d
Messages: 66 Registered: February 2007
|
Member |
|
|
Hi All,
I have two tables like
create table test_employee(a varchar2(10), b varchar2(10));
create table test_department(b varchar2(10), c varchar2(10));
And when I write a query as
select x.a, x.b, y.b, y.c from test_employee x, test_department y where x.b = y.b (+);
A B B C
---------- ---------- ---------- ----------
The query is executing without any error.
But when I rewrite the query using upper() function in outer join then its giving me a oracle error as follows.
select x.a, x.b, y.b, y.c from test_employee x, test_department y where upper(x.b) = upper(y.b) (+);
ORA-00936: missing expression
Can anyone please let me know whether there is any problem with the syntax that I am using in above query or we cannot use upper function during outerjoin?
Thanks in advance.
prashas_d.
|
|
|
|
Re: Using upper() function in outerjoin [message #321393 is a reply to message #321384] |
Tue, 20 May 2008 02:55   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
SQL> create table test_employee(a varchar2(10), b varchar2(10));
Table created.
SQL>
SQL> create table test_department(b varchar2(10), c varchar2(10));
Table created.
SQL> select x.a, x.b, y.b, y.c from test_employee x, test_department y where x.b = y.b (+);
no rows selected
You faked your results.
What you showed was the result of a full outer join, which is not possible using (+)
|
|
|
|
Re: Using upper() function in outerjoin [message #321549 is a reply to message #321393] |
Tue, 20 May 2008 13:08   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Frank wrote on Tue, 20 May 2008 00:55 |
You faked your results.
What you showed was the result of a full outer join, which is not possible using (+)
|
What results are you talking about? All I see are column headings followed by, "The query is executing without any error." It looks like what was left out was the insert statements.
SCOTT@orcl_11g> create table test_employee(a varchar2(10), b varchar2(10));
Table created.
SCOTT@orcl_11g> insert into test_employee values ('empa', 'empb');
1 row created.
SCOTT@orcl_11g> insert into test_employee values ('empa', 'b');
1 row created.
SCOTT@orcl_11g>
SCOTT@orcl_11g> create table test_department(b varchar2(10), c varchar2(10));
Table created.
SCOTT@orcl_11g> insert into test_department values ('deptb', 'deptc');
1 row created.
SCOTT@orcl_11g> insert into test_department values ('b', 'deptc');
1 row created.
SCOTT@orcl_11g>
SCOTT@orcl_11g>
SCOTT@orcl_11g> select x.a, x.b, y.b, y.c
2 from test_employee x, test_department y
3 where x.b = y.b (+);
A B B C
---------- ---------- ---------- ----------
empa b b deptc
empa empb
SCOTT@orcl_11g>
SCOTT@orcl_11g> select x.a, x.b, y.b, y.c
2 from test_employee x, test_department y
3 where upper(x.b) = upper(y.b) (+);
where upper(x.b) = upper(y.b) (+)
*
ERROR at line 3:
ORA-00936: missing expression
SCOTT@orcl_11g>
SCOTT@orcl_11g> select x.a, x.b, y.b, y.c
2 from test_employee x, test_department y
3 where upper(x.b) = upper(y.b(+));
A B B C
---------- ---------- ---------- ----------
empa b b deptc
empa empb
SCOTT@orcl_11g>
|
|
|
Re: Using upper() function in outerjoin [message #321601 is a reply to message #321549] |
Tue, 20 May 2008 17:21  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
You added a couple of inserts, which the original post lacked.
There is no way you can get an empty resultset without inserting a row of null-values in one of the tables.
I was referring to the fact that (in my view) the poster showed us the steps he took to get towards the result he showed.
|
|
|