SQL & PL/SQL

Hi there,

I have a rather simple task: retrieve all the records in a table, for agiven domain p_domain_id (input parameter). The problem is that there are about 6 FKs in the table, and I need the names (strings) corresponding to those FKs (from other tables). Unfortunately, some of the FKs are NULL, so in '=' I loose records. Without the last 2 lines in WHERE clause, I get the correct result. With d2 in place (and without the "(+)" ) I loose 2 records. With the d3 (and also without "(+)"), I do not get any record.

With the "(+)", the code compiles but I get the run time error ORA-01417
NOTE: I put the "+" within parentheses, in order to show it like a text in this editor.

What's an elegant solution to this?
Thanks a lot.

Here's the code:

SELECT
a.DOMAIN,
b.NAME,
a.DE_ID,
a.NAME,
a.PREFERRED_LABEL,
a.TECHNICAL_DEFINITION,
a.PUBLIC_DEFINITION,
a.DE_TYPE,
c1.NAME,
a.HAS_PARAMETER,
a.VALUE_CLASS,
c2.NAME,
a.INDEX_TERMS,
a.DATA_TABLE_ID,
d1.TABLE_NAME,
a.SP_INSERT,
a.SP_UPDATE,
a.SP_GET_BYMRN,
a.SP_GET_BYATTRIBUTE,
a.VALUE_TABLE_ID,
d2.TABLE_NAME,
a.PARAM_TABLE_ID,
d3.TABLE_NAME,
a.PARAM_DOMAIN_LOGIC,
a.SP_LOV,
a.LOWER_LIMIT,
a.UPPER_LIMIT,
a.BOOLEAN_Y,
a.BOOLEAN_N,
a.COMMENTS,
a.ENTERED_BY,
commons_API.get_person_full_name(a.ENTERED_BY),
a.ENTERED_ON
FROM
DATA_ELEMENT_INDEX a,
DE_DOMAIN b,
GENERAL_LIST c1,
GENERAL_LIST c2,
TABLE_GROUP d1,
TABLE_GROUP d2,
TABLE_GROUP d3
WHERE
DOMAIN = p_domain_id AND
b.DOMAIN_ID = a.DOMAIN AND
c1.ID = a.DE_TYPE AND
c2.ID = a.VALUE_CLASS AND
d1.TABLE_ID = a.DATA_TABLE_ID AND -- it works well without the next two lines
d2.TABLE_ID = a.VALUE_TABLE_ID "(+)" AND
d3.TABLE_ID = a.PARAM_TABLE_ID "(+)"
ORDER BY a.NAME;

Edited by: user10817976 on Oct 19, 2009 8:14 AM