Home » SQL & PL/SQL » SQL & PL/SQL » Using upper() function in outerjoin
Using upper() function in outerjoin [message #321384] Tue, 20 May 2008 02:24 Go to next message
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 #321389 is a reply to message #321384] Tue, 20 May 2008 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
upper(y.b(+))

Regards
Michel
Re: Using upper() function in outerjoin [message #321393 is a reply to message #321384] Tue, 20 May 2008 02:55 Go to previous messageGo to next message
Frank
Messages: 7880
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 #321431 is a reply to message #321384] Tue, 20 May 2008 04:37 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Micheal,

Its working now. Now I am not getting any error message.

Thanks for the help Smile

prashas_d.

[Updated on: Tue, 20 May 2008 04:37]

Report message to a moderator

Re: Using upper() function in outerjoin [message #321549 is a reply to message #321393] Tue, 20 May 2008 13:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
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 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: retrieve duplicate rows from a query result
Next Topic: How to convert a file in one characterset to another using pl/sql
Goto Forum:
  


Current Time: Fri Dec 02 21:05:37 CST 2016

Total time taken to generate the page: 0.26077 seconds