I’m converting a MySQL app to run on Azure and having an issue converting a query. The query runs fine in Microsoft SQL Server Management Studio, yet the app is reporting an error? How do I troubleshoot such an issue?
This is basically what I am doing
$SQL = "SELECT u.id, ISNULL(pd.Count, 0) ProjId_Count, (SELECT COUNT(projects_status_changes.ProjId) Count
FROM (
SELECT ProjectStatusChangeId, UserId, ProjId, Min(dtCreation) MindtCreation
FROM projects_status_changes psc
LEFT JOIN lst_projects_statuses lps ON psc.StatusId = lps.StatusId
WHERE lps.Status = :proj_status AND psc.StatusId_Old IS NULL
GROUP BY ProjectStatusChangeId, UserId, ProjId
) Minpsc
JOIN projects_status_changes ON projects_status_changes.ProjectStatusChangeId = Minpsc.ProjectStatusChangeId
WHERE Minpsc.MindtCreation BETWEEN :start_date AND :end_date) ProjId_TotalCount
FROM [user] u
LEFT JOIN (
SELECT projects_status_changes.UserId, COUNT(projects_status_changes.ProjId) Count
FROM (
SELECT ProjectStatusChangeId, UserId, ProjId, Min(dtCreation) MindtCreation
FROM projects_status_changes psc
LEFT JOIN lst_projects_statuses lps ON psc.StatusId = lps.StatusId
WHERE lps.Status = :proj_status AND psc.StatusId_Old IS NULL
GROUP BY ProjectStatusChangeId, UserId, ProjId
) Minpsc
JOIN projects_status_changes ON projects_status_changes.ProjectStatusChangeId = Minpsc.ProjectStatusChangeId
WHERE Minpsc.MindtCreation BETWEEN :start_date AND :end_date
GROUP BY projects_status_changes.UserId
) pd ON u.id = pd.UserId";
$params = [
':start_date' => $start,
':end_date' => $end,
':proj_status' => $status,
];
$connection = Yii::$app->getDb();
$command = $connection->createCommand($SQL, $params);
$queryResultsArray = $command->queryAll();
and the error is
SQLSTATE[07002]: [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error
The SQL being executed was: My SQL Statement