[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.