Oracle join not working

I am trying get all IFSC codes and details (bank unique code) from my bank master which starts with the first 4 characters of entered IFSC code. I have the bank master table which includes IFSC code(4 chars), corresponding bank details.

The main p开发者_运维问答art of the query is given below.

AND D.IFSC_CODE=UPPER(substr(B.BANK_CODE,1,4)) (+) ORDER BY....

When I execute this query, I am getting an error message "ORA-00936: missing expression".

What I am expecting from the query is:

• return the details if the bank exists in bank master corresponding to the entered IFSC code
• else only entered IFSC should display

When I rewrite the query like

AND D.IFSC_CODE(+) =UPPER(substr(B.BANK_CODE,1,4)) ORDER BY....

There is no error but the result was not what I expected.

How can I resolve this?

In a complex outer join expression you would put the (+) operator on all relevant columns, as in:

AND D.IFSC_CODE=UPPER(substr(B.BANK_CODE (+),1,4))

For example:

SQL> WITH table_a AS (
  2     SELECT '0001' ID FROM dual
  3     UNION ALL SELECT '0002' FROM dual
  4     UNION ALL SELECT '0003' FROM dual
  5  ), table_b AS (
  6     SELECT '0001a' ID FROM dual
  7     UNION ALL SELECT '0002b' FROM dual
  8  )
  9  SELECT a.id, b.id
 10    FROM table_a a, table_b b
 11   WHERE a.id = substr(b.id (+), 1, 4);

ID   ID
---- -----
0001 0001a
0002 0002b
0003 

This form of outer join is specific to Oracle and arguably more difficult to read than SQL ANSI outer join. Additionaly, some specific features are disabled with this old method (full outer join, outer join to more than one table). In SQL ansi join form, the query would look like:

SQL> WITH table_a AS (
  2     SELECT '0001' ID FROM dual
  3     UNION ALL SELECT '0002' FROM dual
  4     UNION ALL SELECT '0003' FROM dual
  5  ), table_b AS (
  6     SELECT '0001a' ID FROM dual
  7     UNION ALL SELECT '0002b' FROM dual
  8  )
  9  SELECT a.id, b.id
 10    FROM table_a a
 11    LEFT OUTER JOIN table_b b ON a.id = substr(b.id, 1, 4);

ID   ID
---- -----
0001 0001a
0002 0002b
0003