Error with complex Oracle query

You have a WHERE clause outside of the subquery in your SELECT declaration.
I color coded it for you and explained that your footer code ADDs a new WHERE to the SQL.

You're misusing the SELECT by having a FROM and a WHERE in it.  The CI code expects that you'll use the SELECT for SELECT statements (and the FROM and WHERE clauses for those respective functions) - you're using it wrong.

Use it as designed and this problem will go away.

That's a more plausible response. Now I can move on to a different way of solving this issue.

How would I structure the code below using Active Record objects? I've tried a number of different ways, but to no avail. That's why I started using the query as stated in the original post, as shown below.

I understand how to setup the $this->db->select(), $this->db->from(), $this->db->join(), and $this->db->where() pieces, etc, but it's the embedded, added part (in red below) that breaks the query as a whole. I can't be the only one that's using complex queries with embedded select statements to populate a DataTable.

Did I explain what I'm trying to do well enough? I understand if you can't help. I can start a new thread that better describes my plight.

Thank you!  

select pu.username,

      pu.last_name,
      pu.first_name,
      pu.middle_name,
      ppi.passport_number,
      ps.passport_status_desc,
      pmax_status_date
    from
      passport_users pu,
         (select pi.username,
                 pi.passport_number,
                 max(pid.passport_status_date) pmax_status_date
          from passport_information pi,
               passport_information_detail pid    
          where pi.username = pid.username
          group by pi.username,
                   pi.passport_number) p_max_status,
       passport_information ppi,
       passport_information_detail ppid,
       passport_status ps
    where pu.username = p_max_status.username
       and pu.username = ppi.username
       and ppi.passport_number = p_max_status.passport_number
       and ppi.passport_number = ppid.passport_number
       and ppid.passport_status_date = p_max_status.pmax_status_date
       and ppid.passport_status = ps.passport_status
       and sysdate between ppi.passport_issue_date and NVL(ppi.passport_expiration_date, sysdate)", FALSE);