Im getting a subquery returned more than 1 value error. Please Help!

The error is occurring in the where clause that I spaced out down below, Its dealing with the State_No I know for a fact. I can't figure out how to run it in the Query anzlyzer to relate to match up with another table

PLEASE HELP!!!!!!

This is the error I'm getting in the DTS package i ran and query analyzer:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Update HBC_Boiler_Inspection
set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "

strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = " & rs1 ("State_No") & ") "try

Boiler_ID IN (Select ID from ...

caution: this will update all the boilers selected in the subquery

rudy

IIF Function in Dataset

I am trying to build my dataset WHERE clause with the IIF function.
I have 4 parameters that include a "ALL" option and that is what I am trying
to cater for.
Have done is successfully when there is only one parameter involved,
IIF (Parameters!Rep.Value = "0", " WHERE A.Region = '" &
Parameters!Region.Value & "'" , " WHERE A.Region = '" &
Parameters!Region.Value & "' AND A.SLPRSNID = '" & Parameters!Rep.Value &
"'")
but now I have multiple conditions ... have tried the following ...
" WHERE (CustomerServiceRatesReport.Region = @.Region) " &
IIF (Parameters!Rep.Value = "0", "" , " AND
CustomerServiceRatesReport.SLPRSNID = '" & Parameters!Rep.Value & "'") &
IIF (Parameters!Depot.Value = "0", "" , " AND
CustomerServiceRatesReport.OFFID = '" & Parameters!Depot.Value & "'") &
IIF (Parameters!ContractType.Value = "0", "" , " AND
CustomerServiceRatesReport.AV_Contract_Type = '" &
Parameters!ContractType.Value & "'") &
IIF (Parameters!CustomerNumber.Value = "0", "" , " AND
CustomerServiceRatesReport.CUSTNMBR = '" & Parameters!CustomerNumber.Value &
"'")
The above does not give me any errors, but neither does it give me any
results on the report - no matter what my parameter selections are.
Any help / pointers or suggestions on how I can make this work or
alternatives would be much appreciated.
Thank you,
SmeSme,
One thing that I do in these situations is place a textbox on my report
that contains the expression in your dataset, that way you can see what
SQL the dataset is executing.
Should give you a clue about what the query syntax ends up being after
all the expressions are evaluated.
Andy Potter|||Hi Andy,
Thank you for your reply. I will most certainly try that.
Is there any error in the syntax though?
What would the basic syntax be for a multiple IIF function in a query?
Kind Regards,
Sme|||Just in case someone needs the solution ... quite simple actually ...
WHERE (CustomerServiceRatesReport.Region = @.Region) AND
ISNULL(RTRIM(CustomerServiceRatesReport.SLPRSNID ),'') =COALESCE(@.Rep,RTRIM(CustomerServiceRatesReport.SLPRSNID ),'')
AND
ISNULL(RTRIM(CustomerServiceRatesReport.OFFID),'') =COALESCE(@.Depot,RTRIM(CustomerServiceRatesReport.OFFID ),'')
AND
ISNULL(RTRIM(CustomerServiceRatesReport.AV_Contract_Type),'') =COALESCE(@.ContractType,
RTRIM(CustomerServiceRatesReport.AV_Contract_Type),'')
AND
ISNULL(RTRIM(CustomerServiceRatesReport.CUSTNMBR),'') =COALESCE(@.CustomerNumber, RTRIM(CustomerServiceRatesReport.CUSTNMBR),'')