Subquery returned more than 1 value. This is not permitted when the su

[WayneGretzky]

Not sure why I am not able to check whether > 1 for 'error':

SELECT @RESULTS = ISNULL(@RESULTS + '', '') + CHAR(13) + 'Phone Number: ' + LEFT(PhoneNum, 3) + '-' + SUBSTRING(PhoneNum, 4, 3) + '-' + SUBSTRING(PhoneNum, 7, 4) + ' Call Date: ' + convert(varchar(20),CallDateTime,101) + ' HistoryID: ' + CONVERT(varchar(10), HistoryID) + CHAR(13) + CallData + CHAR(13) FROM dbo.History
WHERE ProjName IN ('TestIVR' , 'Main Test PBX')
AND (((SELECT CRC FROM dbo.History where CRC = 'error') > 1)) OR ((CRC = 'NOCPA') OR (CRC NOT IN ('AMM' , 'OK')))
AND CallDateTime between dateadd(mi, -20, GetDate()) and Getdate() /*checks for any records within the last 30 minutes of the current server's time*/
AND PhoneNum LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' /* ensures PhoneNum has 10 numbers for use in substring above */

 

[PHV]

Replace this:
SELECT CRC
By this:
SELECT COunt(*)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WayneGretzky]

Thanks, worked great. Can you possibly explain to me why I had to do it that way instead of reference the single fieldname?

 

[PHV]

Because a subquery used as a scalar value should return only one value (as clearly stated in the error message you posted).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886