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