Welcome Guest, Not a member yet? Register   Sign In
Error with complex Oracle query
#11

(This post was last modified: 08-15-2017, 12:21 AM by jace.)

(08-09-2017, 10:49 AM)Kaosweaver Wrote:
Code:
$this->db->select("select pu.last_name,

Remove the select, this should just be column names.

Code:
$this->db->select("pu.last_name,

I figured out what is going on, but I'm not sure how to fix it.

The last_query() method is, in fact, returning what it thinks is the query, and I see where it's crashing. First let me show you part of my Status_model.
________________________________________________________________
<?php

 class Status_model extends CI_Model {

 

     var $iTable = 'PASSPORT.PASSPORT_INFORMATION_DETAIL';

     var $uTable = 'PASSPORT.PASSPORT_USERS';

     var $sTable = 'PASSPORT.PASSPORT_STATUS';

     var $sql;

     // order of columns

     var $column_order = array(

         'LAST_NAME',

         'FIRST_NAME',

         'MIDDLE_NAME',

         'PASSPORT_NUMBER'

     );

     // set the searchable columns

     var $column_search = array(

         'LAST_NAME',

         'FIRST_NAME',

         'MIDDLE_NAME',

         'PASSPORT_NUMBER'

     );

     // default sort column

     var $order = array(

         'LAST_NAME' => 'asc'

     );

 

     private function _get_datatables_query() {

         // individual column searching

         $column_count = 0;

         foreach ($this->column_search as $item) {

             if ($_POST['columns'][$column_count]['search']['value']) {

                 $this->db->like('LOWER(' . $item . ')', strtolower($_POST['columns'][$column_count]['search']['value'])); // fix to make case-insensitive

             }

 

             $column_count++;

         }

 

         $this->db->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);


         

         $i = 0;

 

         foreach ($this->column_search as $item) {

             if ($_POST['search']['value']) {

                 if ($i === 0) {

                     $this->db->group_start();

                     $this->db->like('LOWER(' . $item . ')', strtolower($_POST['search']['value'])); // fix to make case-insensitive

                 } else {

                     $this->db->or_like('LOWER(' . $item . ')', strtolower($_POST['search']['value'])); // fix to make case-insensitive

                 }

 

                 if (count($this->column_search) - 1 == $i)

                     $this->db->group_end();

             }

             $i++;

         }

 

         if (isset($_POST['order'])) {

             $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);

         } else if (isset($this->order)) {

             $order = $this->order;

             $this->db->order_by(key($order), $order[key($order)]);

         }

     }

 

     function get_datatables() {

         $this->_get_datatables_query();

         if ($_POST['length'] != -1)

             $this->db->limit($_POST['length'], $_POST['start']);

         $query = $this->db->get();

         //echo $this->db->last_query();

         return $query->result();

     }

 

     function count_filtered() {

         $this->_get_datatables_query();

         $query = $this->db->get();

         //echo $this->db->last_query();

         return $query->num_rows();

     }

 

     public function count_all() {

         $this->db->from($this->uTable);

         return $this->db->count_all_results();
     }

________________________________________________________________

Notice the $this->db->select piece in the _get_datatables_query() function (in blue). It works as expected and the DataTable gets loaded correctly; however, when you try to do a footer or page search, it crashes. If I do a last_query() and look at it from the search, it looks like this:

SELECT * FROM (SELECT inner_query.*, rownum rnum FROM (SELECT pu.last_name, pu.first_name, pu.middle_name, ppi.passport_number 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)
 WHERE LOWER(LAST_NAME) LIKE '%o%' ESCAPE '!'
 ORDER BY "LAST_NAME" ASC
 ) inner_query WHERE rownum < 11)

Now, look at the two WHERE clauses in bold (one lower case and one upper case). This can't work! That's broken sql query syntax for Oracle! If I take that query and run it in Toad, it breaks...like it should. But if I change the line from this:

WHERE LOWER(LAST_NAME) LIKE '%o%' ESCAPE '!'

to this:

AND LOWER(LAST_NAME) LIKE '%o%' ESCAPE '!'

It works!

It appears what's causing that is the code above in red. The problem is that I don't know how to get around it or fix it.

Does all this make sense? I hope I've explained it well enough.
 
Thanks...
Reply
#12

In blue in your post is a $this->db->select() where you have a FROM and WHERE
Then you have a $this->db->where() which appends the SELECT with "WHERE..." giving you the two WHEREs in the SQL.

Take out all of the WHERE commands and FROM commands within the SELECT command - and use the respective $this->db->from() and $this->db>where() to construct those.
Reply
#13

(08-15-2017, 05:04 AM)Kaosweaver Wrote: In blue in your post is a $this->db->select() where you have a FROM and WHERE
Then you have a $this->db->where() which appends the SELECT with "WHERE..." giving you the two WHEREs in the SQL.

Take out all of the WHERE commands and FROM commands within the SELECT command - and use the respective $this->db->from() and $this->db>where() to construct those.

I don't think the SQL in the $this->db->select() is the problem. Yes, there are two WHERE clauses, but the one is embedded in parenthesis with it's own select. That works fine. It's when I do a search.

The other WHERE is coming from this part of the code that I posted, outside of the $this->db->select(). Specifically the first like:

foreach ($this->column_search as $item) {
             if ($_POST['search']['value']) {
                 if ($i === 0) {
                     $this->db->group_start();
                     $this->db->like('LOWER(' . $item . ')', strtolower($_POST['search']['value'])); // fix to make case-insensitive
                 } else {
                     $this->db->or_like('LOWER(' . $item . ')', strtolower($_POST['search']['value'])); // fix to make case-insensitive
                 }

I've tried using $this->db->where() and $this->db->from(), etc, to construct this, but it always points back to the same error. I know there's a way to group the like clauses into the query I have, to where it's expressed as an AND instead of an additional WHERE, but I haven't figured it out yet.

Thank you...
Reply
#14

$this->db->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);

---------------------
I don't think it is embedded...
Reply
#15

(This post was last modified: 08-16-2017, 11:42 PM by jace.)

(08-16-2017, 06:06 AM)Kaosweaver Wrote: $this->db->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);

---------------------
I don't think it is embedded...

Then why when I run the query in Toad for Oracle, it works fine. It also works fine in CI, with the exception of when I try to do a footer search.

The following function is called when doing a footer search:

         foreach ($this->column_search as $item) {
             if ($_POST['search']['value']) {
                 if ($i === 0) {
                     $this->db->group_start();
                     $this->db->like('LOWER(' . $item . ')', strtolower($_POST['search']['value']));
                 } else {
                     $this->db->or_like('LOWER(' . $item . ')', strtolower($_POST['search']['value']));
                 }

                 if (count($this->column_search) - 1 == $i)
                     $this->db->group_end();
             }
             $i++;
         }

And by looking at the results from last_query() when I try the footer search, the piece in red is appended. Do you see the difference?

SELECT * FROM (SELECT inner_query.*, rownum rnum FROM (SELECT pu.last_name, pu.first_name, pu.middle_name, ppi.passport_number 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)
 WHERE LOWER(LAST_NAME) LIKE '%o%' ESCAPE '!'
 ORDER BY "LAST_NAME" ASC
 ) inner_query WHERE rownum < 11)

Now if I change the red WHERE above to an AND, once again it works in Toad for Oracle.
Reply
#16

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.
Reply
#17

(08-17-2017, 05:23 AM)Kaosweaver Wrote: 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);
Reply
#18

When I have very complex queries using subqueries, I just use a string and assemble the query and then use the $this->db->query() to make it work.

In your case, since it appears the $this->db->select will take the FROM clause and parse it (which is still wrong, but it is a "feature" not a "bug" that it works...) - you could just use the existing code as you have it, take out just the WHERE statements and put those in the $this->db->where() statements which *should* append correctly for you.

I'd assemble a sql string and then just pass it to the db->query function. There is nothing magical about the active record code that makes it superior to straight SQL. (apart from filtering/security - but your query has no input/variables in to it)
Reply
#19

(This post was last modified: 04-16-2018, 02:21 AM by ciadmin. Edit Reason: SEO link redacted )

$this->db->from("passport_users, (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");
Reply




Theme © iAndrew 2016 - Forum software by © MyBB