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 Statements

On Apr 24, 8:42 am, RSub <R...@.discussions.microsoft.com> wrote:
> Hi All,
> The below IIF statement is not working for me.
> =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> "Address")
>
> Could you please let me know where I am going wrong. I tried several other
> options such as writing custom code, switch, choose statements..I am
> migrating the report from crystal reports to Reporting services. Instead of
> the format in the above expression it was AddressLine1 in Crystal reports
> that had worked fine. Also does anybody know of an alternative for the
> NameFlip function of crystal rpts to use in Reporting svcs'
>
> Thanks in advance,
> RS
That IIF() call is pretty complex - is it possible for you to put some
of this logic in the database layer (e.g. by calling a view)?Thank you for your reply. I actually removed the variable "Address" and the
OR from the IIF statement and it is working fine now. Looks like IIF doesn't
work well with variables and those logical operators.
"Tokes" wrote:
> On Apr 24, 8:42 am, RSub <R...@.discussions.microsoft.com> wrote:
> > Hi All,
> > The below IIF statement is not working for me.
> > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> > "Address")
> >
> > Could you please let me know where I am going wrong. I tried several other
> > options such as writing custom code, switch, choose statements..I am
> > migrating the report from crystal reports to Reporting services. Instead of
> > the format in the above expression it was AddressLine1 in Crystal reports
> > that had worked fine. Also does anybody know of an alternative for the
> > NameFlip function of crystal rpts to use in Reporting svcs'
> >
> > Thanks in advance,
> > RS
> That IIF() call is pretty complex - is it possible for you to put some
> of this logic in the database layer (e.g. by calling a view)?
>|||IIF() works well "with logical operators and variables", FWIW.
So, here's a guess about why it didn't work, without reading your expression
very closely:
Assuming there was no actual error on your part, it's possible that Crystal
Reports interpreted the segments of your expression in a different order
than RS is doing. (Different compilers are like that <g>.)
To resolve this you can usually add some nested parentheses to make sure
that the order of evaluation is exactly what you expect, explicitly defined,
even though you got this order by default in your old environment.
However... a piece of advice: if you find yourself writing something like
this you may find it worth your while to write a little VB custom function
instead (embed it in the report) and then invoke the function
(=Code.MyFunc()) rather than writing the expression correctly. It's a lot
easier to read and maintain.
Also, you asked a second question about NameFlip... Does this flip two
values based on the appearance of a comma or something? I'm just guessing by
the name, but if so, something like this should work for you:
Function NameFlip(ByVal LastFirst As String) As String
Dim Result As String, Results As String()
Results = LastFirst.Split(",")
If Results.Length = 2 Then
Result = Results(1).Trim() & " " & Results(0).Trim()
Else
' don't make any assumptions if there are
' no commas or more than one comma
Result = LastFirst
End If
Results = Nothing
Return Result
End Function
If I guessed wrong, ask again, and I'll try to write something appropriate
<s>.
Hope this helps,
>L<
"RSub" <RSub@.discussions.microsoft.com> wrote in message
news:EDEAAEF8-8CD1-4518-9AE3-56FE9E714C50@.microsoft.com...
> Thank you for your reply. I actually removed the variable "Address" and
> the
> OR from the IIF statement and it is working fine now. Looks like IIF
> doesn't
> work well with variables and those logical operators.
> "Tokes" wrote:
>> On Apr 24, 8:42 am, RSub <R...@.discussions.microsoft.com> wrote:
>> > Hi All,
>> > The below IIF statement is not working for me.
>> > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
>> > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " "
>> > &
>> > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value &
>> > " " &
>> > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
>> > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " &
>> > Fields!State_2.Value
>> > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value),
>> > Trim("Address")
>> > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United
>> > States",
>> > "Address")
>> >
>> > Could you please let me know where I am going wrong. I tried several
>> > other
>> > options such as writing custom code, switch, choose statements..I am
>> > migrating the report from crystal reports to Reporting services.
>> > Instead of
>> > the format in the above expression it was AddressLine1 in Crystal
>> > reports
>> > that had worked fine. Also does anybody know of an alternative for the
>> > NameFlip function of crystal rpts to use in Reporting svcs'
>> >
>> > Thanks in advance,
>> > RS
>> That IIF() call is pretty complex - is it possible for you to put some
>> of this logic in the database layer (e.g. by calling a view)?
>>

IIF Statements

Hi All,
The below IIF statement is not working for me.
=IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
"Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
& " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
<> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
"Address")
Could you please let me know where I am going wrong. I tried several other
options such as writing custom code, switch, choose statements..I am
migrating the report from crystal reports to Reporting services. Instead of
the format in the above expression it was AddressLine1 in Crystal reports
that had worked fine. Also does anybody know of an alternative for the
NameFlip function of crystal rpts to use in Reporting svcs'
Thanks in advance,
RSAfter seeing the full syntax I think you have to use some more "iif's " in
between before "Address:" , if you can explain in plain language what exactly
you are trying to display. ie something like if the first conditions is true
then what and if false then what...
Amarnath
"RSub" wrote:
> Hi All,
> The below IIF statement is not working for me.
> =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> "Address")
> Could you please let me know where I am going wrong. I tried several other
> options such as writing custom code, switch, choose statements..I am
> migrating the report from crystal reports to Reporting services. Instead of
> the format in the above expression it was AddressLine1 in Crystal reports
> that had worked fine. Also does anybody know of an alternative for the
> NameFlip function of crystal rpts to use in Reporting svcs'
> Thanks in advance,
> RS|||Hi Amarnath,
My report uses a SQL query which is very complex and it has joins from
several diff tables. I was trying to add a calculated field(embedded) to the
data source and I need that to display the address which is addressline1, 2,
city state, zip etc based on some criteria which is the first part of the IIF
statement. I removed the variable Address and the OR and it is working fine.
The latter false part of the statement needs to remove US if it finds it in
the address and not display in the report. I'm trying to make that work now.
Thanks,
Roopa
"Amarnath" wrote:
> After seeing the full syntax I think you have to use some more "iif's " in
> between before "Address:" , if you can explain in plain language what exactly
> you are trying to display. ie something like if the first conditions is true
> then what and if false then what...
> Amarnath
>
> "RSub" wrote:
> > Hi All,
> > The below IIF statement is not working for me.
> > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> > "Address")
> >
> > Could you please let me know where I am going wrong. I tried several other
> > options such as writing custom code, switch, choose statements..I am
> > migrating the report from crystal reports to Reporting services. Instead of
> > the format in the above expression it was AddressLine1 in Crystal reports
> > that had worked fine. Also does anybody know of an alternative for the
> > NameFlip function of crystal rpts to use in Reporting svcs'
> >
> > Thanks in advance,
> > RS|||ok, so infact you can nest the iif as well, to get the desired results.
Amarnath
"RSub" wrote:
> Hi Amarnath,
> My report uses a SQL query which is very complex and it has joins from
> several diff tables. I was trying to add a calculated field(embedded) to the
> data source and I need that to display the address which is addressline1, 2,
> city state, zip etc based on some criteria which is the first part of the IIF
> statement. I removed the variable Address and the OR and it is working fine.
> The latter false part of the statement needs to remove US if it finds it in
> the address and not display in the report. I'm trying to make that work now.
> Thanks,
> Roopa
>
> "Amarnath" wrote:
> > After seeing the full syntax I think you have to use some more "iif's " in
> > between before "Address:" , if you can explain in plain language what exactly
> > you are trying to display. ie something like if the first conditions is true
> > then what and if false then what...
> >
> > Amarnath
> >
> >
> > "RSub" wrote:
> >
> > > Hi All,
> > > The below IIF statement is not working for me.
> > > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> > > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> > > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> > > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> > > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> > > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> > > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> > > "Address")
> > >
> > > Could you please let me know where I am going wrong. I tried several other
> > > options such as writing custom code, switch, choose statements..I am
> > > migrating the report from crystal reports to Reporting services. Instead of
> > > the format in the above expression it was AddressLine1 in Crystal reports
> > > that had worked fine. Also does anybody know of an alternative for the
> > > NameFlip function of crystal rpts to use in Reporting svcs'
> > >
> > > Thanks in advance,
> > > RS|||Your first problem is that the IIf currently contains four parameters:
1: Trim(Fields!BillType.Value) = "IN" AND (Fields!User9.Value)= 1
2: "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value &
" " & Fields!City.Value & " " & Fields!State.Value & " " & Fields!
Zip.Value & " " & Fields!Country.Value) OR Format(Fields!Addr1_2.Value
& " " & Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " &
Fields!State_2.Value & " " & Fields!Zip_2.Value & " " & Fields!
Country_2.Value)
3: Trim("Address") <> "US" and Trim("Address") <> "USA" and
Trim("Address") <> "United States"
4: "Address"
Second, parameter 2 is altogether meaningless for several reasons:
* "Address:" = Format(... is testing if the result of your format
statement matches the string "Address:", which it almost certainly
won't.
* Format() takes two parameters, the object and the format type, and
you only pass one parameter each time.
* Format() is generally used to convert numbers, dates, etc to a
string: for example, Format(1.5, "C") returns $1.50 in the US. You
probably don't even need it for the addresses you're putting together.
* OR operates on two boolean values. Though you have one boolean value
from the "Address:" = Format(... comparison (by accident, I suspect),
I don't see what you're hoping to accomplish with the statement.
And third, parameter 3 will always return true - Trim("Address") will
always return "Address" which will never match the variations on "US".
On Apr 23, 5:42 pm, RSub <R...@.discussions.microsoft.com> wrote:
> Hi All,
> The below IIF statement is not working for me.
> =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> "Address")
> Could you please let me know where I am going wrong. I tried several other
> options such as writing custom code, switch, choose statements..I am
> migrating the report from crystal reports to Reporting services. Instead of
> the format in the above expression it was AddressLine1 in Crystal reports
> that had worked fine. Also does anybody know of an alternative for the
> NameFlip function of crystal rpts to use in Reporting svcs'
> Thanks in advance,
> RS

IIF Statement to Case but getting error

I tried converting the statement below, which is just one of many statements
in a view. This one poplulates one column in the view:
IF(DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
COUNT([CUSTOMER__])>=5,YES,NO)
to:
CASE WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
COUNT([CUSTOMER__])>=5 THEN 'YES' ELSE 'NO'
I'm getting an error that says the query designer does not support the CASE
sql construct. Any thoughts on how I can rewrite the IIF statement so that i
t
can work in a sql view? THANKS!!Mike,
Where are you creating the view?. Use Query analyzer.
AMB
"Mike C" wrote:

> I tried converting the statement below, which is just one of many statemen
ts
> in a view. This one poplulates one column in the view:
> IF(DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5,YES,NO)
> to:
> CASE WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5 THEN 'YES' ELSE 'NO'
> I'm getting an error that says the query designer does not support the CAS
E
> sql construct. Any thoughts on how I can rewrite the IIF statement so that
it
> can work in a sql view? THANKS!!|||Mike C a écrit :
> I tried converting the statement below, which is just one of many statemen
ts
> in a view. This one poplulates one column in the view:
> IF(DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5,YES,NO)
> to:
> CASE WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5 THEN 'YES' ELSE 'NO'
END missing in CAS structure :
CASE
WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE()) <=30
AND COUNT([CUSTOMER__]) >= 5 THEN 'YES'
ELSE 'NO'
END as YesNoCol

> I'm getting an error that says the query designer does not support the CAS
E
> sql construct. Any thoughts on how I can rewrite the IIF statement so that
it
> can work in a sql view? THANKS!!
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Alejandro,
Thank you. That worked. The problem I'm left with is how to run this report
automatically. I've been using DTS to export a view to an Excel sheet but it
looks like that won't work in this case. I guess I could try to put this in
an sp (which I haven't done much of and should probably start mastering) and
either DTS the sp result or I could just throw the results in a web-based
datagrid and export the datagrid to Excel on demand. Do you have any
recommendations on how to make the query results available to users? Thanks
again for the earlier suggestion.
MC
"Alejandro Mesa" wrote:
> Mike,
> Where are you creating the view?. Use Query analyzer.
>
> AMB
> "Mike C" wrote:
>|||I actually had END in the view but I forgot to type it into my question.
"SQLpro [MVP]" wrote:

> Mike C a écrit :
> END missing in CAS structure :
>
> CASE
> WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE()) <=30
> AND COUNT([CUSTOMER__]) >= 5 THEN 'YES'
> ELSE 'NO'
> END as YesNoCol
>
> A +
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQ
L
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
>

IIF Statement

Below I'm trying to return the larger column. If the SUM of UnitsInStock is
greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS
largerUnits and vice versa.
This is in northwind, can someone help me correct my syntax? I wasn't sure
how to do it with CASE.
CODE
SELECT IIf(SUM(UnitsInStock)>SUM(UnitsOnOrder), SUM(UnitsInStock) AS
largerUnits, SUM(UnitsOnOrder) AS largerUnits), ProductName
FROM Products
GROUP BY ProductNameTry this (untested):
SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
then SUM(UnitsInStock)
else SUM(UnitsOnOrder)
end AS largerUnits
,ProductName
FROM Products
GROUP BY ProductName
ML
http://milambda.blogspot.com/|||On Fri, 23 Dec 2005 18:22:17 -0600, Scott wrote:

> Below I'm trying to return the larger column. If the SUM of UnitsInStock i
s
>greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS
>largerUnits and vice versa.
>This is in northwind, can someone help me correct my syntax? I wasn't sure
>how to do it with CASE.
>CODE
>SELECT IIf(SUM(UnitsInStock)>SUM(UnitsOnOrder), SUM(UnitsInStock) AS
>largerUnits, SUM(UnitsOnOrder) AS largerUnits), ProductName
>FROM Products
>GROUP BY ProductName
>
SELECT CASE WHEN SUM(UnitsInStock) > SUM(UnitsOnOrder)
THEN SUM(UnitsInStock)
ELSE SUM(UnitsOnOrder) ) AS largerUnits),
ProductName
FROM Products
GROUP BY ProductName
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Forgive me for dipping my fly into your ointment, but your CASE expression i
s
missing its END. ;)
ML
http://milambda.blogspot.com/|||thanks.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:63517235-3C00-4A10-9FD3-1963051A3411@.microsoft.com...
> Try this (untested):
> SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
> then SUM(UnitsInStock)
> else SUM(UnitsOnOrder)
> end AS largerUnits
> ,ProductName
> FROM Products
> GROUP BY ProductName
>
> ML
> --
> http://milambda.blogspot.com/|||Just one thought - how will you ditinct between the two values in the client
application? After all, those are just numbers, but this query returns them
in a single column, although they originate in two different sources...?
Another CASE maybe?
SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
then SUM(UnitsInStock)
else SUM(UnitsOnOrder)
end AS largerUnits
,case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
then 'InStock'
else 'OnOrder'
end AS largerSource
,ProductName
FROM Products
GROUP BY ProductName
ML
http://milambda.blogspot.com/|||thanks, in my case, i just needed the larger of the 2.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3442E4F6-D752-4708-BB0A-A65416DE9755@.microsoft.com...
> Just one thought - how will you ditinct between the two values in the
> client
> application? After all, those are just numbers, but this query returns
> them
> in a single column, although they originate in two different sources...?
> Another CASE maybe?
> SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
> then SUM(UnitsInStock)
> else SUM(UnitsOnOrder)
> end AS largerUnits
> ,case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
> then 'InStock'
> else 'OnOrder'
> end AS largerSource
> ,ProductName
> FROM Products
> GROUP BY ProductName
>
> ML
> --
> http://milambda.blogspot.com/|||On Fri, 23 Dec 2005 17:05:02 -0800, ML wrote:

>Forgive me for dipping my fly into your ointment, but your CASE expression
is
>missing its END. ;)
Hi ML,
So it is. Thanks for the correction.
I really shouldn't write any more replies after 1 AM....
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||But then again - the level of inspiration is usually at its highest point at
1 AM.
ML
http://milambda.blogspot.com/|||For fun, I thought I would try a sql clr function to simulate IIF. Not
quite as elegant as the real IIF, but maybe more concise then a Case.
-- Usage example
declare @.n1 int
declare @.n2 int
set @.n1 = 1
set @.n2 = 2
select dbo.IIF(sum(@.n1), '>', sum(@.n2), 100, 200) -- Just to show using
sum(), does not make sense in this usage.
select dbo.IIF(@.n1, '>', @.n2, 'n1 is > n2', 'n1 is not > n2')
select dbo.IIF(@.n1, '<', @.n2, 'n1 is < n2', 'n1 is not < n2')
select dbo.IIF(@.n1, '>=', @.n2, 'n1 is >= n2', 'n1 is not >= n2');
select dbo.IIF(@.n1, '<=', @.n2, 'n1 is <= n2', 'n1 is not <= n2')
select dbo.IIF(@.n1, '==', @.n2, 'n1 is == n2', 'n1 is not == n2')
select dbo.IIF(@.n1, '!=', @.n2, 'n1 is != n2', 'n1 is not != n2')
select dbo.IIF(@.n1, '<>', @.n2, 'n1 is <> n2', 'n1 is not <> n2')
//
// The SQL Clr UDF IIF code.
//
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
/// <summary>
/// Returns one of two objects, depending on the evaluation of an
expression.
/// </summary>
/// <param name="lside">The left side comparand.</param>
/// <param name="op">The conditional operator to use for
testing.</param>
/// <param name="rside">The right side comparand.</param>
/// <param name="truePart">Returned if Expression evaluates to
True.</param>
/// <param name="falsePart">Returned if Expression evaluates to
False.</param>
/// <returns>Returns one of two objects, depending on the evaluation of
an expression. </returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static object IIF(object lside, string op, object rside, object
truePart, object falsePart)
{
if (lside == null || rside == null)
return falsePart;
if (lside is DBNull || rside is DBNull)
return falsePart;
if (op == null)
throw new ArgumentNullException("op");
IComparable cLeft = (IComparable)lside;
IComparable cRight = (IComparable)rside;
/*
CompareTo results:
Less than zero - This instance is less than obj.
Zero - This instance is equal to obj.
Greater than zero - This instance is greater than obj.
*/
switch (op)
{
case ">":
if (cLeft.CompareTo(cRight) > 0)
return truePart;
return falsePart;
case ">=":
if (cLeft.CompareTo(cRight) >=0)
return truePart;
return falsePart;
case "<":
if (cLeft.CompareTo(cRight) < 0)
return truePart;
return falsePart;
case "<=":
if (cLeft.CompareTo(cRight) <= 0)
return truePart;
return falsePart;
case "==":
if (cLeft.CompareTo(cRight) == 0)
return truePart;
return falsePart;
case "!=":
case "<>":
if (cLeft.CompareTo(cRight) == 0)
return falsePart;
return truePart;
default:
throw new ArgumentException("op");
}
}
};
William Stacey [MVP]
"Scott" <sbailey@.mileslumber.com> wrote in message
news:ua%230dECCGHA.216@.TK2MSFTNGP15.phx.gbl...
> Below I'm trying to return the larger column. If the SUM of UnitsInStock
> is greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS
> largerUnits and vice versa.
> This is in northwind, can someone help me correct my syntax? I wasn't sure
> how to do it with CASE.
> CODE
> SELECT IIf(SUM(UnitsInStock)>SUM(UnitsOnOrder), SUM(UnitsInStock) AS
> largerUnits, SUM(UnitsOnOrder) AS largerUnits), ProductName
> FROM Products
> GROUP BY ProductName
>

iif error in reporting services

Hi ,

Is there anything wrong in the below expression?

=IIf(Fields!outboundcalls.Value=0,"0.00%",(Sum(Fields!obappt.Value))/(Sum(Fields!outboundcalls.Value)))

I have the above expression in a grouping row.

sometimes even when 'Fields!outboundcalls.Value' is not equal to zero it is still displaying "0.00%" in the report.it does this randomly.it works fine in some rows and in some it displays "0.00%".

I find it odd because i have defined the same expression for all rows, yet it works only for some...

can anybody shed some light on this issue if i am missing anything?

Thanks!

The problem is that with IIF, both the true and false "parts" get executed no matter how the expression evaluates. Try using Switch instead. Something like this:

=Switch(Fields!outboundcalls.Value = 0,"0.00%", Fields!outboundcalls.Value <> 0, DO DIVISION HERE )

IIF Count

Cannot get this to work, any suggestions? (Need to count where values
are as below)
=Count(iif(LCase(Fields!Exceptions.Value Like) "*center*" AND
(LCase(Fields!Server.Value) Like "ser*" AND Fields!Production.Value = "1", 1, Nothing)))Bracket in wrong place?
Should be:
=Count(iif((LCase(Fields!Exceptions.Value)) Like "*center*" AND
(LCase(Fields!Server.Value)) Like "ser*" AND Fields!Production.Value ="1"), 1, Nothing))
Craig
"d4" <d4mann@.gmail.com> wrote in message
news:1141939610.708232.138940@.j33g2000cwa.googlegroups.com...
> Cannot get this to work, any suggestions? (Need to count where values
> are as below)
>
> =Count(iif(LCase(Fields!Exceptions.Value Like) "*center*" AND
> (LCase(Fields!Server.Value) Like "ser*" AND Fields!Production.Value => "1", 1, Nothing)))
>|||Get build errors:
The value expression for the textbox 'textbox25' has a scope
parameter that is not valid for an aggregate function. The scope
parameter must be set to a string constant that is equal to either the
name of a containing group, the name of a containing data region, or
the name of a data set.
and
The value expression for the textbox 'textbox25' uses an aggregate
expression with an invalid recursive/simple flag. The valid values for
this flag are 'Recursive' and 'Simple'.
Below does work, but I need to add another AND value to further select
correct one...
=Count(iif(LCase( Fields!Exceptions.Value ) Like "*center*" AND
Fields!Production.Value = "1", 1, Nothing))|||Got it:
=Count(iif(LCase(Fields!Exceptions.Value) Like "*center*" AND
LCase(Fields!Server.Value) Like "ser*" AND Fields!Production.Value ="1"), 1, Nothing))
Now, does anyone know how to do a NOT LIKE?|||put a NOT in front ... IIF(NOT (condition),truepart,falsepart)
"d4" wrote:
> Got it:
> =Count(iif(LCase(Fields!Exceptions.Value) Like "*center*" AND
> LCase(Fields!Server.Value) Like "ser*" AND Fields!Production.Value => "1"), 1, Nothing))
> Now, does anyone know how to do a NOT LIKE?
>

IIF & IsNull Functions

Below in "ACCESS SQL CODE" is a query that I use in Access. It uses the IIf
and IsNull functions to concatenate the full name. If there is a middle
initial, my statement returns a comma after the LastName field, the middle
initial followed by a period. If it doesn't exist, sql returns nothing.
I'm upsizing the access database to an access adp project file and
converting my queries into views. When I try to run the below "SQL VIEW
CODE", I get an error saying "The isnull function requires 2 arguments".
I thought my syntax was right. What am I doing wrong? Is the IIf function
causing the problem in my Access ADP view?
ACCESS SQL CODE *************
SELECT LastName, FirstName, MiddleInit, LastName & ", " & FirstName &
IIf(IsNull(MiddleInit),""," " & MiddleInit & ".") AS Name
FROM myTable
SQL VIEW CODE ****************
SELECT FirstName, t_Users.MiddleInit,
LastName + ", " + FirstName + IIf(IsNull(MiddleInit),'',' ' + MiddleInit &
'.') AS Name
FROM myTableOn Wed, 26 Apr 2006 16:54:07 -0500, scott wrote:

>SELECT FirstName, t_Users.MiddleInit,
>LastName + ", " + FirstName + IIf(IsNull(MiddleInit),'',' ' + MiddleInit &
>'.') AS Name
>FROM myTable
Hi Scott,
IIf and IsNull are Access-specific functions that won't work in SQL
Server or in any ANSI-compliant relational database.
SQL Server also has an ISNULL function; it's function is roughly the
same as Access' Nz function. And it''s allso non-ANSI-compliant.
The ANSI-compliant version of yoour query (that will work on SQL Server
and other ANNSI-compliant databases, but not on Access) is
SELECT FirstName, t_Users.MiddleInit,
LastName + ', ' + FirstName + COALESCE(' ' + MiddleInit + '.'),
'') AS Name
FROM myTable
Hugo Kornelis, SQL Server MVP|||thanks.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:5ssv42tvikim4oi8cnl3b6taln8bq76hhf@.
4ax.com...
> On Wed, 26 Apr 2006 16:54:07 -0500, scott wrote:
>
> Hi Scott,
> IIf and IsNull are Access-specific functions that won't work in SQL
> Server or in any ANSI-compliant relational database.
> SQL Server also has an ISNULL function; it's function is roughly the
> same as Access' Nz function. And it''s allso non-ANSI-compliant.
> The ANSI-compliant version of yoour query (that will work on SQL Server
> and other ANNSI-compliant databases, but not on Access) is
> SELECT FirstName, t_Users.MiddleInit,
> LastName + ', ' + FirstName + COALESCE(' ' + MiddleInit + '.'),
> '') AS Name
> FROM myTable
> --
> Hugo Kornelis, SQL Server MVP