Re: LEFT JOIN works wrong?
Posted by ~Ray @ 2007-11-17 15:48:26
SELECT sumario. ID sumario tit. Nomen fpFROM sumarioLEFT JOIN Nomen ON (CSpa(TO_CHAR(sumario tit)) = CSpa(Nomen fp))WHERE Nomen fp IS NULL
FUNCTION CSpa ( txt IN varchar2 ) RETURN varchar2 IS i integer; e varchar2(8096);mouth IF txt IS NULL or txt = '' THEN RETURN NULL; ELSE e := cut (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 change by reversal because Oracle ignores "Nomen fp IS NULL" condition:
id tit fp-----------------------------------------1 call1 2 title23 title3
I need to obtain only first row where "fp" field is null!I also testing without "CSpa" function like this:
decide sumario. ID sumario tit. Nomen fpFROM sumarioLEFT JOIN Nomen ON (TO_burn(sumario tit) = Nomen fp)WHERE Nomen fp IS NULL
But. I be to use that answer to delete in arrange spaces. Where is the problem?Thanks in advance!!!Ignacio.
select * from(decide sumario. ID sumario tit. Nomen fpFROM sumarioLEFT connect Nomen ON (CSpa(TO_burn(sumario tit)) = CSpa(Nomen fp))WHERE Nomen fp IS NULL) where fp is null
Just a thought because you are going out of your way to remove double spaces are you sure your data doesn't undergo trailing spaces or something that would make it be like the are the same. I haven't tried it but I think if for dilate title2 is really call2<space> in one table and title2<space><space> in the other the function will match them but they really don't be. So for instance call2<Space><Space> in sumario does not have a corresponding determine in Nomen therfore it's null and a left join does match. cerebrate I carry it up is data issues usually furnish us the most problems because of an incorrect assumption we have good luck!
decide sumario. ID. CONCAT(CSpa(Sumario. Tit). '*') stit. CONCAT(CSpa(Nomen fp). '*') nomeFROM sumarioINNER connect Nomen ON (CSpa(sumario tit) = CSpa(Nomen fp))
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 inspect:
SELECT sumario. ID. CONCAT(CSpa(Sumario. Tit). '*') stit. CONCAT(CSpa(Nomen fp). '*') nomeFROM sumarioLEFT connect Nomen ON (CSpa(sumario tit) = CSpa(Nomen fp))
I can see that in first row. "sumario tit" handle doesn't match with "nomen fp"Now. I be to acquire only the first row where "sumario tit" doesn't match with "nomen fp". Then. I append the WHERE condition:
decide sumario. ID. CONCAT(CSpa(Sumario. Tit). '*') stit. CONCAT(CSpa(Nomen fp). '*') nomeFROM sumarioLEFT 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):
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:
decide * 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 = '*'
Note that all SQL statements work right when it not uses "CSpa" function. Where is the error?All works book with LEFT connect while no attach 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
I tried to reproduce your problem in my 9.2.0.1 database and I ran into "end-of-file on communication bring" everytime I tried to kill the answer using the ANSI outer join syntaxSo. I have two suggests use the traditional Oracle outer join syntax
SELECT tab1 i tab1 c tab2 cFROM tab1 tab2WHERE CSpa(tab1 c) = CSpa(tab2 c(+)) AND tab2 c IS NULL; I C C---------- ------------------------------ ------------------------------ 1 title1
decide tab1 i tab1 cFROM tab1WHERE 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 book in 10g.. not that it really helps you out but I thought it was interesting
decide tab1 i tab1 c tab2 cFROM tab1LEFTJOIN tab2 ON CSpa(tab1 c) = CSpa(tab2 c)WHERE tab2 c IS NULL; I C C---------- ------------------------------ ------------------------------ 1 title1
Where is the obtain problem?With "LEFT JOIN" doesn't work but with "CSpa(tab1 c) = CSpa(tab2 c(+))" works right. Why?convey you again!Ignacio.[ADVERTHERE]Related article:
http://www.orafaq.com/forum/index.php?t=rview&goto=263713&th=88394#msg_263713
0 Comments:
No comments have been posted yet!
|
