CodeIgniter Forums
Error with complex Oracle query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Error with complex Oracle query (/showthread.php?tid=68609)

Pages: 1 2


Error with complex Oracle query - jace - 08-03-2017

I’m getting this error when trying an Oracle query inside a search function:
Code:
<div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">

<h4>A PHP Error was encountered</h4>

<p>Severity: Warning</p>
<p>Message:  oci_execute(): ORA-00936: missing expression</p>
<p>Filename: oci8/oci8_driver.php</p>
<p>Line Number: 286</p>

           <p>Backtrace:</p>
         
                     
                                   <p style="margin-left:10px">
                                   File: C:\xampp\htdocs\passport\application\models\Status_model.php<br />
                                   Line: 143<br />
                                   Function: get                           </p>
                                   <p style="margin-left:10px">
                                   File: C:\xampp\htdocs\passport\application\controllers\Status.php<br />
                                   Line: 29<br />
                                   Function: get_datatables                                 </p>

                     
                                   <p style="margin-left:10px">
                                   File: C:\xampp\htdocs\passport\index.php<br />
                                   Line: 304<br />
                                   Function: require_once                                   </p>

</div><br />
<b>Fatal error</b>:  Call to a member function result() on boolean in <b>C:\xampp\htdocs\passport\application\models\Status_model.php</b> on line <b>144</b><br />

<div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">

<h4>A PHP Error was encountered</h4>

<p>Severity: Error</p>
<p>Message:  Call to a member function result() on boolean</p>
<p>Filename: models/Status_model.php</p>
<p>Line Number: 144</p>


           <p>Backtrace:</p>

</div>

 
The query is as follows:
PHP Code:
       $this->db->select("select pu.username,
                          pu.last_name,
                          pu.first_name,
                          pu.middle_name,
                          ppi.passport_number,
                          ps.passport_status_desc,
                          passport.rowid as unique_id"
FALSE);

 
       $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"
);
 
       
        $this
->db->where("pu.username = p_max_status.username");
 
       $this->db->where("pu.username = ppi.username");
 
       $this->db->where("ppi.passport_number = p_max_status.passport_number");
 
       $this->db->where("ppi.passport_number = ppid.passport_number");
 
       $this->db->where("ppid.passport_status_date = p_max_status.pmax_status_date");
 
       $this->db->where("ppid.passport_status = ps.passport_status"); 


I also get the same error when doing it through a string variable query, such as:  $this->db->query($sql)


Thanks ahead of time. Any help or direction is much appreciate.

jACE


RE: Error with complex Oracle query - Kaosweaver - 08-04-2017

I'm not certain how Oracle does alias names, however, I do not see anything for the alias "pu" in the list of tables in the FROM declaration. I would assume that it is for the passport_users?

Maybe:
PHP Code:
$this->db->from("passport_users pu, (select pi.username, 



RE: Error with complex Oracle query - jace - 08-05-2017

Thanks for the reply. You are absolutely correct; however, after adding in the alias I still get the error. I get this whether I use a query string or Active Record. I believe the problem is in me using get() for the search functionality.

Ultimately, I want to be able to use the query string and still embed the query inside the _get_datatables_query() function. Active record would be fine, I just need to be able to utilize the query inside  the search functionality.

PHP Code:
<?php

class Status_model extends CI_Model {

   var $iTable 'PASSPORT.PASSPORT_INFORMATION_DETAIL';
   var $uTable 'PASSPORT.PASSPORT_USERS';
   var $sTable 'PASSPORT.PASSPORT_STATUS';
   // 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++;
       }

//        $sql = "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)";
       //      $query = $this->db->query($sql);


       $this->db->select("select pu.username,
                         pu.last_name,
                         pu.first_name,
                         pu.middle_name,
                         ppi.passport_number,
                         ps.passport_status_desc,
                         PASSPORT_USERS.ROWID as UNIQUE_ID"
FALSE);

       $this->db->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"
);
       
       $this
->db->where("pu.username = p_max_status.username");
       $this->db->where("pu.username = ppi.username");
       $this->db->where("ppi.passport_number = p_max_status.passport_number");
       $this->db->where("ppi.passport_number = ppid.passport_number");
       $this->db->where("ppid.passport_status_date = p_max_status.pmax_status_date");
       $this->db->where("ppid.passport_status = ps.passport_status");

       $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) - == $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();
       return $query->result();
   

Code:
<div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">

<h4>A PHP Error was encountered</h4>

<p>Severity: Warning</p>
<p>Message:  oci_execute(): ORA-00936: missing expression</p>
<p>Filename: oci8/oci8_driver.php</p>
<p>Line Number: 286</p>

    <p>Backtrace:</p>
    
            <p style="margin-left:10px">
            File: C:\xampp\htdocs\passport\application\models\Status_model.php<br />
            Line: 123<br />
            Function: get            </p>

            <p style="margin-left:10px">
            File: C:\xampp\htdocs\passport\application\controllers\Status.php<br />
            Line: 29<br />
            Function: get_datatables            </p>

            <p style="margin-left:10px">
            File: C:\xampp\htdocs\passport\index.php<br />
            Line: 304<br />
            Function: require_once            </p>

</div><br />
<b>Fatal error</b>:  Call to a member function result() on boolean in <b>C:\xampp\htdocs\passport\application\models\Status_model.php</b> on line <b>124</b><br />

<div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">

<h4>A PHP Error was encountered</h4>

<p>Severity: Error</p>
<p>Message:  Call to a member function result() on boolean</p>
<p>Filename: models/Status_model.php</p>
<p>Line Number: 124</p>

    <p>Backtrace:</p>
    
</div>



RE: Error with complex Oracle query - jace - 08-05-2017

(08-04-2017, 05:07 AM)Kaosweaver Wrote: I'm not certain how Oracle does alias names, however, I do not see anything for the alias "pu" in the list of tables in the FROM declaration. I would assume that it is for the passport_users?

Maybe:
PHP Code:
$this->db->from("passport_users pu, (select pi.username, 

Not sure why this forum posts my replies sometimes, and other times it flashes something about moderation and then I never see the reply to the thread. Let's try again.

You are right. I was missing the alias; however, after adding it I still get the error:
Code:
<div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">

<h4>A PHP Error was encountered</h4>

<p>Severity: Warning</p>
<p>Message:  oci_execute(): ORA-00936: missing expression</p>
<p>Filename: oci8/oci8_driver.php</p>
<p>Line Number: 286</p>

    <p>Backtrace:</p>
    
            <p style="margin-left:10px">
            File: C:\xampp\htdocs\passport\application\models\Status_model.php<br />
            Line: 123<br />
            Function: get            </p>

            <p style="margin-left:10px">
            File: C:\xampp\htdocs\passport\application\controllers\Status.php<br />
            Line: 29<br />
            Function: get_datatables            </p>

            <p style="margin-left:10px">
            File: C:\xampp\htdocs\passport\index.php<br />
            Line: 304<br />
            Function: require_once            </p>

</div><br />
<b>Fatal error</b>:  Call to a member function result() on boolean in <b>C:\xampp\htdocs\passport\application\models\Status_model.php</b> on line <b>124</b><br />

<div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">

<h4>A PHP Error was encountered</h4>

<p>Severity: Error</p>
<p>Message:  Call to a member function result() on boolean</p>
<p>Filename: models/Status_model.php</p>
<p>Line Number: 124</p>

    <p>Backtrace:</p>
    
</div>

<p>Message:  Call to a member function result() on boolean</p>

I believe it has to do with me using the $query = $this->db->get() of the search functionality in the get_datatables() function (see below); however, I can't see how to mix the two. In other words, I want to be able to call the query, but inside the _get_datatables_query() method for search functionality.

Thanks for your input...


PHP Code:
    var $iTable 'PASSPORT.PASSPORT_INFORMATION_DETAIL';
    var $uTable 'PASSPORT.PASSPORT_USERS';
    var $sTable 'PASSPORT.PASSPORT_STATUS';
    // 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++;
        }

//        $sql = "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)";
        //      $query = $this->db->query($sql);


        $this->db->select("select pu.username,
                          pu.last_name,
                          pu.first_name,
                          pu.middle_name,
                          ppi.passport_number,
                          ps.passport_status_desc,
                          PASSPORT_USERS.ROWID as UNIQUE_ID"
FALSE);

        $this->db->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"
);
        
        $this
->db->where("pu.username = p_max_status.username");
        $this->db->where("pu.username = ppi.username");
        $this->db->where("ppi.passport_number = p_max_status.passport_number");
        $this->db->where("ppi.passport_number = ppid.passport_number");
        $this->db->where("ppid.passport_status_date = p_max_status.pmax_status_date");
        $this->db->where("ppid.passport_status = ps.passport_status");

        $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) - == $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();
        return $query->result();
    



RE: Error with complex Oracle query - Kaosweaver - 08-07-2017

The error you're getting (ORA-00936c) is referenced here:
http://www.dba-oracle.com/sf_ora_00936_missing_expression.htm
to show that something is wrong with your SQL. While I'm looking at it, I'm seeing things I'm unsure of how they would operate for Oracle, since I'm a mySQL database guy, I see scope issues with the alias naming and I see a column aliased (passport_status ps) and then used as a prefix to another column (ps.passport_status_desc) - so, not sure about how that would work out.

But, I do know the GET is working because you're getting a valid error that shows that the SQL tried, but the Orcale engine threw an error that basically says (as far as I can tell), you're SQL needs work.

I would suggest you write out the SQL in the full text version and then use your database management tool and run that SQL to make sure it is working before you try to import it to the CI model code (this way we can eliminate CI from the problem and figure out if it is something with the SQL or something with CI.


RE: Error with complex Oracle query - jace - 08-07-2017

(08-07-2017, 05:52 AM)Kaosweaver Wrote: The error you're getting (ORA-00936c) is referenced here:
http://www.dba-oracle.com/sf_ora_00936_missing_expression.htm
to show that something is wrong with your SQL.  While I'm looking at it, I'm seeing things I'm unsure of how they would operate for Oracle, since I'm a mySQL database guy, I see scope issues with the alias naming and I see a column aliased (passport_status ps) and then used as a prefix to another column (ps.passport_status_desc) - so, not sure about how that would work out.

But, I do know the GET is working because you're getting a valid error that shows that the SQL tried, but the Orcale engine threw an error that basically says (as far as I can tell), you're SQL needs work.

I would suggest you write out the SQL in the full text version and then use your database management tool and run that SQL to make sure it is working before you try to import it to the CI model code (this way we can eliminate CI from the problem and figure out if it is something with the SQL or something with CI.

When I plug this Oracle query in Toad, it works perfectly.


Code:
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);



RE: Error with complex Oracle query - pporlan - 08-08-2017

Maybe CI is escaping something it doesn't need to, like this:

PHP Code:
       $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"
); 

Try showing what CI really sends to Oracle using this:

PHP Code:
echo $this->db->last_query(); 



RE: Error with complex Oracle query - jace - 08-08-2017

(08-08-2017, 03:40 AM)pporlan Wrote: Maybe CI is escaping something it doesn't need to, like this:

PHP Code:
       $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"
); 

Try showing what CI really sends to Oracle using this:

PHP Code:
echo $this->db->last_query(); 

I used last_query(), and don't understand how CI assumes this:


Code:
SELECT * FROM (SELECT inner_query.*, rownum rnum FROM (SELECT "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"
ORDER BY "LAST_NAME" ASC
) inner_query WHERE rownum < 11)


from this:


Code:
$this->db->select("select pu.last_name,
                          pu.first_name,
                          pu.middle_name,
                          ppi.passport_number", TRUE);

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

        $this->db->where("pu.username = p_max_status.username");
        $this->db->where("pu.username = ppi.username");
        $this->db->where("ppi.passport_number = p_max_status.passport_number");
        $this->db->where("ppi.passport_number = ppid.passport_number");
        $this->db->where("ppid.passport_status_date = p_max_status.pmax_status_date");
        $this->db->where("ppid.passport_status = ps.passport_status");



RE: Error with complex Oracle query - Paradinight - 08-08-2017

Try

PHP Code:
$query $this->db->query('YOUR QUERY HERE'); 



RE: Error with complex Oracle query - Kaosweaver - 08-09-2017

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

Remove the select, this should just be column names.

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