How to change this line of code to a WHERE clause?

Question

I have the following line of code in Oracle SQL:

LEFT OUTER JOIN lrmi_dba.rte_session_info si
          ON si.rte_session_id = TO_CHAR(USERENV('SESSIONID'))

I would like to change this part of the code to a WHERE clause, something like:

LEFT OUTER JOIN lrmi_dba.rte_session_info si
          ON si.rte_session_id 
WHERE si.rte_session_id = TO_CHAR(USERENV('SESSIONID'))

This does not work. Does anyone know how to do this please?

Thank-you!

You can use: "LEFT OUTER JOIN lrmi_dba.rte_session_info si ON 1 = 1 WHERE si.rte_session_id = TO_CHAR(USERENV('SESSIONID'))". But why? — Vadym Pechenoha, Jul 26 '17 at 21:30
the filter condition that you write in `ON` clause will take `ALL` results from the left table and only matching results from the `lrmi_dba.rte_session_info` table which in turn matches the passed on `session_id`. But when you say you want to move the condition to where clause then that means that first you get all the results from both the tables where `rte_session_id` is equal and then finally get only the results where the passed on session_id matches.. what exactly is the requirement.. — Deepshikha, Jul 26 '17 at 21:36
In short, those queries are different because of `NULL` . Effectively `LEFT OUTER JOIN lrmi_dba.rte_session_info si .. WHERE si.rte_session_id = ..` is an INNER JOIN. — Serg, Jul 26 '17 at 21:37
I'm not sure what your intentions are for putting the join clause in the where clause (although your query as it stands isn't actually "joining" the two tables together in the way that `on` is intended), but this question may be an interesting read: https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax — RToyo, Jul 26 '17 at 21:37

score 2 · Answer 1 · answered Jul 26 '17 at 21:47

If you use a WHERE clause you will turn the outer join to an inner join. So, put it in the ON clause:

 LEFT OUTER JOIN
 lrmi_dba.rte_session_info si
 ON si.rte_session_id AND
    si.rte_session_id = TO_CHAR(USERENV('SESSIONID'))

Well, that is how the code started out. Leave the condition in the ON clause.

score 0 · Answer 2 · answered Jul 26 '17 at 21:45

You have to connect the table to another table using a column with the same value (mostly a key column)

 LEFT OUTER JOIN lrmi_dba.rte_session_info si
   ON si.rte_session_id = another table's column
WHERE si.rte_session_id = TO_CHAR(USERENV('SESSIONID'))

SAMPLE

 FROM abcd.table1 t1
 LEFT OUTER JOIN lrmi_dba.rte_session_info si
   ON si.rte_session_id = t1.session_id
WHERE si.rte_session_id = TO_CHAR(USERENV('SESSIONID'))

score 0 · Answer 3 · answered Jul 26 '17 at 22:02

Perhaps you could use USING:

LEFT OUTER JOIN lrmi_dba.rte_session_info si
  USING (rte_session_id)
WHERE si.rte_session_id = TO_CHAR(USERENV('SESSIONID'))

This presumes that the table you're joining with lrmi_dba.rte_session_info also has a column named rte_session_id.

Best of luck.

score 0 · Answer 4 · answered Jul 27 '17 at 03:59

0

You can try like this:

LEFT OUTER JOIN lrmi_dba.rte_session_info si
      ON si.rte_session_id = si.rte_session_id WHERE si.rte_session_id = TO_CHAR(USERENV('SESSIONID'))

answered Jul 27 '17 at 03:59

Hong Van Vit

2,884
3
18
43

How to change this line of code to a WHERE clause?

4 Answers4