Home » SQL & PL/SQL » SQL & PL/SQL » LEFT JOIN works wrong?
LEFT JOIN works wrong? [message #263631] Thu, 30 August 2007 09:43 Go to next message
inapal
Messages: 14
Registered: September 2006
Junior Member
Hi List,
I've a problem with the results returned by a Query aganst an Oracle 9i DB, using LEFT JOIN. The statement is:

SELECT sumario.ID, sumario.tit, Nomen.fp
FROM sumario
LEFT JOIN Nomen ON (CSpa(TO_CHAR(sumario.tit)) = CSpa(Nomen.fp))
WHERE Nomen.fp IS NULL


The "CSpa" user function is:

FUNCTION CSpa
  ( txt IN varchar2 )
  RETURN  varchar2 IS

    i integer;
    e varchar2(8096);
BEGIN
    IF txt IS NULL or txt = '' THEN
        RETURN NULL;
    ELSE
        e := trim (txt);
        while (INSTR(e, '  ') > 0) loop
            e := replace(e, '  ', ' ');
        end loop;

        RETURN e;
    END IF;
END;


"sumario.tit" is CLOB datatype, and Nomen.fp is varchar2 datatype.

The obtained result is the following, and is not correct, because Oracle ignores "Nomen.fp IS NULL" condition:

id        tit            fp
-----------------------------------------
1         title1 
2         title2
3         title3


If I delete this condition, and use a SQL like this:
SELECT sumario.ID, sumario.tit, Nomen.fp
FROM sumario
LEFT JOIN Nomen ON (CSpa(TO_CHAR(sumario.tit)) = CSpa(Nomen.fp))

then I obtain:

id        tit            fp
-----------------------------------------
1         title1 
2         title2               title2
3         title3               title3


I need to obtain only first row, where "fp" field is null!

I also testing without "CSpa" function, like this:
SELECT sumario.ID, sumario.tit, Nomen.fp
FROM sumario
LEFT JOIN Nomen ON (TO_CHAR(sumario.tit) = Nomen.fp)
WHERE Nomen.fp IS NULL


and results is correct!
id        tit            fp
-----------------------------------------
1         title1 


But, I need to use that function, to delete in string spaces.

Where is the problem?

Thanks in advance!!!
Ignacio.

[Updated on: Thu, 30 August 2007 09:47]

Report message to a moderator

Re: LEFT JOIN works wrong? [message #263646 is a reply to message #263631] Thu, 30 August 2007 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case that we can reproduce.
Post your Oracle version with 4 decimals.

Remove TO_CHAR, CLOB is already a CHAR.

Regards
Michel
Re: LEFT JOIN works wrong? [message #263675 is a reply to message #263631] Thu, 30 August 2007 13:12 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select * from
(SELECT sumario.ID, sumario.tit, Nomen.fp
FROM sumario
LEFT JOIN Nomen ON (CSpa(TO_CHAR(sumario.tit)) = CSpa(Nomen.fp))
WHERE Nomen.fp IS NULL
)
where fp is null
Re: LEFT JOIN works wrong? [message #263691 is a reply to message #263631] Thu, 30 August 2007 14:42 Go to previous messageGo to next message
gconner1997
Messages: 10
Registered: July 2006
Location: New York
Junior Member
Just a thought, because you are going out of your way to remove double spaces, are you sure your data doesn't have trailing spaces or something that would make it look like the are the same.

I haven't tried it, but I think if for instance title2 is really title2<space> in one table and title2<space><space> in the other, the function will match them, but they really don't match. So for instance title2<Space><Space> in sumario does not have a corresponding value in Nomen, therfore it's null and a left join does match.

Reason I bring it up is data issues usually give us the most problems because of an incorrect assumption we have.

good luck!
Re: LEFT JOIN works wrong? [message #263695 is a reply to message #263675] Thu, 30 August 2007 15:21 Go to previous messageGo to next message
inapal
Messages: 14
Registered: September 2006
Junior Member
I tested that alternative, but the error persists.

I checked with this case:

SELECT sumario.ID,  
    CONCAT(CSpa(Sumario.Tit), '*') stit,
    CONCAT(CSpa(Nomen.fp), '*') nome
FROM sumario
INNER JOIN Nomen ON (CSpa(sumario.tit) = CSpa(Nomen.fp))


The INNER JOIN works right, and I obtain:

ID        stit                 nome
-----------------------------------------
2         title2*              title2*
3         title3*              title3*


In fact, I'm sure that "sumario.tit" field matchs to "nomen.fp"

Then, I've change the join type (from INNER to LEFT), and I checked this another case:

SELECT sumario.ID,  
    CONCAT(CSpa(Sumario.Tit), '*') stit,
    CONCAT(CSpa(Nomen.fp), '*') nome
FROM sumario
LEFT JOIN Nomen ON (CSpa(sumario.tit) = CSpa(Nomen.fp))


The LEFT JOIN works right, and I obtain:

ID        stit                 nome
-----------------------------------------
1         title1*              *
2         title2*              title2*
3         title3*              title3*


I can see that in first row, "sumario.tit" field doesn't match with "nomen.fp"

Now, I need to retrieve only the first row, where "sumario.tit" doesn't match with "nomen.fp". Then, I append the WHERE condition:

SELECT sumario.ID,  
    CONCAT(CSpa(Sumario.Tit), '*') stit,
    CONCAT(CSpa(Nomen.fp), '*') nome
FROM sumario
LEFT JOIN Nomen ON (CSpa(sumario.tit) = CSpa(Nomen.fp))
WHERE Nomen.fp IS NULL


The obtained result is not correct(Oracle ignores "Nomen.fp IS NULL" condition):

ID        stit                 nome
-----------------------------------------
1         title1*              *
2         title2*              *
3         title3*              *

Furthermore, now the "nome" column retrieve nulls!! (only "*" char)

Look at this. I checked then with this statement, ommiting "Nomen.fp IS NULL" condition, and appending another filter:

SELECT * FROM (
    SELECT sumario.ID,  
        CONCAT(CSpa(Sumario.Tit), '*') stit,
        CONCAT(CSpa(Nomen.fp), '*') nome
    FROM sumario
    LEFT JOIN Nomen ON (CSpa(sumario.tit) = CSpa(Nomen.fp))
    )
WHERE nome = '*'


But I obtain the same wrong result:
ID        stit                 nome
-----------------------------------------
1         title1*              *
2         title2*              *
3         title3*              *


Note that all SQL statements work right when it not uses "CSpa" function.

Where is the error?
All works fine with LEFT JOIN, while no append WHERE conditions?

The Oracle is:
Oracle9i Enterprise Edition Release 9.2.0.1.0, running on Windows XP.

Any suggestion will be wellcome!!

Thank you

[Updated on: Thu, 30 August 2007 16:11]

Report message to a moderator

Re: LEFT JOIN works wrong? [message #263713 is a reply to message #263631] Thu, 30 August 2007 18:46 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
I tried to reproduce your problem in my 9.2.0.1 database, and I ran into "end-of-file on communication channel" everytime I tried to execute the function using the ANSI outer join syntax

So, I have two suggests, use the traditional Oracle outer join syntax
SELECT tab1.i, tab1.c, tab2.c
FROM   tab1, tab2
WHERE  CSpa(tab1.c) = CSpa(tab2.c(+))
  AND  tab2.c IS NULL;

         I C                              C
---------- ------------------------------ ------------------------------
         1 title1


Or use a subquery
SELECT tab1.i, tab1.c
FROM   tab1
WHERE  NOT EXISTS ( SELECT 1
                    FROM   tab2
                    WHERE  CSpa(TO_CHAR(tab1.c)) = CSpa(tab2.c) );

         I C                              C
---------- ------------------------------ ------------------------------
         1 title1


By the way, the function works fine in 10g...not that it really helps you out, but I thought it was interesting
SELECT tab1.i, tab1.c, tab2.c
FROM   tab1
LEFT
JOIN   tab2
  ON   CSpa(tab1.c) = CSpa(tab2.c)
WHERE  tab2.c IS NULL;

         I C                              C
---------- ------------------------------ ------------------------------
         1 title1
Re: LEFT JOIN works wrong? [message #263988 is a reply to message #263713] Fri, 31 August 2007 07:35 Go to previous messageGo to next message
inapal
Messages: 14
Registered: September 2006
Junior Member
It works!
Thank you!!!

SELECT tab1.i, tab1.c, tab2.c
FROM   tab1, tab2
WHERE  CSpa(tab1.c) = CSpa(tab2.c(+))
  AND  tab2.c IS NULL;

Where is the source problem?

With "LEFT JOIN" doesn't work, but with "CSpa(tab1.c) = CSpa(tab2.c(+))" works right. Why?

Thank you again!
Ignacio.
Re: LEFT JOIN works wrong? [message #264075 is a reply to message #263988] Fri, 31 August 2007 12:00 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Quote:
Where is the source problem?

No idea, I couldn't get it to work at all in 9i...perhaps a bug? The ANSI syntax was new in 9i. Like I said, it works fine in 10g.
Previous Topic: Trigger for restrictions on Database
Next Topic: Merge over Multiple Schemas
Goto Forum:
  


Current Time: Mon Dec 05 15:14:30 CST 2016

Total time taken to generate the page: 0.15500 seconds