• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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