Using IN in the Select Clause
I have a Query which works fine in access but I am having a difficult time getting to work on the SQL2k server.
The query is as follows out of access:
SELECT DateValue([CRDateTime]) AS [Date], dbo_xxx.LD, dbo_xxx.Div, Sum(IIf([CRResultCode] In ('AN','ANS','BUS','HD','R04','R99','HB','HF'),1,0)) AS [NA], Sum(IIf([CRCallResultCode] In ('N21'),1,0)) AS [RC], Sum(IIf([CRResultCode] In ('E03','FXS','IDE','OP','OPS','IDL'),1,0)) AS [BN], Sum(IIf([CRResultCode] In ('MAS','MA','N27','N28','E18','R03'),1,0)) AS [AM], Sum(IIf([CRResultCode] In ('N25','N26'),1,0)) AS [AP], Sum(IIf([CRResultCode] In ('S29','S30','S31','S32','S24','S25','S28'),1,0)) AS [CP], Sum(IIf([CRResultCode] In ('N24'),1,0)) AS [PA], Sum(IIf([CRResultCode] In ('N29'),1,0)) AS [WPAO], Sum(IIf([CRResultCode] In ('E15','E16'),1,0)) AS [AD]
FROM dbo_xxx
WHERE (((dbo_xxx.CRID) Not In (2819,2820,2828,2830)))
GROUP BY DateValue([CRDateTime]), dbo_xxx.LD, dbo_xxx.Div
HAVING (((DateValue([CRDateTime]))=Date()))
ORDER BY dbo_xxx.LD, dbo_xxx.Div;
Any help would be greatly appreciated.
[1436 byte] By [
dagdamor67] at [2007-11-11 8:00:03]
# 1 Re: Using IN in the Select Clause
Dear dagdamor67,
The problem is not IN clause but problem is usage of following functions which is belong to MS-Access and you need to replace them with SQL Server function i.e. you have to modify your query as per SQL Sever Syntax:
1. DateValue - Use Convert function of SQL Server in place of this function.
2. IIF - Use CASE WHEN Statement of SQL Server in place of IIF.
I have modified your query for you but you have to verify it in SQL Server and make it running:
SELECT convert(varchar(10), [CRDateTime], 101) AS [Date], dbo_xxx.LD, dbo_xxx.Div, Sum((CASE WHEN ([CRResultCode] In ('AN','ANS','BUS','HD','R04','R99','HB','HF')) THEN 1 ELSE 0 END) ) AS [NA], Sum((CASE WHEN ([CRCallResultCode] In ('N21')) THEN 1 ELSE 0 END)) AS [RC], Sum((CASE WHEN([CRResultCode] In ('E03','FXS','IDE','OP','OPS','IDL')) THEN 1 ELSE 0 END)) AS [BN], Sum((CASE WHEN([CRResultCode] In ('MAS','MA','N27','N28','E18','R03')) THEN 1 ELSE 0 END)) AS [AM], Sum((CASE WHEN([CRResultCode] In ('N25','N26'),1,0)) THEN 1 ELSE 0 END)) AS [AP], Sum((CASE WHEN([CRResultCode] In ('S29','S30','S31','S32','S24','S25','S28')) THEN 1 ELSE 0 END)) AS [CP], Sum((CASE WHEN([CRResultCode] In ('N24')) THEN 1 ELSE 0 END)) AS [PA], Sum((CASE WHEN([CRResultCode] In ('N29')) THEN 1 ELSE 0 END)) AS [WPAO], Sum((CASE WHEN([CRResultCode] In ('E15','E16')) THEN 1 ELSE 0 END)) AS [AD]
FROM dbo_xxx
WHERE dbo_xxx.CRID Not In (2819,2820,2828,2830)
GROUP BY CONVERT(VARCHAR(10), [CRDateTime], 101), dbo_xxx.LD, dbo_xxx.Div
HAVING CONVERT(DATETIME, CONVERT(VARCHAR(10), [CRDateTime], 101))=CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 101))
ORDER BY dbo_xxx.LD, dbo_xxx.Div
Hope this will solve your problem.
