• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Converting JET SQL to T-SQL

Looking for some help converting the following JET SQL to T-SQL syntax:

SELECT vCTR.ID, vCTR.Agency_ID, vCTR.Status, vCTR.Update_Date, vCTR.Update_By, Users.Agency_ID, Users.UserName, IIf([vCTR]![Agency_ID]=[Users]![Agency_ID],"Yes","No") AS CorrectAssignment FROM Users INNER JOIN vCTR ON Users.UserName = vCTR.Update_By WHERE (((vCTR.Agency_ID)<>2 And (vCTR.Agency_ID)<>68 And (vCTR.Agency_ID)<>21 And (vCTR.Agency_ID)<>137 And (vCTR.Agency_ID)<>138 And (vCTR.Agency_ID)<>136 And (vCTR.Agency_ID)<>152 And (vCTR.Agency_ID)<>139) AND ((vCTR.Update_By)<>"whatever" And (vCTR.Update_By)<>"test") AND ((Users.Agency_ID)<>2 And (Users.Agency_ID)<>68 And (Users.Agency_ID)<>21 And (Users.Agency_ID)<>137 And (Users.Agency_ID)<>138 And (Users.Agency_ID)<>136 And (Users.Agency_ID)<>152 And (Users.Agency_ID)<>139) AND ((IIf([vCTR]![Agency_ID]=[Users]![Agency_ID],"Yes","No"))="No"))
ORDER BY vCTR.Update_Date DESC;
0
SimplyGeeky
Asked:
SimplyGeeky
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
Changing IIf to CASE...

SELECT vCTR.ID, vCTR.Agency_ID, vCTR.Status, vCTR.Update_Date, vCTR.Update_By, Users.Agency_ID,
      Users.UserName, 'No' AS CorrectAssignment

FROM Users INNER JOIN vCTR ON Users.UserName = vCTR.Update_By AND [vCTR].[Agency_ID]=[Users].[Agency_ID]

WHERE (((vCTR.Agency_ID)<>2 And (vCTR.Agency_ID)<>68 And (vCTR.Agency_ID)<>21 And (vCTR.Agency_ID)<>137 And (vCTR.Agency_ID)<>138 And (vCTR.Agency_ID)<>136 And (vCTR.Agency_ID)<>152 And (vCTR.Agency_ID)<>139) AND ((vCTR.Update_By)<>"whatever" And (vCTR.Update_By)<>"test") AND ((Users.Agency_ID)<>2 And (Users.Agency_ID)<>68 And (Users.Agency_ID)<>21 And (Users.Agency_ID)<>137 And (Users.Agency_ID)<>138 And (Users.Agency_ID)<>136 And (Users.Agency_ID)<>152 And (Users.Agency_ID)<>139))

ORDER BY vCTR.Update_Date DESC;
0
 
Patrick MatthewsCommented:
Typos...



SELECT vCTR.ID, vCTR.Agency_ID, vCTR.Status, vCTR.Update_Date, vCTR.Update_By, Users.Agency_ID,
      Users.UserName, 'No' AS CorrectAssignment

FROM Users INNER JOIN vCTR ON Users.UserName = vCTR.Update_By AND [vCTR].[Agency_ID]=[Users].[Agency_ID]

WHERE (((vCTR.Agency_ID)<>2 And (vCTR.Agency_ID)<>68 And (vCTR.Agency_ID)<>21 And (vCTR.Agency_ID)<>137 And (vCTR.Agency_ID)<>138 And (vCTR.Agency_ID)<>136 And (vCTR.Agency_ID)<>152 And (vCTR.Agency_ID)<>139) AND ((vCTR.Update_By)<>'whatever' And (vCTR.Update_By)<>'test') AND ((Users.Agency_ID)<>2 And (Users.Agency_ID)<>68 And (Users.Agency_ID)<>21 And (Users.Agency_ID)<>137 And (Users.Agency_ID)<>138 And (Users.Agency_ID)<>136 And (Users.Agency_ID)<>152 And (Users.Agency_ID)<>139))

ORDER BY vCTR.Update_Date DESC;
0
 
SimplyGeekyAuthor Commented:
What you have above didn't work.  Tried replacing IIf with Case statements with something like this:
(vCTR is actually CTR)

SELECT CTR.ID, CTR.Agency_ID, CTR.Status, CTR.Update_Date, CTR.Update_By, Users.Agency_ID, Users.UserName, CASE([CTR].[Agency_ID]=[Users].[Agency_ID],"Yes","No") AS CorrectAssignment FROM Users INNER JOIN CTR ON Users.UserName = CTR.Update_By WHERE (((CTR.Agency_ID)<>2 And (CTR.Agency_ID)<>68 And (CTR.Agency_ID)<>21 And (CTR.Agency_ID)<>137 And (CTR.Agency_ID)<>138 And (CTR.Agency_ID)<>136 And (CTR.Agency_ID)<>152 And (CTR.Agency_ID)<>139) AND ((CTR.Update_By)<>"whatever" And (CTR.Update_By)<>"test") AND ((Users.Agency_ID)<>2 And (Users.Agency_ID)<>68 And (Users.Agency_ID)<>21 And (Users.Agency_ID)<>137 And (Users.Agency_ID)<>138 And (Users.Agency_ID)<>136 And (Users.Agency_ID)<>152 And (Users.Agency_ID)<>139) AND ((CASE([CTR].[Agency_ID]=[Users].[Agency_ID],"Yes","No"))="No"))
ORDER BY CTR.Update_Date DESC;
0
 
SimplyGeekyAuthor Commented:
actually your syntax is wrong, it's missing case statements from the original
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now