I have a grid which has the following source query (don’t worry, you do not need to go through it in any detail). The point to focus on (I believe) is that:
[LIST=1]
SELECT
SUB_ProjName.TimesheetID,
SUB_ProjName.TimesheetName,
SUB_ProjName.AgencyID,
SUB_ProjName.ClientID,
SUB_ProjName.AgencyContactID,
SUB_ProjName.ClientContactID,
SUB_ProjName.WeekCommencing,
SUB_ProjName.ClientApprovedDate,
SUB_ProjName.SignedTimesheet,
SUB_ProjName.User,
SUB_ProjName.projectname,
SUB_ProjName.AgencyName,
SUB_ProjName.ClientName,
SUB_ProjName.ContactName
FROM
(SELECT
tbltimesheets.TimesheetID,
tbltimesheets.TimesheetName,
tbltimesheets.AgencyID,
tbltimesheets.ClientID,
tbltimesheets.AgencyContactID,
tbltimesheets.ClientContactID,
tbltimesheets.WeekCommencing,
tbltimesheets.ClientApprovedDate,
tbltimesheets.SignedTimesheet,
tbltimesheets.User,
GROUP_CONCAT( projectname SEPARATOR ' ' ) AS projectname,
tblagencies.AgencyName,
tblclients.ClientName,
tblclientcontacts.ContactName
FROM
tblprojects
INNER JOIN tblprojecthours
ON tblprojects.projectid = tblprojecthours.ProjectID
INNER JOIN tbltimesheets
ON tblprojecthours.TimesheetID = tbltimesheets.TimesheetID
INNER JOIN tblclientcontacts
ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID
INNER JOIN tblagencies
ON tbltimesheets.AgencyID = tblagencies.AgencyID
INNER JOIN tblclients
ON tbltimesheets.ClientID = tblclients.ClientID
AND tblclients.ClientID = tblclientcontacts.ClientID
GROUP BY
tbltimesheets.TimesheetID) SUB_ProjName
WHERE
SUB_ProjName.User = 'testdel'
ORDER BY
SUB_ProjName.WeekCommencing DESC
The bit that determines whether the WHERE is to be included or not is handled in the grid’s onScriptInit event, and if it is to be added, I use the following code:
if ({something} == 'this_or_that') {
sc_select_where(add) = "WHERE SUB_ProjName.User = 'testdel' ";
}
When I run the app I get 1054: Unknown column ‘SUB_ProjName.User’ in ‘where clause’.
When I look at the SQL that SC generates I see (look at the last bit):
(mysqlt): select count(*) from (SELECT SUB_ProjName.TimesheetID, SUB_ProjName.TimesheetName, SUB_ProjName.AgencyID,
SUB_ProjName.ClientID, SUB_ProjName.AgencyContactID, SUB_ProjName.ClientContactID, SUB_ProjName.WeekCommencing,
SUB_ProjName.ClientApprovedDate, SUB_ProjName.SignedTimesheet, SUB_ProjName.User, SUB_ProjName.projectname,
SUB_ProjName.AgencyName, SUB_ProjName.ClientName, SUB_ProjName.ContactName FROM (SELECT tbltimesheets.TimesheetID,
tbltimesheets.TimesheetName, tbltimesheets.AgencyID, tbltimesheets.ClientID, tbltimesheets.AgencyContactID, tbltimesheets.ClientContactID,
tbltimesheets.WeekCommencing, tbltimesheets.ClientApprovedDate, tbltimesheets.SignedTimesheet, tbltimesheets.User, GROUP_CONCAT(
projectname SEPARATOR ' ' ) AS projectname, tblagencies.AgencyName, tblclients.ClientName, tblclientcontacts.ContactName FROM tblprojects
INNER JOIN tblprojecthours ON tblprojects.projectid = tblprojecthours.ProjectID INNER JOIN tbltimesheets ON tblprojecthours.TimesheetID =
tbltimesheets.TimesheetID INNER JOIN tblclientcontacts ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID INNER JOIN
tblagencies ON tbltimesheets.AgencyID = tblagencies.AgencyID INNER JOIN tblclients ON tbltimesheets.ClientID = tblclients.ClientID AND
tblclients.ClientID = tblclientcontacts.ClientID GROUP BY tbltimesheets.TimesheetID) SUB_ProjName ) nm_sel_esp WHERE SUB_ProjName.User
= 'testdel'
It seems to me that the query is fine except for the red bit that SC adds near the end. I am only guessing here, but it looks like SC wants nm_sel_esp at the very END of the query - so that the last bit should look like this (with the close bracket moved too):
......... tblclientcontacts.ClientID GROUP BY tbltimesheets.TimesheetID) SUB_ProjName WHERE SUB_ProjName.User = 'testdel' ) nm_sel_esp
If that’s the case then SC has miscalculated where the query ends. And I think its the sub-query that’s confused the software. Is this a bug, or am I doing something stupid?
Thoughts folks?