I simply paste this SQL code that I've carefully crafted in SQL Server Management Studio and it works.
SELECT v.StudentID, v.StudentName, s.StudentHomeEmailAddress, s.StudentStudyYear, s.StudentMobilePhone, t.TradeName, p.ProgramName, (CASE WHEN s.StudentHomeEmailAddress IS NULL THEN 1 ELSE 0 END) AS Ord1
FROM vwCurrentStudents AS v
INNER JOIN tblStudents AS s ON s.StudentID = v.StudentID
INNER JOIN tblStudentProgramReg AS r ON r.StudentID = s.StudentID AND r.StudProgEnrolStatusID IN (1, 3)
INNER JOIN tblPrograms AS p ON r.ProgramID = p.ProgramID AND p.ProgramCatID IN (1, 3) AND p.ProgramID NOT IN (23, 112, 113)
INNER JOIN tblTrades AS t ON t.TradeID = p.TradeID
WHERE (@Who = 'Stu')
AND (s.StudentStudyYear LIKE @StudyYear)
AND (
(CASE WHEN (SELECT Item FROM dbo.fnMakeTableFromList(@Programs, ',') AS mkTblP WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(p.ProgramName))))) = p.ProgramName THEN 1 ELSE 0 END = 1 OR dbo.fnISEMPTY(@Programs) = 1 OR @Programs = '%')
AND (CASE WHEN (SELECT Item FROM dbo.fnMakeTableFromList(@Trades, ',') AS mkTblT WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(t.TradeName))))) = t.TradeName THEN 1 ELSE 0 END = 1 OR dbo.fnISEMPTY(@Trades) = 1 OR @Trades = '%')
)
ORDER BY Ord1, v.StudentName
However, if I paste it into the Query Builder, where it attempts to generate table links etc... the query changes to this!
SELECT v.StudentID, v.StudentName, s.StudentHomeEmailAddress, s.StudentStudyYear, s.StudentMobilePhone, t.TradeName, p.ProgramName,
(CASE WHEN s.StudentHomeEmailAddress IS NULL THEN 1 ELSE 0 END) AS Ord1
FROM vwCurrentStudents AS v INNER JOIN
tblStudents AS s ON s.StudentID = v.StudentID INNER JOIN
tblStudentProgramReg AS r ON r.StudentID = s.StudentID AND r.StudProgEnrolStatusID IN (1, 3) INNER JOIN
tblPrograms AS p ON r.ProgramID = p.ProgramID AND p.ProgramCatID IN (1, 3) AND p.ProgramID NOT IN (23, 112, 113) INNER JOIN
tblTrades AS t ON t.TradeID = p.TradeID
WHERE (@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
(SELECT Item
FROM dbo.fnMakeTableFromList(@Programs, ',') AS mkTblP
WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(p.ProgramName))))) = p.ProgramName THEN 1 ELSE 0 END = 1) AND
(CASE WHEN
(SELECT Item
FROM dbo.fnMakeTableFromList(@Trades, ',') AS mkTblT
WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(t .TradeName))))) = t .TradeName THEN 1 ELSE 0 END = 1) OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
(SELECT Item
FROM dbo.fnMakeTableFromList(@Trades, ',') AS mkTblT
WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(t .TradeName))))) = t .TradeName THEN 1 ELSE 0 END = 1) AND
(dbo.fnISEMPTY(@Programs) = 1) OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
(SELECT Item
FROM dbo.fnMakeTableFromList(@Trades, ',') AS mkTblT
WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(t .TradeName))))) = t .TradeName THEN 1 ELSE 0 END = 1) AND
(@Programs = '%') OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
(SELECT Item
FROM dbo.fnMakeTableFromList(@Programs, ',') AS mkTblP
WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(p.ProgramName))))) = p.ProgramName THEN 1 ELSE 0 END = 1) AND
(dbo.fnISEMPTY(@Trades) = 1) OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (dbo.fnISEMPTY(@Programs) = 1) AND (dbo.fnISEMPTY(@Trades) = 1) OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (@Programs = '%') AND (dbo.fnISEMPTY(@Trades) = 1) OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (CASE WHEN
(SELECT Item
FROM dbo.fnMakeTableFromList(@Programs, ',') AS mkTblP
WHERE (LTRIM(RTRIM(UPPER(Item))) = LTRIM(RTRIM(UPPER(p.ProgramName))))) = p.ProgramName THEN 1 ELSE 0 END = 1) AND
(@Trades = '%') OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (dbo.fnISEMPTY(@Programs) = 1) AND (@Trades = '%') OR
(@Who = 'Stu') AND (s.StudentStudyYear LIKE @StudyYear) AND (@Programs = '%') AND (@Trades = '%')
ORDER BY Ord1, v.StudentName
And it does not work. It's wrong. The placements of ORs and ANDs are wrong.
Is there an option or something I can do to stop this?
My only other option is to paste it into the ASP code view directly and carefully make sure it's SQL compliant as it does not like line breaks, ampersands, etc
Aucun commentaire:
Enregistrer un commentaire