Function & Join

[Matteo Gattoni]

Hello!
We have a problem with the following statement

SELECT
TRANSLATE_DICTIONARY_KEY(substr(rtrim(M3.N_ENTRY) ,1,100), 1, 
substr(rtrim(M3.N_ENTRY),1,100))
FROM
MENU M3 
inner join Menu m4 on (m3.k_entry=m4.k_entry)

It returns a error General error;-7015 More than one result row not allowed.

The function Translate_dictionary_key is the following

CREATE FUNCTION TRANSLATE_DICTIONARY_KEY(KEY CHAR(100), LANGUAGE FIXED(6,0), 
DEFAULT_WORD CHAR(100)) RETURNS VARCHAR AS
  VAR RES VARCHAR(4000);
TRY
  SET RES = DEFAULT_WORD;

 SELECT N_WORD 
 INTO :RES
      FROM PSF.DICTIONARY
     WHERE
           DATE(VALUE(DATE(D_END),ADDDATE(TIMESTAMP,1))) > DATE(TIMESTAMP) 
       AND DATE(D_START) <= DATE(TIMESTAMP) 
       AND K_LANGUAGE = :LANGUAGE
       AND N_KEY = :KEY;
 RETURN RES;

CATCH
 IF $RC = 100 THEN RETURN RES
 ELSE STOP($RC, $ERRMSG);
 RETURN RES;
//

and the tables are

CREATE TABLE MENU
 (K_ENTRY VARCHAR2(50) NOT NULL
 ,N_ENTRY VARCHAR2(250) NOT NULL
 ,F_HIDDEN CHAR(1) NOT NULL
 ,D_START DATE NOT NULL
 ,O_MODIFY DATE NOT NULL
 ,N_LINK VARCHAR2(250)
 ,K_PROG NUMBER(3,0)
 ,C_NOTE VARCHAR2(250)
 ,E_MENU_K_ENTRY VARCHAR2(50)
 ,E_MENU_K_ENTRY_SEE_ALSO VARCHAR2(50)
 ,E_OPERATOR_K_OPERATOR VARCHAR2(20) NOT NULL
, N_LOADER_CLASS VARCHAR2(250)
, N_ICON VARCHAR2(250)
, F_FUNCTION CHAR(1)
, N_PARAMS VARCHAR2(250)
, F_SEPARATOR VARCHAR2(1)
 ,D_END DATE
 ,C_TREE_PATH VARCHAR2(4000)
)

CREATE TABLE DICTIONARY
 (N_KEY VARCHAR(100) NOT NULL
 ,N_WORD VARCHAR2(4000) NOT NULL
 ,K_LANGUAGE NUMBER(6,0) NOT NULL
 ,D_START DATE NOT NULL
 ,O_MODIFY DATE NOT NULL
 ,E_OPERATOR_K_OPERATOR VARCHAR2(20) NOT NULL
 ,C_NOTE VARCHAR2(250)
 ,D_END DATE
 )

Note that the statement works if we delete the join as follow

SELECT
TRANSLATE_DICTIONARY_KEY(substr(rtrim(M3.N_ENTRY) ,1,100), 1, 
substr(rtrim(M3.N_ENTRY),1,100))
FROM
MENU M3 

and still doesn't work if we change the join as

SELECT
TRANSLATE_DICTIONARY_KEY(substr(rtrim(M3.N_ENTRY) ,1,100), 1, 
substr(rtrim(M3.N_ENTRY),1,100))
FROM
MENU M3 
cross join (select 1 from dual)

It seems that a join and using that function are incompatible.

Could you tell me what's wrong with it?

Thanks for your support.
Bye,
  Matteo