1 2 Previous Next 22 Replies Latest reply: Mar 30, 2011 7:14 AM by 849327 RSS

    OUTER JOIN and OR Operator

    nazlfc
      Hi

      I wonder if someone can help me. I'm trying to bring back some data on a date period whilst using the or operator in the where clause with additional data using the same period but on another date. However, I also have left outer join in my query which I think is what is causing my data to return obscure results. Here is an example below:

      Select t.id,
      t.column 1,
      t.column 2,
      t.column 3,
      t.create_date,
      t.last_login
      from table t
      left outer join (select id, sys_connect_by(name, '~') link
      from table2 t2) t1
      where t.id = t1.id
      and t.create_date between to_char(sysdate-14), 'DD-MON-YY') and to_char(sysdate-1), 'DD-MON-YY')
      OR t.last_login between to_char(sysdate-14), 'DD-MON-YY') and to_char(sysdate-1), 'DD-MON-YY')

      Here I am expecting all data between the date ranges of create_date or date ranges between last login. However I am getting data outside these ranges which is not correct. Is there a problem with my SQL?

      I have read that the use of the OR operator where an outer join is used maybe causing is this correct?

      Your help and advice will be greatly appreciated.

      Thanks in advance.
        • 1. Re: OUTER JOIN and OR Operator
          849327
          This is a classic example of Operator precedence. AND has higher precedence as compared to OR. Use brackets to override the precedence.

          Try this...

          Untested!
          Select t.id,
          t.column 1,
          t.column 2,
          t.column 3,
          t.create_date,
          t.last_login
          from table t
          left outer join (select id, sys_connect_by(name, '~') link
          from table2 t2) t1 ON t.id = t1.id
          --where t.id = t1.id --I would recommend having this clause as part of the join
          WHERE 
          --Added brackets 
          (t.create_date between to_char((sysdate-14), 'DD-MON-YY') and to_char((sysdate-1), 'DD-MON-YY'))
          OR 
          --Added brackets
          (t.last_login between to_char((sysdate-14), 'DD-MON-YY') and to_char((sysdate-1), 'DD-MON-YY'))
          Hope it helps

          Regards,
          Rakesh

          Edited by: Rakesh Desai on Mar 28, 2011 10:41 PM
          • 2. Re: OUTER JOIN and OR Operator
            nagornyi
            Try
            ...
            where t.id = t1.id
            And 
            (
            T.Create_Date Between Sysdate-14 And Sysdate-1
            OR 
            t.last_login between Between Sysdate-14 And Sysdate-1
            )
            • 3. Re: OUTER JOIN and OR Operator
              nazlfc
              Hi Rakesh,

              Many thanks for the fast response. However, this has not worked. I am still getting data outside of the ranges for both create_date and last login.
              • 4. Re: OUTER JOIN and OR Operator
                Frank Kulash
                Hi,
                nazlfc wrote:
                Hi

                I wonder if someone can help me. I'm trying to bring back some data on a date period whilst using the or operator in the where clause with additional data using the same period but on another date. However, I also have left outer join in my query which I think is what is causing my data to return obscure results. Here is an example below:

                Select t.id,
                t.column 1,
                t.column 2,
                t.column 3,
                t.create_date,
                t.last_login
                from table t
                left outer join (select id, sys_connect_by(name, '~') link
                from table2 t2) t1
                where t.id = t1.id
                and t.create_date between to_char(sysdate-14), 'DD-MON-YY') and to_char(sysdate-1), 'DD-MON-YY')
                OR t.last_login between to_char(sysdate-14), 'DD-MON-YY') and to_char(sysdate-1), 'DD-MON-YY')

                Here I am expecting all data between the date ranges of create_date or date ranges between last login. However I am getting data outside these ranges which is not correct. Is there a problem with my SQL?
                Are you sure that's reaaly what you're running? I get an error if I say
                ...
                FROM          table t
                LEFT OUTER JOIN (
                                      SELECT  ...
                          ) t1
                WHERE   ...
                without an ON cluase after t1 and before WHERE.

                Post your actual code, not something that you suppose more or less kinda sorta resembles your code.
                Also, post some sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables, and also post the results you want from that data.
                Explain, using specific examples, how you get those results from that data.
                Always say which version of Oracle you're using.

                You may just need to replace the keyword WHERE with ON. Since that compound condition ainclude both AND and OR, you should have parentheses to make your meaning clear.
                Also, when you say SYS_CONNECT_BY, do you mean SYS_CONNECT_BY<b>_PATH</b>?  If so, don't you need a CONNECT BY clause in the same sub-query?

                What are the data types of create_date and last_login? If they are DATEs, then you shouldn't be using TO_CHAR to compare them. If they are not DATEs, that's an even bigger msitake.

                I suspect you want something like this:
                ...
                FROM          table t
                LEFT OUTER JOIN (
                                      SELECT  ...
                          ) t1
                ON     t.id = t1.id
                AND    (   t.create_date   BETWEEN SYSDATE - 14
                                                AND     SYSDATE -  1
                       OR  t.last_login    BETWEEN SYSDATE - 14
                                            AND        SYSDATE -  1
                       )
                but that's just a guess, and guessing isn't a very good way to solve problems. If you want something better than a guess, then post CREATE TABLE and INSERT statements for some sample data, and the results you want from that data.
                I have read that the use of the OR operator where an outer join is used maybe causing is this correct?
                That only applies to the old + notation, not to ANSI join syntax.
                • 5. Re: OUTER JOIN and OR Operator
                  nazlfc
                  Hi Frank,

                  Here is the actual code:

                  SELECT u.site_id,
                  u.id,
                  u.username,
                  u.email as,
                  u.org_id,
                  u.first_name,
                  u.last_name,
                  u.create_date,
                  u.last_updated_by,
                  u.last_update_date,
                  PATH,
                  u.last_login,
                  FROM users u
                  LEFT OUTER JOIN
                  (SELECT id,
                  sys_connect_by_path(Name,'~') path
                  FROM organization
                  START WITH parent_id IS NULL
                  CONNECT BY prior id = parent_id
                  ) o
                  ON u.org_id = o.id
                  WHERE (u.create_date between to_char(trunc(sysdate-14), 'DD-MON-YY') and to_char(trunc(sysdate-1), 'DD-MON-YY')
                  OR u.last_login between to_char(trunc(sysdate-14), 'DD-MON-YY') and to_char(trunc(sysdate-1), 'DD-MON-YY'))
                  ORDER BY u.last_login desc
                  • 6. Re: OUTER JOIN and OR Operator
                    849327
                    Did you try this as well??
                    from table2 t2) t1 ON t.id = t1.id
                    As pointed out by Frank, the query doesn't seems to be the one you are using actually.

                    You are not using data from table t1. You are not using it to restrict the rows (since you are using OUTER JOIN)
                    The table t1 seems to be redundant. Can you please post the actual code you are using?

                    Regards,
                    Rakesh

                    Edited by: Rakesh Desai on Mar 28, 2011 10:58 PM
                    • 7. Re: OUTER JOIN and OR Operator
                      nazlfc
                      Hi Rakesh,

                      Please see above.
                      • 8. Re: OUTER JOIN and OR Operator
                        nazlfc
                        Hi Frank,

                        Using your suggestion, it's still not working. Data outside the ranges specified for create date and last login are coming back..
                        • 9. Re: OUTER JOIN and OR Operator
                          Frank Kulash
                          Hi,
                          nazlfc wrote:
                          Hi Frank,

                          Using your suggestion, it's still not working. Data outside the ranges specified for create date and last login are coming back..
                          Actually, that wasn't my suggestion. That was my guess. See my last message for what you really need to do:
                          Frank Kulash wrote:
                          ... but that's just a guess, and guessing isn't a very good way to solve problems. If you want something better than a guess, then post CREATE TABLE and INSERT statements for some sample data, and the results you want from that data.
                          • 10. Re: OUTER JOIN and OR Operator
                            849327
                            Can you post some sample data and your desired output? It would be great if you can post the dates in your result set which are out of range as you specified.
                            • 11. Re: OUTER JOIN and OR Operator
                              nazlfc
                              Hi Rakesh

                              Here is what I am looking for (not probably in the sequence of my SQL in post above): I'm going to keep the first name, llast names and path column null for this demo but there are data. Below is demo data.


                              UserID, Username, Email, Firstname, Lastname, Date_Created, Last_Login, Path
                              1 ABC 1@1 24-MAR-11 26-MAR-11
                              2 CDA 2@1 14-MAR-11 18-MAR-11
                              3 BAG 3@1 26-MAR-11 NULL
                              4 CAR 4@1 20-MAR-11 25-MAR-11
                              5 LOD 5@1 15-MAR-11 20-MAR-11
                              6 POT 6@1 25-MAR-11 NULL


                              What I am trying to in my SQL is to return in the same report 1. All the users that have been created a specified date range 2. All users latest last login (if they have logged in) within the same date range period. Therefore, I had set a date range of between sysdate -15 and sysdate -2. I would have expected data upto the 27th March with data starting from the 14th March. However, I got only dates created if I was using the AND operator in the where clause. Using the OR operator the last logins were outside the above date range. Similarly if I switched to using the AND operator with last login and using the OR operator to filter dates created on the date range, I would users with their dates created outside the specified range.

                              Hope that's clear.

                              Thanks
                              • 12. Re: OUTER JOIN and OR Operator
                                849327
                                Hi nazlfc,

                                I think I got it...

                                The problem is with the way you are comparing the dates. You are trying to compare two strings instead of two dates


                                --YOUR SQL... See the use of to_char function
                                
                                WHERE (u.create_date between to_char(trunc(sysdate-14), 'DD-MON-YY') and to_char(trunc(sysdate-1), 'DD-MON-YY')
                                OR u.last_login between to_char(trunc(sysdate-14), 'DD-MON-YY') and to_char(trunc(sysdate-1), 'DD-MON-YY'))
                                By looking at your SQL, I am assuming the columns create_date and last_login are of data type VARCHAR2.

                                MODIFIED SQL
                                WHERE (TO_DATE(u.create_date,'DD-MON-YY') between trunc(sysdate-14) and trunc(sysdate-1))
                                OR(TO_DATE(u.last_login,'DD-MON-YY') between trunc(sysdate-14) and trunc(sysdate-1))
                                If columns create_date and last_login are of data type DATE, you can write this way...
                                WHERE (u.create_date between trunc(sysdate-14) and trunc(sysdate-1))
                                OR (u.last_login between trunc(sysdate-14) and trunc(sysdate-1))
                                Hope it helps...

                                Regards,
                                Rakesh




                                Edited by: Rakesh Desai on Mar 29, 2011 2:32 PM

                                Edited by: Rakesh Desai on Mar 29, 2011 2:34 PM

                                Edited by: Rakesh Desai on Mar 29, 2011 2:34 PM
                                • 13. Re: OUTER JOIN and OR Operator
                                  nazlfc
                                  Hi Rakesh,

                                  I'll give it a go but I suspect using to_char to convert date to varchar2 maybe causing the problem.

                                  Thanks for that.
                                  • 14. Re: OUTER JOIN and OR Operator
                                    849327
                                    By the way what is the data type of the columns create_date and last_login ?
                                    1 2 Previous Next