Substring outer join

[sammybee]

Hi ALl,

is it possible to do an outer join when the two fields linked are linked by substrings?

Cheers

Sam

 

[stefanhei]

Hi
It is with the following Syntax:

...AND SUBSTR(a.f(+),1,1) = b.f

Stefan

 

[sammybee]

Hi, thanks for help but using the following and have caused a cartesian. please help

AND SUBSTR (fielda, 5, 10) = SUBSTR (fieldb(+), 5, 10)

 

[SantaMufasa]

Sammy,

A true Cartesian product has no WHERE clause, but I presume you mean that you are getting more rows returned than you want/expected, right? Your query is still matching fielda's characters 5-14 with fieldb's characters 5-14, correct? If not, please post a copy-and-paste of both your code and the spurious results.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:13 (30Sep04) UTC (aka "GMT" and "Zulu"), 09:13 (30Sep04) Mountain Time)

 

[sammybee]

I realised the problem was duplicates in the first table so fixed with a distinct, my new problem with the code below is that in the case of substr(aun_code) I want to find those where they have additional characters at the end. please help! Code below:-

cheers Sam

SELECT DISTINCT (scr_prorefno_auncode), aun_code, aun_auy_code,
RTRIM (scr_prorefno_auncode)
FROM service_charge_rates, admin_units
WHERE scr_calcd_weighting_tot IS NULL
AND scr_scb_scp_start_date = '01-apr-2004'
AND SUBSTR (scr_prorefno_auncode, 5, 10) = SUBSTR(aun_code(+), 5,10)
AND scr_prorefno_auncode LIKE 'RPAU%'
ORDER BY scr_prorefno_auncode

 

[SantaMufasa]

Sammy,

Sorry, I must have taken my "dumb pills" this morning...I don't understand what you mean, "I want to find those where they have additional characters at the end." Can you please give examples of input values for "tablea" and "tableb" and the results you want to produce from those values?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:35 (30Sep04) UTC (aka "GMT" and "Zulu"), 09:35 (30Sep04) Mountain Time)

 

[sammybee]

Ok so Field A contains

aaaa45454

and field B

cccc45454
cccc45454A

I want the query to return both rows in field b

cheers

 

[SantaMufasa]

Sammy,

If you are interested simply in matching values that appear in characters 5-9, regardless of what may appear in characters prior or following 5-9, then wouldn't this code do what you want?:

...AND SUBSTR (fielda, 5, 5) = SUBSTR (fieldb(+), 5, 5)

Remember, the third argument of a substr function is not the ending column number, but the length in characters from the argument-2 starting position.

Does this resolve your need?

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:17 (30Sep04) UTC (aka "GMT" and "Zulu"), 10:17 (30Sep04) Mountain Time)

 

[sammybee]

This works, but does not find those with more than the selected characters

e.g.

(field A, 5, 10) = field B, 5, 10)

does not return field B with 11 characters.

Cheers

 

[sammybee]

Would I have to create a view with null characters?