Welcome Guest, Not a member yet? Register   Sign In
CodeIgniter Forums Archived Discussions Archived Development & Programming CI Database Class Changing my Query!
CI Database Class Changing my Query!
  • El Forum
    Guest
  •  
#1
07-16-2009, 08:51 AM

[eluser]whobutsb[/eluser]
Am I losing my mind? Or is CI f**king (excuse the profanity) with my query? BTW a week ago this was working, and when I did a restore from my subversion i'm still having issues!

So I have a fairly complex query that I'am trying to run for a report. Here is the query without using Active Record:

Code:
$SQL = "SELECT SUM(dbo.tblEventFinance.estimated_value) AS Value
FROM dbo.tblEvent
INNER JOIN dbo.tblEventLocation ON dbo.tblEvent.eventID = dbo.tblEventLocation.eventID
INNER JOIN dbo.tblEventFinance ON dbo.tblEvent.eventID = dbo.tblEventFinance.eventID
INNER JOIN dbo.tblLocation ON dbo.tblEventLocation.locationID = dbo.tblLocation.locationID WHERE (dbo.tblEvent.shortBU = 'BWR') AND (dbo.tblEvent.shortBU = 'BSC')
AND (dbo.tblEvent.shortBU = 'BHR') AND (dbo.tblEvent.shortBU = 'CHS')
AND (dbo.tblEvent.shortBU = 'CHI') AND (dbo.tblEvent.shortBU = 'PAR')
AND (dbo.tblEvent.shortBU = 'HEM') AND (dbo.tblEvent.shortBU = 'MOO')
AND (dbo.tblEvent.shortBU = 'NEG') AND (dbo.tblEvent.shortBU = 'NHC')
AND (dbo.tblEvent.shortBU = 'NYC') AND (dbo.tblEvent.shortBU = 'SMO')
AND (dbo.tblEvent.shortBU = 'TRI') AND (dbo.tblEvent.shortBU = 'WAT')
AND (dbo.tblEvent.eventstarttime BETWEEN CONVERT(DATETIME, '2009-01-01 12:00 AM', 102)
AND CONVERT(DATETIME, '2009-01-31 11:59 PM', 102)) AND (dbo.tblEvent.statusdescID = 1)
GROUP BY dbo.tblEventFinance.actualized_value, dbo.tblEvent.statusdescID
HAVING (dbo.tblEventFinance.actualized_value = 0 OR dbo.tblEventFinance.actualized_value IS NULL)";

When I run this within MS SQL Server Manager I get what i'm looking for.
When I try to run this query through Active Record, like this:

Code:
$query = $this->db->query($SQL);
return $query->result();

I receive a database error:

Code:
SELECT *, *, *, *, *, *, *, *, *, *, *, *, *, *,
SUM(DISTINCT dbo.tblEventFinance.actualized_value) AS Value
FROM tblEvent JOIN tblEventLocation ON tblEvent.eventID = tblEventLocation.eventID
JOIN tblEventFinance ON tblEvent.eventID = tblEventFinance.eventID
JOIN tblLocation ON tblEventLocation.locationID = tblLocation.locationID
WHERE shortBU = 'BWR' AND shortBU = 'BSC' AND shortBU = 'BHR' AND shortBU = 'CHS'
AND shortBU = 'CHI' AND shortBU = 'PAR' AND shortBU = 'HEM' AND shortBU = 'MOO'
AND shortBU = 'NEG' AND shortBU = 'NHC' AND shortBU = 'NYC' AND shortBU = 'SMO'
AND shortBU = 'TRI' AND shortBU = 'WAT' AND (tblEvent.eventstarttime
BETWEEN CONVERT(DATETIME, '2009-03-01 12:00 AM', 102)
AND CONVERT(DATETIME, '2009-03-31 11:59 PM', 102)) AND tblEvent.statusdescID = 9
AND (tblLocation.shortBU = 'BWR' OR tblLocation.shortBU = 'BSC' OR tblLocation.shortBU = 'BHR' OR tblLocation.shortBU = 'CHS' OR tblLocation.shortBU = 'CHI' OR tblLocation.shortBU = 'PAR' OR tblLocation.shortBU = 'HEM' OR tblLocation.shortBU = 'MOO' OR tblLocation.shortBU = 'NEG' OR tblLocation.shortBU = 'NHC' OR tblLocation.shortBU = 'NYC' OR tblLocation.shortBU = 'SMO' OR tblLocation.shortBU = 'TRI' OR tblLocation.shortBU = 'WAT')

Where the heck are all those Select Everything (*) coming from? I also built this query using Active Record and found that when I use a db->from('tblName'); or db->get('tblName'); I get all the select everythings, if i leave those methods out the query is correct is except for the missing FROM statement. Am I losing my mind and am I missing something with my query?

Thanks for the help.
  • El Forum
    Guest
  •  
#2
07-16-2009, 09:11 AM

[eluser]Michael Wales[/eluser]
What's the rest of your code look like - the code that actually executes the SQL command?
  • El Forum
    Guest
  •  
#3
07-16-2009, 09:15 AM

[eluser]whobutsb[/eluser]
I made some slight changes to the query, but it still gets what i'm looking for:

When I run this method:

Code:
function get_sales_revenue_forecast($BUarray = array(), $statusdescID, $dateRange = array()){
        $this->db->select_sum('dbo.tblEventFinance.estimated_value');
        $this->db->join('tblEventFinance', 'tblEvent.eventID = tblEventFinance.eventID');
        $this->db->join('tblEventLocation', 'tblEvent.eventID = tblEventLocation.eventID');
        $this->db->join('tblLocation', 'tblEventLocation.locationID = tblLocation.locationID');
        $whereDate = "(tblEvent.eventstarttime BETWEEN CONVERT(DATETIME, '$dateRange[start]', 102) AND CONVERT(DATETIME, '$dateRange[end]', 102))";
        $this->db->where($whereDate);
        $this->db->where('tblEventFinance.actualized_value', 0);
        $this->db->where('tblEventFinance.actualized_value IS NULL');
        foreach($BUarray as $BU){
            $whereBU[] = "tblLocation.shortBU = '$BU'";
        }
        $whereBU = implode(' OR ', $whereBU);
        $whereBU = '('. $whereBU .')';
        $this->db->where($whereBU);
        $this->db->group_by('dbo.tblEvent.statusdescID, dbo.tblEvent.eventstarttime, dbo.tblLocation.shortBU, dbo.tblEventFinance.actualized_value');
        $this->db->having("tblEvent.statusdescID = $statusdescID");
        $query = $this->db->get('tblEvent');
        return $query->result();
    }

I get this error:

Code:
A Database Error Occurred

Error Number: 209

SELECT *, *, *, *, *, *, *, *, *, *, *, *, *, *, SUM(dbo.tblEventFinance.estimated_value) AS estimated_value
FROM tblEvent JOIN tblEventFinance ON tblEvent.eventID = tblEventFinance.eventID JOIN tblEventLocation ON tblEvent.eventID = tblEventLocation.eventID JOIN tblLocation ON tblEventLocation.locationID = tblLocation.locationID WHERE shortBU = 'BWR' AND shortBU = 'BSC' AND shortBU = 'BHR' AND shortBU = 'CHS' AND shortBU = 'CHI' AND shortBU = 'PAR' AND shortBU = 'HEM' AND shortBU = 'MOO' AND shortBU = 'NEG' AND shortBU = 'NHC' AND shortBU = 'NYC' AND shortBU = 'SMO' AND shortBU = 'TRI' AND shortBU = 'WAT' AND (tblEvent.eventstarttime BETWEEN CONVERT(DATETIME, '2009-02-01 12:00 AM', 102) AND CONVERT(DATETIME, '2009-02-28 11:59 PM', 102)) AND tblEventFinance.actualized_value = 0 AND tblEventFinance.actualized_value IS NULL AND (tblLocation.shortBU = 'BWR' OR tblLocation.shortBU = 'BSC' OR tblLocation.shortBU = 'BHR' OR tblLocation.shortBU = 'CHS' OR tblLocation.shortBU = 'CHI' OR tblLocation.shortBU = 'PAR' OR tblLocation.shortBU = 'HEM' OR tblLocation.shortBU = 'MOO' OR tblLocation.shortBU = 'NEG' OR tblLocation.shortBU = 'NHC' OR tblLocation.shortBU = 'NYC' OR tblLocation.shortBU = 'SMO' OR tblLocation.shortBU = 'TRI' OR tblLocation.shortBU = 'WAT') GROUP BY dbo.tblEvent.statusdescID, dbo.tblEvent.eventstarttime, dbo.tblLocation.shortBU, dbo.tblEventFinance.actualized_value HAVING tblEvent.statusdescID = 1
The strangest thing is I can't figure out for the life of me why all of the *, *, ... are being added. I don't make any mention in the select statement to add select everything.
  • El Forum
    Guest
  •  
#4
07-16-2009, 10:44 AM

[eluser]whobutsb[/eluser]
So it looks like there is a issue with the: DB_active_rec.php class, function _compile_select() method, starting on line 1448:

Code:
if (count($this->ar_select) == 0)
            {
                $sql .= '*';        
            }

This is evaluating my code that $this->ar_select variable is empty. And then printing a whole bunch *.
  • El Forum
    Guest
  •  
#5
07-16-2009, 10:44 AM

[eluser]Hakkam[/eluser]
Hi,
Did in database config, dbo as your default db ?? Please try 'tbl.xxx' ... instead of 'dbo.tbl.xxx' ...
  • El Forum
    Guest
  •  
#6
07-16-2009, 10:48 AM

[eluser]whobutsb[/eluser]
I haven't run into any issues with using dbo or not using it. But I did remove the dbo. for all my queries.
  • El Forum
    Guest
  •  
#7
07-16-2009, 11:15 AM

[eluser]whobutsb[/eluser]
While Troubleshooting this issue I added some print_r to see where the query is screwing up:

Here is my code:
Code:
function get_sales_revenue_forecast($BUarray = array(), $statusdescID, $dateRange = array()){
        print_r($dateRange);
        echo "- $statusdescID";
        echo "<br /><br />";
        
        $this->db->select('SUM(tblEventFinance.estimated_value) AS Value');
        $this->db->join('tblEventFinance', 'tblEvent.eventID = tblEventFinance.eventID');
        $this->db->join('tblEventLocation', 'tblEvent.eventID = tblEventLocation.eventID');
        $this->db->join('tblLocation', 'tblEventLocation.locationID = tblLocation.locationID');
        $whereDate = "(tblEvent.eventstarttime BETWEEN CONVERT(DATETIME, '$dateRange[start]', 102) AND CONVERT(DATETIME, '$dateRange[end]', 102))";
        $this->db->where($whereDate);
        $this->db->where('tblEventFinance.actualized_value', 0);
        $this->db->where('tblEventFinance.actualized_value IS NULL');
        foreach($BUarray as $BU){
            $whereBU[] = "tblLocation.shortBU = '$BU'";
        }
        $whereBU = implode(' OR ', $whereBU);
        $whereBU = '('. $whereBU .')';
        $this->db->where($whereBU);
        $this->db->group_by('tblEvent.statusdescID, tblEventFinance.estimated_value, tblEvent.eventstarttime, tblLocation.shortBU, tblEventFinance.actualized_value');
        $this->db->having("tblEvent.statusdescID = $statusdescID");
        $query = $this->db->get('tblEvent');
        $results =  $query->result();
        
        print_r($results);
        echo "<br /><br />";
    }

And then the output is:

Code:
Array ( [start] => 2009-01-01 12:00 AM [end] => 2009-01-31 11:59 PM ) - 1

Array ( )

Array ( [start] => 2009-01-01 12:00 AM [end] => 2009-01-31 11:59 PM ) - 2

Array ( )

Array ( [start] => 2009-01-01 12:00 AM [end] => 2009-01-31 11:59 PM ) - 3

Array ( )

Array ( [start] => 2009-02-01 12:00 AM [end] => 2009-02-28 11:59 PM ) - 1

A Database Error Occurred

Error Number: 209

SELECT *, *, *, *, *, *, *, *, *, *, *, *, *, *, SUM(tblEventFinance.estimated_value) AS Value FROM tblEvent JOIN tblEventFinance ON tblEvent.eventID = tblEventFinance.eventID JOIN tblEventLocation ON tblEvent.eventID = tblEventLocation.eventID JOIN tblLocation ON tblEventLocation.locationID = tblLocation.locationID WHERE shortBU = 'HEM' AND shortBU = 'BHR' AND shortBU = 'BSC' AND shortBU = 'BWR' AND shortBU = 'CHI' AND shortBU = 'CHS' AND shortBU = 'MOO' AND shortBU = 'NEG' AND shortBU = 'NHC' AND shortBU = 'NYC' AND shortBU = 'PAR' AND shortBU = 'SMO' AND shortBU = 'TRI' AND shortBU = 'WAT' AND (tblEvent.eventstarttime BETWEEN CONVERT(DATETIME, '2009-02-01 12:00 AM', 102) AND CONVERT(DATETIME, '2009-02-28 11:59 PM', 102)) AND tblEventFinance.actualized_value = 0 AND tblEventFinance.actualized_value IS NULL AND (tblLocation.shortBU = 'HEM' OR tblLocation.shortBU = 'BHR' OR tblLocation.shortBU = 'BSC' OR tblLocation.shortBU = 'BWR' OR tblLocation.shortBU = 'CHI' OR tblLocation.shortBU = 'CHS' OR tblLocation.shortBU = 'MOO' OR tblLocation.shortBU = 'NEG' OR tblLocation.shortBU = 'NHC' OR tblLocation.shortBU = 'NYC' OR tblLocation.shortBU = 'PAR' OR tblLocation.shortBU = 'SMO' OR tblLocation.shortBU = 'TRI' OR tblLocation.shortBU = 'WAT') GROUP BY tblEvent.statusdescID, tblEventFinance.estimated_value, tblEvent.eventstarttime, tblLocation.shortBU, tblEventFinance.actualized_value HAVING tblEvent.statusdescID = 1

So it looks like this script is running through 3 times with no problem but when It gets to the fourth query it is barfing. I don't really know what would be changing besides my variable statusdescID.
  • El Forum
    Guest
  •  
#8
07-16-2009, 11:16 AM

[eluser]whobutsb[/eluser]
Even weirder!!! I didn't change anything but refresh the page and It successfully ran through 3 more times with out a problem.
  • El Forum
    Guest
  •  
#9
07-16-2009, 11:24 AM

[eluser]whobutsb[/eluser]
I just added the $this->db->last_query() function to my output: When I run my script I get this:

Code:
DateRange: Array ( [start] => 2009-01-01 12:00 AM [end] => 2009-01-31 11:59 PM )
StatusDescID: 1
Business Units Array: Array ( [0] => HEM [1] => BHR [2] => BSC [3] => BWR [4] => CHI [5] => CHS [6] => MOO [7] => NEG [8] => NHC [9] => NYC [10] => PAR [11] => SMO [12] => TRI [13] => WAT )
QUERY: SELECT SUM(tblEventFinance.estimated_value) AS Value FROM tblEvent JOIN tblEventFinance ON tblEvent.eventID = tblEventFinance.eventID JOIN tblEventLocation ON tblEvent.eventID = tblEventLocation.eventID JOIN tblLocation ON tblEventLocation.locationID = tblLocation.locationID WHERE (tblEvent.eventstarttime BETWEEN CONVERT(DATETIME, '2009-01-01 12:00 AM', 102) AND CONVERT(DATETIME, '2009-01-31 11:59 PM', 102)) AND tblEventFinance.actualized_value = 0 AND tblEventFinance.actualized_value IS NULL AND (tblLocation.shortBU = 'HEM' OR tblLocation.shortBU = 'BHR' OR tblLocation.shortBU = 'BSC' OR tblLocation.shortBU = 'BWR' OR tblLocation.shortBU = 'CHI' OR tblLocation.shortBU = 'CHS' OR tblLocation.shortBU = 'MOO' OR tblLocation.shortBU = 'NEG' OR tblLocation.shortBU = 'NHC' OR tblLocation.shortBU = 'NYC' OR tblLocation.shortBU = 'PAR' OR tblLocation.shortBU = 'SMO' OR tblLocation.shortBU = 'TRI' OR tblLocation.shortBU = 'WAT') GROUP BY tblEvent.statusdescID, tblEventFinance.estimated_value, tblEvent.eventstarttime, tblLocation.shortBU, tblEventFinance.actualized_value HAVING tblEvent.statusdescID = 1
OUTPUT: Array ( )



DateRange: Array ( [start] => 2009-01-01 12:00 AM [end] => 2009-01-31 11:59 PM )
StatusDescID: 2
Business Units Array: Array ( [0] => HEM [1] => BHR [2] => BSC [3] => BWR [4] => CHI [5] => CHS [6] => MOO [7] => NEG [8] => NHC [9] => NYC [10] => PAR [11] => SMO [12] => TRI [13] => WAT )
QUERY: SELECT SUM(tblEventFinance.estimated_value) AS Value FROM tblEvent JOIN tblEventFinance ON tblEvent.eventID = tblEventFinance.eventID JOIN tblEventLocation ON tblEvent.eventID = tblEventLocation.eventID JOIN tblLocation ON tblEventLocation.locationID = tblLocation.locationID WHERE (tblEvent.eventstarttime BETWEEN CONVERT(DATETIME, '2009-01-01 12:00 AM', 102) AND CONVERT(DATETIME, '2009-01-31 11:59 PM', 102)) AND tblEventFinance.actualized_value = 0 AND tblEventFinance.actualized_value IS NULL AND (tblLocation.shortBU = 'HEM' OR tblLocation.shortBU = 'BHR' OR tblLocation.shortBU = 'BSC' OR tblLocation.shortBU = 'BWR' OR tblLocation.shortBU = 'CHI' OR tblLocation.shortBU = 'CHS' OR tblLocation.shortBU = 'MOO' OR tblLocation.shortBU = 'NEG' OR tblLocation.shortBU = 'NHC' OR tblLocation.shortBU = 'NYC' OR tblLocation.shortBU = 'PAR' OR tblLocation.shortBU = 'SMO' OR tblLocation.shortBU = 'TRI' OR tblLocation.shortBU = 'WAT') GROUP BY tblEvent.statusdescID, tblEventFinance.estimated_value, tblEvent.eventstarttime, tblLocation.shortBU, tblEventFinance.actualized_value HAVING tblEvent.statusdescID = 2
OUTPUT: Array ( )



DateRange: Array ( [start] => 2009-01-01 12:00 AM [end] => 2009-01-31 11:59 PM )
StatusDescID: 3
Business Units Array: Array ( [0] => HEM [1] => BHR [2] => BSC [3] => BWR [4] => CHI [5] => CHS [6] => MOO [7] => NEG [8] => NHC [9] => NYC [10] => PAR [11] => SMO [12] => TRI [13] => WAT )
QUERY: SELECT SUM(tblEventFinance.estimated_value) AS Value FROM tblEvent JOIN tblEventFinance ON tblEvent.eventID = tblEventFinance.eventID JOIN tblEventLocation ON tblEvent.eventID = tblEventLocation.eventID JOIN tblLocation ON tblEventLocation.locationID = tblLocation.locationID WHERE (tblEvent.eventstarttime BETWEEN CONVERT(DATETIME, '2009-01-01 12:00 AM', 102) AND CONVERT(DATETIME, '2009-01-31 11:59 PM', 102)) AND tblEventFinance.actualized_value = 0 AND tblEventFinance.actualized_value IS NULL AND (tblLocation.shortBU = 'HEM' OR tblLocation.shortBU = 'BHR' OR tblLocation.shortBU = 'BSC' OR tblLocation.shortBU = 'BWR' OR tblLocation.shortBU = 'CHI' OR tblLocation.shortBU = 'CHS' OR tblLocation.shortBU = 'MOO' OR tblLocation.shortBU = 'NEG' OR tblLocation.shortBU = 'NHC' OR tblLocation.shortBU = 'NYC' OR tblLocation.shortBU = 'PAR' OR tblLocation.shortBU = 'SMO' OR tblLocation.shortBU = 'TRI' OR tblLocation.shortBU = 'WAT') GROUP BY tblEvent.statusdescID, tblEventFinance.estimated_value, tblEvent.eventstarttime, tblLocation.shortBU, tblEventFinance.actualized_value HAVING tblEvent.statusdescID = 3
OUTPUT: Array ( )

Continued on next post:
  • El Forum
    Guest
  •  
#10
07-16-2009, 11:26 AM

[eluser]whobutsb[/eluser]
This the final query with error message:

Code:
DateRange: Array ( [start] => 2009-02-01 12:00 AM [end] => 2009-02-28 11:59 PM )
StatusDescID: 1
Business Units Array: Array ( [0] => HEM [1] => BHR [2] => BSC [3] => BWR [4] => CHI [5] => CHS [6] => MOO [7] => NEG [8] => NHC [9] => NYC [10] => PAR [11] => SMO [12] => TRI [13] => WAT )
A Database Error Occurred

Error Number: 209

SELECT *, *, *, *, *, *, *, *, *, *, *, *, *, *, SUM(tblEventFinance.estimated_value) AS Value FROM tblEvent JOIN tblEventFinance ON tblEvent.eventID = tblEventFinance.eventID JOIN tblEventLocation ON tblEvent.eventID = tblEventLocation.eventID JOIN tblLocation ON tblEventLocation.locationID = tblLocation.locationID WHERE shortBU = 'HEM' AND shortBU = 'BHR' AND shortBU = 'BSC' AND shortBU = 'BWR' AND shortBU = 'CHI' AND shortBU = 'CHS' AND shortBU = 'MOO' AND shortBU = 'NEG' AND shortBU = 'NHC' AND shortBU = 'NYC' AND shortBU = 'PAR' AND shortBU = 'SMO' AND shortBU = 'TRI' AND shortBU = 'WAT' AND (tblEvent.eventstarttime BETWEEN CONVERT(DATETIME, '2009-02-01 12:00 AM', 102) AND CONVERT(DATETIME, '2009-02-28 11:59 PM', 102)) AND tblEventFinance.actualized_value = 0 AND tblEventFinance.actualized_value IS NULL AND (tblLocation.shortBU = 'HEM' OR tblLocation.shortBU = 'BHR' OR tblLocation.shortBU = 'BSC' OR tblLocation.shortBU = 'BWR' OR tblLocation.shortBU = 'CHI' OR tblLocation.shortBU = 'CHS' OR tblLocation.shortBU = 'MOO' OR tblLocation.shortBU = 'NEG' OR tblLocation.shortBU = 'NHC' OR tblLocation.shortBU = 'NYC' OR tblLocation.shortBU = 'PAR' OR tblLocation.shortBU = 'SMO' OR tblLocation.shortBU = 'TRI' OR tblLocation.shortBU = 'WAT') GROUP BY tblEvent.status

If there is only one variable changing why would it throw in all of the *, *, *,!!!! This is driving me bananas!!




Theme © iAndrew 2016 - Forum software by © MyBB