Using TRIM function with outer join 1

[hazelsisson]

Hi guys,

just a quick question (hopefully).

This is my desired query (simplified):

select p.dm_id, x.title 
from   post p, post_desc x
where  p.post_num = trim(x.post_no)(+)

But I get the error "ORA-00933: SQL command not properly ended" because of the use of the trim function before the (+).

Is there any way of using the trim function in this scenario, or will I need to pad out the other side of the join instead? The field x.post_no is always 12 characters long and is filled with spaces, whereas p.post_num is already "trimmed".

Many thanks,
Hazel

 

[Dagon]

Haven't got time to test it, but try this:

select p.dm_id, x.title
from post p, post_desc x
where p.post_num = trim(x.post_no(+))

 

[hazelsisson]

Brilliant, it worked!

So simple too, why didn't I think of that?

Thanks very much Dagon.

 

[SantaMufasa]

Hazel,

Something "Brilliant (and)...so simple too" deserves a

, don't you agree?

You can award a

by clicking "Thank Dagon for this valuable post".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hazelsisson]

Hi Santa,

I totally agree and usually do give someone a star.

However when I tried to yesterday I found that our network security guys had placed a restriction on it - maybe because it was classed as "voting" for something, I'm not sure. So instead of the usual popup I got a content violation message.

Would it be possible for you to give Dagon a star on my behalf?

Sorry about that - I really do appreciate the help!

Cheers
Hazel

 

[SantaMufasa]

Glad to oblige, Hazel.

Dagon, hava

from the both of us.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]