Outer Join -- SQL

[amerbaig]

Am I allowed to use oute-join like this

Select Normalised_event_ID, A_Party_Id, B_Party_Id, Full_Path, Name from Normalised_event, tblOperator where Substr(B_Party_Id,1,length(code)) (+) = code and trim(full_path) = '107' order by Normalised_event_ID

It give me error ORA--00920 Invalid Operator

 

[rcurva]

If you did not create a TRIM() function then this could be your problem. You can use LTRIM(), RTRIM() instead to trim spaces from your column.

 

[rcurva]

My mistake, I just checked now. There is a trim() function in sql. SORRY, better get back to my SQL references.

 

[rcurva]

Can you post your table desc?

 

[amerbaig]

Description of two tables are --------

SQL> desc tbloperator;
Name Null? Type
----------------------------------------- -------- ----------------------------
CODE NOT NULL VARCHAR2(10)
NAME NOT NULL VARCHAR2(30)
ID NOT NULL NUMBER(10)

========================
SQL> desc normalised_event;
Name Null? Type
----------------------------------------- -------- ----------------------------
NORMALISED_EVENT_ID NOT NULL NUMBER(10)
LAST_MODIFIED NOT NULL DATE
PARENT_NORMALISED_EVENT_ID NUMBER(10)
NORMALISED_EVENT_FILE_ID NOT NULL NUMBER(8)
FILE_RECORD_NR NOT NULL NUMBER(10)
ORIGINAL_RECORD_NR NUMBER(10)
A_PARTY_ID VARCHAR2(40)
A_PARTY_NETWORK_ID VARCHAR2(40)
A_PARTY_NAME VARCHAR2(40)
A_PARTY_TON_CODE NUMBER(8)
A_PARTY_CELL_ID VARCHAR2(20)
A_PARTY_CARRIER_CODE NUMBER(8)
A_PARTY_LOCATION_CODE NUMBER(8)
A_PARTY_ROUTE VARCHAR2(80)
B_PARTY_ID VARCHAR2(40)
B_PARTY_NETWORK_ID VARCHAR2(40)
B_PARTY_NAME VARCHAR2(40)
B_PARTY_TON_CODE NUMBER(8)
B_PARTY_CELL_ID VARCHAR2(20)
B_PARTY_CARRIER_CODE NUMBER(8)
B_PARTY_LOCATION_CODE NUMBER(8)
B_PARTY_ROUTE VARCHAR2(80)
C_PARTY_ID VARCHAR2(40)
C_PARTY_NETWORK_ID VARCHAR2(40)
C_PARTY_NAME VARCHAR2(40)
C_PARTY_INTERNAL_ID NUMBER(10)
C_PARTY_TON_CODE NUMBER(8)
C_PARTY_CELL_ID VARCHAR2(20)
C_PARTY_CARRIER_CODE NUMBER(8)
C_PARTY_LOCATION_CODE NUMBER(8)
C_PARTY_ROUTE VARCHAR2(80)
FULL_PATH VARCHAR2(40)
CASCADE_CARRIER_CODE NUMBER(8)
SWITCH_START_DATE DATE
CHARGE_START_DATE DATE
PERIOD_START_DATE DATE
PERIOD_END_DATE DATE
EVENT_SOURCE VARCHAR2(40)
EVENT_CLASS_CODE NUMBER(2)
EVENT_TYPE_CODE NUMBER(8)
EVENT_SUB_TYPE_CODE NUMBER(8)
DURATION NUMBER
VOLUME NUMBER
PULSES NUMBER(8)
CHARGE NUMBER(22,6)
CURRENCY_ID NUMBER(8)
RATE_BAND VARCHAR2(40)
GENERAL_1 VARCHAR2(255)
GENERAL_2 VARCHAR2(255)
GENERAL_3 VARCHAR2(255)
GENERAL_4 VARCHAR2(255)
GENERAL_5 VARCHAR2(255)
GENERAL_6 VARCHAR2(255)
GENERAL_7 VARCHAR2(255)
GENERAL_8 VARCHAR2(255)
GENERAL_9 VARCHAR2(255)
GENERAL_10 VARCHAR2(255)
GENERAL_11 VARCHAR2(255)
GENERAL_12 VARCHAR2(255)
GENERAL_13 VARCHAR2(255)
GENERAL_14 VARCHAR2(255)
GENERAL_15 VARCHAR2(255)
GENERAL_16 VARCHAR2(255)
GENERAL_17 VARCHAR2(255)
GENERAL_18 VARCHAR2(255)
GENERAL_19 VARCHAR2(255)
GENERAL_20 VARCHAR2(255)
DUMMY VARCHAR2(1)
SNO NUMBER(30)

 

[cfsenorin]

Outer join cannot be used in arbitrary expression so I think what you can do is try this statement:

Select Normalised_event_ID, A_Party_Id, B_Party_Id, Full_Path, Name from Normalised_event, tblOperator where Substr(B_Party_Id,1,length(code)) = code(+) and trim(full_path) = '107' order by Normalised_event_ID

 

[rcurva]

Better change the text in BOLD:

Select Normalised_event_ID, A_Party_Id, B_Party_Id, Full_Path, Name
from Normalised_event, tblOperator
where Substr(B_Party_Id,1,length(code))(+) = code
and trim(full_path) = '107'
order by Normalised_event_ID

 

[sem]

You can not use operators in "optional" part of outer join. Try to modify your condition as
B_Party_Id(+) like code||'%'

If I understand your query it will do the same

 

[amerbaig]

None of the above solution/statement worked!!!!

 

[rcurva]

How about using NOT EXISTS instead;

Select Normalised_event_ID, A_Party_Id, B_Party_Id, Full_Path, Name
from Normalised_event, tblOperator
where Substr(B_Party_Id,1,length(code)) = code
and trim(full_path) = '107'
UNION ALL
Select NULL, NULL, NULL, NULL, Name
from tblOperator
WHERE not exists
( select null
from Normalised_event
where Substr(B_Party_Id,1,length(code)) = code
and trim(full_path) = '107' )
order by 1;

 

[sem]

Oracle SQL does not support trim() function, try to use ltrim(rtrim()). As for outer join if your CODE field does not contain special characters (%, _) my solution must work.

 

[lewisp]

Yes it does. Try

select trim(' hello ') from dual;

You get back 'hello' trimmed left and right.

 

[sem]

Oops, but it works only on 8i+.

 

[amerbaig]

For all of you, Normalised_event have 70000000 records.

 

[prashjha]

Try :
Select Normalised_event_ID,
A_Party_Id,
B_Party_Id,
Full_Path,
Name
from
Normalised_event,
tblOperator
where
Substr(B_Party_Id(+),1,length(code)) = code
and trim(full_path) = '107'
order by Normalised_event_ID

Do let know if it works ....

 

[amerbaig]

It returned me only matching records. Not an out-join effect.

Amer

 

[SpiritOfLennon]

I think the problem arises because if there is no match for code then the substr function does not work as it is being asked to take from position 1 for length unknown. The only way I can think of to get around this is to write a function that carries out the substr function and returns a valid value. If you need a hand with this let me know. SOL
The best thing about banging your head against a wall is when you stop.

 

[amerbaig]

I'll apperciate if you give me such function.

Thanks in Advance

 

[SpiritOfLennon]

Okay your function is;

FUNCTION substr_code
(
p_B_party_id in varchar2,
p_code in varchar2)

RETURN char IS

v_char1 char(1);
v_B_party_id varchar2(40);

BEGIN

if p_code is null
then
v_char1 := 6;
else
v_char1 := length(p_code);
end if;

v_B_party_id := substr(p_B_party_id, 1, v_char1);

RETURN v_B_party_id;

end;

and your query should then be
Select Normalised_event_ID,
A_Party_Id,
B_Party_Id,
Full_Path,
Name
from
Normalised_event,
tblOperator
where
substr_code(B_Party_Id(+),code) = code
and trim(full_path) = '107'
order by Normalised_event_ID

I have assigned an arbitrary length in the result of a null record set as the length in this case is academic. I haven't tested this as I have no data similar to what you are trying to achieve.
SOL
The best thing about banging your head against a wall is when you stop.

 

[sem]

I do apologize but what about using

B_Party_Id(+) like code||'%'

Doesn't it provide outer join with matches you need?
Any errors or incorrect result set?
Does your CODE field contain % or _ ?