[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.
[eluser]Michael Wales[/eluser]
What's the rest of your code look like - the code that actually executes the SQL command?
[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.
[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 *.
[eluser]Hakkam[/eluser]
Hi,
Did in database config, dbo as your default db ?? Please try 'tbl.xxx' ... instead of 'dbo.tbl.xxx' ...
[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.
[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.
[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.
[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:
[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!!
|