IIS & Sql Server authentication

When would you use scenerio one vs. scenerio two?
Scenerio one - IIS (anonymous, or anonymous and
integrated)--OLE DB Provider for SQL (integrated
security)-->SQL (mixed mode or windows
authentication)
Scenerion two - IIS (basic or integrated)--OLE DB
Provider for SQL (integrated security)--> SQL (mixed
mode or windows authentication)
Our environment has IIS and SQL Server on the same machine.Hi Michelle,
Merry Christmas and thank you for using MSDN Newsgroup! It's my pleasure to
assist you with your issue.
You are choosing the authentication way for you application run on IIS and
SQL Server with OLE DB Provider, right? From my experience, to design a
system with high efficency and security will take many aspects for
consideration, including the application environment, security, connection,
performance requirment, etc. It is hard to say that one way is better than
another. It depends on specifics. So you can refer to the following
articles from Microsoft :
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/SecNetch05.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/secnetlpMSDN.asp
Implementing a Secure Site with ASP
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsecure/ht
ml/msdn_implement.asp
Designing Efficient Applications for Microsoft SQL Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlsg/htm
l/msdn_designeff.asp
Accessing SQL Server from a Web Application
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vbconaccessingsqlserverfromwebapplication.asp
Configuring Security for Internet Information Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsentpro/ht
ml/veconConfiguringSecurityForInternetInformationServer.asp
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:
80/support/kb/articles/Q247/9/31.ASP&NoWebContent=1
Microsoft Internet Information Server Security Overview
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dniis/html/
iissecure.asp
Optimizing SQL Server and IIS Security and Connectivity
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9150
The following articles are from some other website. Microsoft does not
guarentee the correctness of it. You can still take them for reference:
Using Windows Security with IIS and SQL Server 2000
http://www.winnetmag.com/Articles/ArticleID/23035/pg/2/2.html
IIS User Authentication
http://www.adiscon.com/IIS/gen001.htm
Setting SQL Server 7.0 and IIS Security
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9002
Hope this would be helpful in solving your problem. If you still have
questions, please feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

IIS

I have .net 2003 installed on my pc without IIS, because in our organisation there is web server separate and there its installed IIS. Can anybody let me know how to configure my application to the server so that i can open my application. because its giving the following error

The Web server reported the following error when attempting to create or open the web project location at the following location URL:http://192.168.0.61/HelpDesk' http/1.1 503 Service unavailable.

im using windows 2000

is it possible to run asp.net application file from web server without installing IIS in my system.. please reply .

Its very urgent. Thanks

Sorry, this is not an IIS forum, but before you move over to another you should check if the Virtual Directory you want to use is exisiting on port 80, if the service is started and ready to answer requests on the WebServer. if you are starting a web application on a remote server, then yes, the execution of an ASP.NET application from a "simple" equiped client with just a brwoser is possible. if you want to host an ASP.NET application on your computer then you probably will need to have some sort of WebServer with support for ASP.NET.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

IIS

Hi,
A few of the machines have not got IIS installed (business related).
Is there a way to install sql server 2005 without having IIS on the machine?
Thanks

From my understanding the IIS Part is only for the reporting services, and som other smaller functions. You should remove the reporting service components and it should install.

|||

Tried that already and still sql server 2005 management studio does not appear.

|||

Are you able to install SQL Server?

What all components are you selcting while installing? Since you are not able to open Sql sever 2005, it seems like you are not selecting tools option.

Could you please give a snapshot of the error that you are getting?

(This http://msdn2.microsoft.com/en-us/library/ms143506.aspx mentions that “Reporting Services, which is installed as part of SQL Server Express with Advanced Services, will not install on operating systems that do not include Internet Information Services (IIS)”. There should be no problem in installing SQL server without IIS (We tried in our lab we were able to install SQL Server without IIS).)

Thanks

Arpita

|||

Management Studio can be installed regardless of whether IIS is installed.

Management Studio is one of the "Workstation components", so you need to check that checkbox on the "Components to Install" page of the setup wizard. If you need to be more selective, you can also click the "Advanced" button the "Components to Install" page, expand the "Client Components" folder, and then install just the Management Tools to your machine.

Hope this helps,
Steve

IIS

Hi,
A few of the machines have not got IIS installed (business related).
Is there a way to install sql server 2005 without having IIS on the machine?
Thanks

From my understanding the IIS Part is only for the reporting services, and som other smaller functions. You should remove the reporting service components and it should install.

|||

Tried that already and still sql server 2005 management studio does not appear.

|||

Are you able to install SQL Server?

What all components are you selcting while installing? Since you are not able to open Sql sever 2005, it seems like you are not selecting tools option.

Could you please give a snapshot of the error that you are getting?

(Thishttp://msdn2.microsoft.com/en-us/library/ms143506.aspx mentions that “Reporting Services, which is installed as part of SQL Server Express with Advanced Services, will not install on operating systems that do not include Internet Information Services (IIS)”. There should be no problem in installing SQL server without IIS (We tried in our lab we were able to install SQL Server without IIS).)

Thanks

Arpita

|||

Management Studio can be installed regardless of whether IIS is installed.

Management Studio is one of the "Workstation components", so you need to check that checkbox on the "Components to Install" page of the setup wizard. If you need to be more selective, you can also click the "Advanced" button the "Components to Install" page, expand the "Client Components" folder, and then install just the Management Tools to your machine.

Hope this helps,
Steve

Iis

Hi,
A few of the machines have not got IIS installed (business related).
Is there a way to install sql server 2005 without having IIS on the machine?
ThanksAs far as I've understood (I'm still on 2000), there's no need for IIS. At least it's not listed in the software requirements for Express Edition.

IIS

Hi,
A few of the machines have not got IIS installed (business related).
Is there a way to install sql server 2005 without having IIS on the machine?
Thanks

From my understanding the IIS Part is only for the reporting services, and som other smaller functions. You should remove the reporting service components and it should install.

|||

Tried that already and still sql server 2005 management studio does not appear.

|||

Are you able to install SQL Server?

What all components are you selcting while installing? Since you are not able to open Sql sever 2005, it seems like you are not selecting tools option.

Could you please give a snapshot of the error that you are getting?

(This http://msdn2.microsoft.com/en-us/library/ms143506.aspx mentions that “Reporting Services, which is installed as part of SQL Server Express with Advanced Services, will not install on operating systems that do not include Internet Information Services (IIS)”. There should be no problem in installing SQL server without IIS (We tried in our lab we were able to install SQL Server without IIS).)

Thanks

Arpita

|||

Management Studio can be installed regardless of whether IIS is installed.

Management Studio is one of the "Workstation components", so you need to check that checkbox on the "Components to Install" page of the setup wizard. If you need to be more selective, you can also click the "Advanced" button the "Components to Install" page, expand the "Client Components" folder, and then install just the Management Tools to your machine.

Hope this helps,
Steve

iif/switch statement does not work in input parameter expression

Hi There,

One of the parameters that i need to pass to a stored procedure of my
report, i wish to dynamically calculate from another input default parameter

lets say parameter 'DrillDown' = "ABCDEFG"

paramenter 'MyLevel' is calculated by either of the following a switch or
iif statement:

=switch
(left(Parameters!DrillDown.Value,1)="A",1,
left(Parameters!DrillDown.Value,1)="B",2,
left(Parameters!DrillDown.Value,1)="C",3,
left(Parameters!DrillDown.Value,1)="D",4,
left(Parameters!DrillDown.Value,1)="E",5,
left(Parameters!DrillDown.Value,1)="F",6,
left(Parameters!DrillDown.Value,1)="G",7)
--or--
=iif(left(Parameters!RepLvl.Value,1) = "A",1,
iif(left(Parameters!RepLvl.Value,1) = "B",2,5))

Trouble is none of the 2 above work!

i get the following error: The value expression for the report parameter
‘MyLevel’ contains an error: [BC30201] Expression expected.

Wierd thing is if i just have '=iif(left(Parameters!RepLvl.Value,1) =
"A",1,5)' without an 'else if' it works fine !

Can any one help please!

Cheers

Dave

why not use custom code?

I have found for complicated IIF statements, it's easier and safer to create a custom code function.

|||

I would use a code function but for input parameters on start of a report you cant assign them to a 'code.' parameter. Can you? I get an error when i try this.

I got it to work , by taking out all the spaces ?!?!?

Wierdly it works with the spaces in if i have the expression elsewhere in the report, for intsance in a text box .

Anyhow thanks for the reply

Cheers

Dave

IIF,ISNULL in transact sql

I have this query in ACCESS VBA:
SELECT DISTINCT qryRCP.RCP_VendorID, Sum(IIf(nz([RCP_ReceiptQty],0)=0,0,1))
AS fldNbrRcpts,
Sum(IIf(nz([SumOfRCQ_RejectQty]+[SumOfRC
Q_ScrapQty],0)=0,0,1)) AS
fldNbrRejScrap
FROM qryRCP
WHERE qryRCP.RCP_ReceiptQty >=0
GROUP BY qryRCP.RCP_VendorID, qryRCP.POM_PayName, qryRCP.VEN_PerfRating,
qryRCP.VEN_StatusCode
Can anyone help to convert it in sql?
I tried to change nz to isnull, but still hitting syntaz error.
I need to run in sql query analyzer first to find out the problem.
Thanks lotCheck out the ISNULL() and COALESCE functions in BooksOnLine.
Andrew J. Kelly SQL MVP
"Sql Fren" <SqlFren@.discussions.microsoft.com> wrote in message
news:16728166-FF7F-4BEB-86E1-D045B4801F0F@.microsoft.com...
>I have this query in ACCESS VBA:
> SELECT DISTINCT qryRCP.RCP_VendorID,
> Sum(IIf(nz([RCP_ReceiptQty],0)=0,0,1))
> AS fldNbrRcpts,
> Sum(IIf(nz([SumOfRCQ_RejectQty]+[SumOfRC
Q_ScrapQty],0)=0,0,1)) AS
> fldNbrRejScrap
> FROM qryRCP
> WHERE qryRCP.RCP_ReceiptQty >=0
> GROUP BY qryRCP.RCP_VendorID, qryRCP.POM_PayName, qryRCP.VEN_PerfRating,
> qryRCP.VEN_StatusCode
> Can anyone help to convert it in sql?
> I tried to change nz to isnull, but still hitting syntaz error.
> I need to run in sql query analyzer first to find out the problem.
> Thanks lot|||
SELECT
RCP_VendorID,
SUM(RCP_ReceiptQty), -- because of WHERE clause,
-- this can't possibly be NULL
SUM(COALESCE(SumOfRCQ_RejectQty,0) + COALESCE(SumOfRCQ_ScrapQty,0))
FROM qryRCP
WHERE RCP_ReceiptQty >= 0
GROUP BY RCP_VendorID
-- your other GROUP BY columns are illegal here, since they're
-- not part of the query at all!
On 3/17/05 10:49 PM, in article
16728166-FF7F-4BEB-86E1-D045B4801F0F@.microsoft.com, "Sql Fren"
<SqlFren@.discussions.microsoft.com> wrote:

> SELECT DISTINCT qryRCP.RCP_VendorID, Sum(IIf(nz([RCP_ReceiptQty],0)=0,0,1
))
> AS fldNbrRcpts,
> Sum(IIf(nz([SumOfRCQ_RejectQty]+[SumOfRC
Q_ScrapQty],0)=0,0,1)) AS
> fldNbrRejScrap
> FROM qryRCP
> WHERE qryRCP.RCP_ReceiptQty >=0
> GROUP BY qryRCP.RCP_VendorID, qryRCP.POM_PayName, qryRCP.VEN_PerfRating,
> qryRCP.VEN_StatusCode|||On Thu, 17 Mar 2005 23:06:08 -0500, Aaron [SQL Server MVP] wrote:
(snip)
>-- your other GROUP BY columns are illegal here, since they're
>-- not part of the query at all!
Hi Aaron,
That doesn't make them illegal. There's no requirement to include all
group by columns in the select list (though omitting then might make the
output of the query useless - I have a hard time imagining a scenario
where it'd be useful).
use pubs
go
select max(zip)
from authors
group by state
go
95688
46403
66044
20853
48105
97330
37215
84152
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 17 Mar 2005 23:06:08 -0500, Aaron [SQL Server MVP] wrote:

> -- your other GROUP BY columns are illegal here, since they're
> -- not part of the query at all!
Say what?
From SQL2K Books Online:
|| GROUP BY Clause
|| Specifies the groups into which output rows are to be placed and, if
|| aggregate functions are included in the SELECT clause <select list>,
|| calculates a summary value for each group. When GROUP BY is specified,
|| either each column in any non-aggregate expression in the select list
|| should be included in the GROUP BY list, or the GROUP BY expression
|| must match exactly the select list expression.
I read this as saying that you can't have a non-aggregate expression in the
SELECT list that isn't in the GROUP BY clause - but it says nothing about
having an expression in the GROUP BY clause that isn't in the SELECT list!
Of course, the result isn't very meaningful - how can you tell which rows
correspond to which group by value if the group by value isn't returned -
but I just ran this in query analyzer
select job, count(map) maps, sum(qty) qtys
from ttOrdClubItem
group by job,club
and got the same result as this:
select job, maps, qtys
from (
select job, club, count(map) maps, sum(qty) qtys
from ttOrdClubItem
group by job,club
)
so the construction is definitely "legal", at least|||Oh gosh, semantics. Sorry, I should have said useless, stupid, meaningless,
bizarre, weird, unexpected... any others I'm missing?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7jql31po7vvbvsu0q5eo2bgdmmus870567@.
4ax.com...
> On Thu, 17 Mar 2005 23:06:08 -0500, Aaron [SQL Server MVP] wrote:
> (snip)
> Hi Aaron,
> That doesn't make them illegal. There's no requirement to include all
> group by columns in the select list (though omitting then might make the
> output of the query useless - I have a hard time imagining a scenario
> where it'd be useful).
> use pubs
> go
> select max(zip)
> from authors
> group by state
> go
>
> --
> 95688
> 46403
> 66044
> 20853
> 48105
> 97330
> 37215
> 84152
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

IIF(ISNULL(dbo.SalesAnalyse.Verzenddatum), 'unknown', DATEPART(yyyy.SalesAnalyse.Verz

Hello,
I think i am missing something. This code give's an error... Can someone
tell me what it is'
IIF(ISNULL(dbo.SalesAnalyse.Verzenddatum), 'unknown',
DATEPART(yyyy.SalesAnalyse.Verzenddatum))
tnx in advance..
eric1) IIF is not a function is T-SQL.
2) ISNULL accepts 2 arguments, not 1.
3) DATEPART accepts 2 arguments; separate the datepart (yyyy) from the date
with a comma, not a period.
I assume you want this?
ISNULL(CAST(DATEPART(yyyy, SalesAnalyse.Verzenddatum) AS VARCHAR(8)),
'unknown')
Jacco Schalkwijk
SQL Server MVP
"Judith van der Niet" <jniet@.mit.com> wrote in message
news:e5QpjKfDFHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I think i am missing something. This code give's an error... Can someone
> tell me what it is'
> IIF(ISNULL(dbo.SalesAnalyse.Verzenddatum), 'unknown',
> DATEPART(yyyy.SalesAnalyse.Verzenddatum))
> tnx in advance..
> eric
>

IIF()

What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =
'1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
it is in a SELECT statement and I keep getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
TIA
Andrew MadsenDon't believe IIF is supported in TSQL (although I think it is available to
Analysis Services...)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...

quote:

---

> What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =
> '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
> it is in a SELECT statement and I keep getting an error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='.
> TIA
> Andrew Madsen
>

---

|||No IIF in SQL Server, look up CASE in Books Online.
SELECT OpenTime = DATEDIFF(DAY, open_date_key,
CASE WHEN Closed_date_key = '19000101' THEN Current_date ELSE
Closed_date_key END)
If you used NULL instead of a token date when the closed_date_key is
"unknown", you could say
SELECT OpenTime = DATEDIFF(DAY, open_date_key,
COALESCE(Closed_date_key, Current_date))
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...

quote:

---

> What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =
> '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
> it is in a SELECT statement and I keep getting an error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='.
> TIA
> Andrew Madsen
>

---

|||IIF is not supported in T-SQL. Use CASE:
Datediff(dd, open_date_key
, case when Closed_date_key = '1/1/1900'
then Current_Date -- I think you mean to use CURRENT_TIMESTAMP
else Closed_date_key end) as OpenTime
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =
'1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
it is in a SELECT statement and I keep getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
TIA
Andrew Madsen|||That would throw an error. It is just interesting it did not throw at the
IIF( instead of waiting for the '=' sign. Thanks for the help.
Andrew Madsn
Harley-Davidson Motor Company
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uf86F9B7DHA.1052@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Don't believe IIF is supported in TSQL (although I think it is available

---

to

quote:

---

> Analysis Services...)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
=[QUOTE]
>

---

|||hi andrew,
Use CASE, there is no iif in SQL Server.
Try:
select datediff(dd, open_date_key, case when Closed_date_key = '19000101' th
en getdate() else Closed_date_key end)
from <table>
-Vishal|||Tom, Aaron and Vishal,
Case was it. Thank you very much. and Tom Current_Timestamp was what I
wanted even though Current_Date is a T-SQL value. Weird. Well, Thank you all
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Vishal Parkar" <remove_this_vgparkar@.yahoo.co.in> wrote in message
news:673ECD20-76F2-418F-9B73-110BE31A9F8D@.microsoft.com...

quote:

---

> hi andrew,
> Use CASE, there is no iif in SQL Server.
> Try:
> select datediff(dd, open_date_key, case when Closed_date_key = '19000101'

---

then getdate() else Closed_date_key end)

quote:

---

> from <table>
> -Vishal

---

IIF()

What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key = '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
it is in a SELECT statement and I keep getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
TIA
Andrew MadsenDon't believe IIF is supported in TSQL (although I think it is available to
Analysis Services...)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
> What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key => '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
> it is in a SELECT statement and I keep getting an error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='.
> TIA
> Andrew Madsen
>|||No IIF in SQL Server, look up CASE in Books Online.
SELECT OpenTime = DATEDIFF(DAY, open_date_key,
CASE WHEN Closed_date_key = '19000101' THEN Current_date ELSE
Closed_date_key END)
If you used NULL instead of a token date when the closed_date_key is
"unknown", you could say
SELECT OpenTime = DATEDIFF(DAY, open_date_key,
COALESCE(Closed_date_key, Current_date))
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
> What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key => '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
> it is in a SELECT statement and I keep getting an error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='.
> TIA
> Andrew Madsen
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0289_01C3EBF6.C5A01230
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
IIF is not supported in T-SQL. Use CASE:
Datediff(dd, open_date_key
, case when Closed_date_key = '1/1/1900'
then Current_Date -- I think you mean to use CURRENT_TIMESTAMP
else Closed_date_key end) as OpenTime
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key ='1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
it is in a SELECT statement and I keep getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
TIA
Andrew Madsen
--=_NextPart_000_0289_01C3EBF6.C5A01230
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

IIF is not supported in T-SQL. =Use CASE:
Datediff(dd, =open_date_key
, case when Closed_date_key =3D ='1/1/1900'
then Current_Date -- I think you mean to =use CURRENT_TIMESTAMP
else Closed_date_key end) as OpenTime
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Andrew Madsen" wrote in message news:uU5u73B7DHA.632@.T=K2MSFTNGP12.phx.gbl...What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =3D'1/1/1900', Current_Date, Closed_date_key )) as =OpenTime?it is in a SELECT statement and I keep getting an error:Server: Msg 170, =Level 15, State 1, Line 1Line 1: Incorrect syntax near ='=3D'.TIAAndrew Madsen

---

--=_NextPart_000_0289_01C3EBF6.C5A01230--|||That would throw an error. It is just interesting it did not throw at the
IIF( instead of waiting for the '=' sign. Thanks for the help.
Andrew Madsn
Harley-Davidson Motor Company
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uf86F9B7DHA.1052@.TK2MSFTNGP12.phx.gbl...
> Don't believe IIF is supported in TSQL (although I think it is available
to
> Analysis Services...)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
> > What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key
=> > '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
> >
> > it is in a SELECT statement and I keep getting an error:
> >
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near '='.
> >
> > TIA
> >
> > Andrew Madsen
> >
> >
>|||hi andrew
Use CASE, there is no iif in SQL Server
Try
select datediff(dd, open_date_key, case when Closed_date_key = '19000101' then getdate() else Closed_date_key end
from <table
-Vishal|||Tom, Aaron and Vishal,
Case was it. Thank you very much. and Tom Current_Timestamp was what I
wanted even though Current_Date is a T-SQL value. Weird. Well, Thank you all
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Vishal Parkar" <remove_this_vgparkar@.yahoo.co.in> wrote in message
news:673ECD20-76F2-418F-9B73-110BE31A9F8D@.microsoft.com...
> hi andrew,
> Use CASE, there is no iif in SQL Server.
> Try:
> select datediff(dd, open_date_key, case when Closed_date_key = '19000101'
then getdate() else Closed_date_key end)
> from <table>
> -Vishal

IIF X AND Y , why does Y get evaluated?

I have some logic in a report that is not working as I thought it should and
it puzzles me.
Lets says I have this line, (ignoring syntax errors, I forget what is
supposed to be there for days)
IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8, SomeDate))
Now because in this case ProductType is 5, SomeDate isn't populated, its
null if this language has that concept. But the error I get is telling me
that adding -8 to SomeDate produces something thats not a date. Well of
course thats true because when ProductType is 5 and SomeDate is nothing so
why is it even looking at it.
Is there a way around this?
thanksOn Apr 12, 4:22 pm, "Coaster" <Coas...@.Coaster.net> wrote:
> I have some logic in a report that is not working as I thought it should and
> it puzzles me.
>
That's weird, I would not have expected IIF to evaluate both branches,
but maybe if you first check SomeDate to see if it is null, then you
can control when DateAdd is run, like so:
IIF(Fields!ProductType.Value = 5,"Hello there", IIF
IsNull(SomeDate)=False, DateAdd(days, -8, SomeDate), ''))
HTH
> Lets says I have this line, (ignoring syntax errors, I forget what is
> supposed to be there for days)
> IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8, SomeDate))
> Now because in this case ProductType is 5, SomeDate isn't populated, its
> null if this language has that concept. But the error I get is telling me
> that adding -8 to SomeDate produces something thats not a date. Well of
> course thats true because when ProductType is 5 and SomeDate is nothing so
> why is it even looking at it.
> Is there a way around this?
> thanks|||On Apr 14, 4:59 am, "Jerry H." <boilersr...@.gmail.com> wrote:
> On Apr 12, 4:22 pm, "Coaster" <Coas...@.Coaster.net> wrote:> I have some logic in a report that is not working as I thought it should and
> > it puzzles me.
> That's weird, I would not have expected IIF to evaluate both branches,
> but maybe if you first check SomeDate to see if it is null, then you
> can control when DateAdd is run, like so:
> IIF(Fields!ProductType.Value = 5,"Hello there", IIF
> IsNull(SomeDate)=False, DateAdd(days, -8, SomeDate), ''))
>
I dont know if it resolves your issue or not . as per my experience,in
iif statement, then clause and else clause should have the same
datatype values,in the following case.one is date type and another is
string type .please correct this also
Thanks
Raj deep.A
>
> > Lets says I have this line, (ignoring syntax errors, I forget what is
> > supposed to be there for days)
> > IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8, SomeDate))
> > Now because in this case ProductType is 5, SomeDate isn't populated, its
> > null if this language has that concept. But the error I get is telling me
> > that adding -8 to SomeDate produces something thats not a date. Well of
> > course thats true because when ProductType is 5 and SomeDate is nothing so
> > why is it even looking at it.
> > Is there a way around this?
> > thanks|||I've seen today a coworker using IIF to avoid a division by zero error and
it seemed to works fine..
What is the error you get ? I suspect a problem with DateAdd (are you sure
"day" shouldn't be within quotes ?)
--
Patrice
"Coaster" <Coaster@.Coaster.net> a écrit dans le message de news:
ObHp0uNnIHA.1052@.TK2MSFTNGP05.phx.gbl...
>I have some logic in a report that is not working as I thought it should
>and it puzzles me.
> Lets says I have this line, (ignoring syntax errors, I forget what is
> supposed to be there for days)
> IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8,
> SomeDate))
> Now because in this case ProductType is 5, SomeDate isn't populated, its
> null if this language has that concept. But the error I get is telling me
> that adding -8 to SomeDate produces something thats not a date. Well of
> course thats true because when ProductType is 5 and SomeDate is nothing so
> why is it even looking at it.
> Is there a way around this?
> thanks
>|||On Apr 12, 4:22=A0pm, "Coaster" <Coas...@.Coaster.net> wrote:
> I have some logic in a report that is not working as I thought it should a=nd
> it puzzles me.
> Lets says I have this line, (ignoring syntax errors, I forget what is
> supposed to be there for days)
> IIF(Fields!ProductType.Value =3D 5,"Hello there", DateAdd(days, -8, SomeDa=te))
> Now because in this case ProductType is 5, SomeDate isn't populated, its
> null if this language has that concept. But the error I get is telling me
> that adding -8 to SomeDate produces something thats not a date. Well of
> course thats true because when ProductType is 5 and SomeDate is nothing so=
> why is it even looking at it.
> Is there a way around this?
> thanks
This is a quote from Chris Hayes from Microsoft:
"The problem is this: The IIF function evaluates all of its
arguments."
JerryH's solution (I adjusted his SQL to SSRS/VB syntax),
IIF(Fields!ProductType.Value =3D 5,"Hello there", IIF(SomeDate =3D
Nothing, Nothing, DateAdd("d", -8, SomeDate)))
may work because the nested IIf is evaluated first.
In the Code tab/window of Report Properties, enter the following:
Public Function DateMinus8(ByVal Exp1)
If Exp1 =3D 5 Then
DateMinus8 =3D "Hello There"
Else DateMinus8 =3D DateAdd("d", -8, SomeDate)
End If
End Function
Then use =3Dcode.DateMinus8(Fields!ProductType.Value )
instead of =3D IIF(Fields!ProductType.Value =3D 5,"Hello there",
DateAdd(days, -8, SomeDate))
To Patrice: I think your co-worker just got lucky and had no zeros
show up in the denominator because IIF will not resolve divide by zero
issues without some tweaking.
To truly avoid divide by zero use either:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 =3D 0 Then
DivideBy =3D 0
Else DivideBy =3D Exp1 / Exp2
End If
End Function
Then use =3Dcode.DivideBy(Numerator,Denominator)
instead of =3DIIF(Denominator =3D 0, 0, Numerator/Denominator)
OR if you don't want to use custom code try
=3DIIf(Denominator =3D 0, "N/A", Numerator / IIf(Denominator =3D 0, 1,
Denominator))|||On Apr 14, 12:46=A0pm, "Patrice" <http://www.chez.com/scribe/> wrote:
> I've seen today a coworker using IIF to avoid a division by zero error and=
> it seemed to works fine..
> What is the error you get ? I suspect a problem with DateAdd (are you sure=
> "day" shouldn't be within quotes ?)
> --
> Patrice
> "Coaster" <Coas...@.Coaster.net> a =E9crit dans le message de news:
> ObHp0uNnIHA.1...@.TK2MSFTNGP05.phx.gbl...
>
> >I have some logic in a report that is not working as I thought it should
> >and it puzzles me.
> > Lets says I have this line, (ignoring syntax errors, I forget what is
> > supposed to be there for days)
> > IIF(Fields!ProductType.Value =3D 5,"Hello there", DateAdd(days, -8,
> > SomeDate))
> > Now because in this case ProductType is 5, SomeDate isn't populated, its=
> > null if this language has that concept. But the error I get is telling m=e
> > that adding -8 to SomeDate produces something thats not a date. Well of
> > course thats true because when ProductType is 5 and SomeDate is nothing =so
> > why is it even looking at it.
> > Is there a way around this?
> > thanks- Hide quoted text -
> - Show quoted text -
This is a quote from Chris Hayes from Microsoft:
"The problem is this: The IIF function evaluates all of its
arguments."
JerryH's solution (I adjusted his SQL to SSRS/VB syntax),
IIF(Fields!ProductType.Value =3D 5,"Hello there", IIF(SomeDate =3D
Nothing, Nothing, DateAdd("d", -8, SomeDate)))
may work because the nested IIf is evaluated first.
I usually use custom code to get around the IIF issue. You could try
something like the following.
In the Code tab/window of Report Properties, enter the following:
Public Function DateMinus8(ByVal Exp1)
If Exp1 =3D 5 Then
DateMinus8 =3D "Hello There"
Else DateMinus8 =3D DateAdd("d", -8, SomeDate)
End If
End Function
Then use =3Dcode.DateMinus8(Fields!ProductType.Value )
instead of =3D IIF(Fields!ProductType.Value =3D 5,"Hello there",
DateAdd(days, -8, SomeDate))
To Patrice: I think your co-worker just got lucky and had no zeros
show up in the denominator because IIF will not resolve divide by
zero
issues without some tweaking.
To truly avoid divide by zero use either:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 =3D 0 Then
DivideBy =3D 0
Else DivideBy =3D Exp1 / Exp2
End If
End Function
Then use =3Dcode.DivideBy(Numerator,Denominator)
instead of =3DIIF(Denominator =3D 0, 0, Numerator/Denominator)
OR if you don't want to use custom code try
=3DIIf(Denominator =3D 0, "N/A", Numerator / IIf(Denominator =3D 0, 1,
Denominator))|||"toolman" <timd@.infocision.com> wrote in message
news:f3db3d38-59d5-402d-9b4b-e2b59c64f563@.u69g2000hse.googlegroups.com...
On Apr 12, 4:22 pm, "Coaster" <Coas...@.Coaster.net> wrote:
> I have some logic in a report that is not working as I thought it should
> and
> it puzzles me.
> Lets says I have this line, (ignoring syntax errors, I forget what is
> supposed to be there for days)
> IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8,
> SomeDate))
> Now because in this case ProductType is 5, SomeDate isn't populated, its
> null if this language has that concept. But the error I get is telling me
> that adding -8 to SomeDate produces something thats not a date. Well of
> course thats true because when ProductType is 5 and SomeDate is nothing so
> why is it even looking at it.
> Is there a way around this?
> thanks
This is a quote from Chris Hayes from Microsoft:
"The problem is this: The IIF function evaluates all of its
arguments."
JerryH's solution (I adjusted his SQL to SSRS/VB syntax),
IIF(Fields!ProductType.Value = 5,"Hello there", IIF(SomeDate =Nothing, Nothing, DateAdd("d", -8, SomeDate)))
may work because the nested IIf is evaluated first.
In the Code tab/window of Report Properties, enter the following:
Public Function DateMinus8(ByVal Exp1)
If Exp1 = 5 Then
DateMinus8 = "Hello There"
Else DateMinus8 = DateAdd("d", -8, SomeDate)
End If
End Function
Then use =code.DateMinus8(Fields!ProductType.Value )
instead of = IIF(Fields!ProductType.Value = 5,"Hello there",
DateAdd(days, -8, SomeDate))
To Patrice: I think your co-worker just got lucky and had no zeros
show up in the denominator because IIF will not resolve divide by zero
issues without some tweaking.
To truly avoid divide by zero use either:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 = 0 Then
DivideBy = 0
Else DivideBy = Exp1 / Exp2
End If
End Function
Then use =code.DivideBy(Numerator,Denominator)
instead of =IIF(Denominator = 0, 0, Numerator/Denominator)
OR if you don't want to use custom code try
=IIf(Denominator = 0, "N/A", Numerator / IIf(Denominator = 0, 1,
Denominator))
Thanks alot !!! I 'll check it out tomorrow at work. I didn't even know you
could have functions like that in the report. JerryH's solution didn't work
for me because it still evaluated the date even though it was nested,
hopefully this won't happen using a function.|||"Jerry H." <boilersrock@.gmail.com> wrote in message
news:8bd61d4f-5c11-434e-931b-0615c09fd011@.59g2000hsb.googlegroups.com...
> On Apr 12, 4:22 pm, "Coaster" <Coas...@.Coaster.net> wrote:
>> I have some logic in a report that is not working as I thought it should
>> and
>> it puzzles me.
> That's weird, I would not have expected IIF to evaluate both branches,
> but maybe if you first check SomeDate to see if it is null, then you
> can control when DateAdd is run, like so:
> IIF(Fields!ProductType.Value = 5,"Hello there", IIF
> IsNull(SomeDate)=False, DateAdd(days, -8, SomeDate), ''))
> HTH
>
>
>> Lets says I have this line, (ignoring syntax errors, I forget what is
>> supposed to be there for days)
>> IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8,
>> SomeDate))
>> Now because in this case ProductType is 5, SomeDate isn't populated, its
>> null if this language has that concept. But the error I get is telling me
>> that adding -8 to SomeDate produces something thats not a date. Well of
>> course thats true because when ProductType is 5 and SomeDate is nothing
>> so
>> why is it even looking at it.
>> Is there a way around this?
>> thanks
>
yeah it is weird and it even evaluated it in your solution. Perhaps the
toolmans solution will work. Find out tomorrow.|||Humm... I gave this a try on another report i'm working on :
=IIf(True,1,0/0)
and it worked fine. If I change True to False I then have a "non numerical
value" string shown in the field...
I'm using RS 2005...

Iif use

I am using this code, trying to set the bit value of @.temp1 using the Iif statement:

declare @.temp1 bit,
@.var1 varchar,
@.var2 varchar,
@.var3 varchar

select @.var1='testing', @.var2='testing2'

Select @.temp1 = Iif((@.var1 = @.var2), 1, 0)

select @.temp1

I get this error:
Line 9: Incorrect syntax near '='.

Any suggestions?
ThanksI belive yu are using IIF in the wrong context try:

case when @.var1 = @.var2 then 1 else 0 end|||declare @.temp1 bit,
@.var1 varchar,
@.var2 varchar,
@.var3 varchar

select @.var1='testing', @.var2='testing2', @.temp1=0

Select @.temp1 = case when @.var1 = @.var2 then 1 else 0 end

select @.temp1 as result

OK, I changed it to the codeabove. But now result comes back as 1, how is that possible since the 2 variables arent equal?|||try:

declare @.temp1 bit,
@.var1 varchar(10),
@.var2 varchar(10),
@.var3 varchar(10)
select @.var1='testing'
, @.var2='testing2'
, @.temp1=0
Select @.temp1 = case when @.var1 = @.var2 then 1 else 0 end
select @.temp1 as result|||That worked. Wonder why the first one didnt. Thanks!

Originally posted by Paul Young
try:

declare @.temp1 bit,
@.var1 varchar(10),
@.var2 varchar(10),
@.var3 varchar(10)
select @.var1='testing'
, @.var2='testing2'
, @.temp1=0
Select @.temp1 = case when @.var1 = @.var2 then 1 else 0 end
select @.temp1 as result|||By default Varchar (or Char, NChar, NVarchar, etc.) is a 1 character string. Adding the (10) makes it a 10 byte string.

iif to case for sql server 2000

I am trying to convert this query to slq server 2000 and I cant figure
out how to get rid of the IIF statements and make them case statements.

If anyone could help I would greatly appreciate it!

Thanks!

spafa

SELECT Jeopardy.Main, Jeopardy.Name, Jeopardy.COMMENTS2,
Jeopardy.STATUS, Jeopardy.DENTAL_STATUS, Jeopardy.HLTH_INC,
Jeopardy.DNTL_INC, Jeopardy.COMP_HLTH, Jeopardy.COMP_HLTH_DISC,
Jeopardy.COMP_PLAN_DESIGN, Jeopardy.COMP_DNTL, Jeopardy.COMP_DNTL_DISC,
Jeopardy.OUT_TO_BID, IIf([COMP_HLTH]=\"Mass Blue
Cross\",\"YES\",IIf([COMP_HLTH]=\"Out of State Blue
Cross\",\"YES\",IIf([COMP_HLTH]=\"CT Blue
Cross\",\"YES\",IIf([COMP_HLTH]=\"Empire Blue Cross\",\"YES\",\"NO\"))))
AS OTHER_BC_PLAN, IIf([other_bc_plan]=\"yes\",[COMP_HLTH],\"\") AS
BC_PLAN, Jeopardy.LG_RANKING, Jeopardy.LG_SCORE, Jeopardy.DATE_NOTIFIED,
Jeopardy.DATE_UPDATED, Now()-([Jeopardy]![DATE_UPDATED]) AS DATEDIFF,
Now()-([Jeopardy]![DATE_ADDED]) AS DATEDIFF2,
IIf([DateDiff]<8,\"*\",Null) AS CHANGE, IIf([DateDiff2]<8,\"+\",Null) AS
[ADD], Jeopardy.Rep_Id, Jeopardy.Rep_Name, tblIRIP_QA_NAMES.ADMIN_NAME
AS MSS, AccountOwnership.ANALYST_NAME, AccountOwnership.UND_NAME,
AccountOwnership.DNTL_UND_NAME, AccountOwnership.SERVICE_REP,
AccountOwnership.SIZE, AccountOwnership.SIZE2, Jeopardy.CYCLE,
Jeopardy.DENTAL_CYCLE, IIf([Jeopardy]![cycle] Is Null,[Jeopardy]![DENTA-
L_CYCLE],IIf([Jeopardy]![cycle]=\"N/A\",[Jeopardy]![DENTAL_CYCLE],[Jeop-
ardy]![cycle])) AS CYCLE2, AccountOwnership.Canc_Date,
AccountOwnership.Dntl_Canc_Date, AccountOwnership.EFFDATE,
AccountOwnership.Dntl_EFFDATE, AccountOwnership.TOTALHLTH,
AccountOwnership.TOTALDNTL, [healthmate]+[classic] AS TotalCross,
AccountOwnership.HEALTHMATE, AccountOwnership.CHIP,
AccountOwnership.CLASSIC, AccountOwnership.BROKER,
AccountOwnership.HLTH_BROKER_1, AccountOwnership_DSC.DISPOSITION,
AccountOwnership_DSC.DISPOSITION_MONTH, IIf([DISPOSITION_month] Is Not
Null,\"YES\",\"NO\") AS OC, IIf([DISPOSITION_month] Is Not
Null,[DISPOSITION_month],Null) AS OC_MONTH

FROM ((AccountOwnership_DSC RIGHT JOIN AccountOwnership ON
AccountOwnership_DSC.Main = AccountOwnership.Main) RIGHT JOIN Jeopardy
ON AccountOwnership.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAMES ON
AccountOwnership.REP_ID = tblIRIP_QA_NAMES.Rep_Id ORDER BY
Jeopardy.DATE_UPDATED DESC; " );

--
Posted via http://dbforums.comBelow are 2 examples taken directly from the SQL 2000 Books Online
<"tsqlref.chm::/ts_ca-co_5t9v.htm">. The first example uses the simple
CASE form where the specified expression is compared with each entry in
the list. The second example uses the searched CASE form where each
entry in the list specifies a Boolean condition.

USE pubs
GO

SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"SPAFA" <member44362@.dbforums.com> wrote in message
news:3487057.1066269043@.dbforums.com...
> I am trying to convert this query to slq server 2000 and I cant figure
> out how to get rid of the IIF statements and make them case
statements.
>
> If anyone could help I would greatly appreciate it!
>
> Thanks!
> spafa
>
>
> SELECT Jeopardy.Main, Jeopardy.Name, Jeopardy.COMMENTS2,
> Jeopardy.STATUS, Jeopardy.DENTAL_STATUS, Jeopardy.HLTH_INC,
> Jeopardy.DNTL_INC, Jeopardy.COMP_HLTH, Jeopardy.COMP_HLTH_DISC,
> Jeopardy.COMP_PLAN_DESIGN, Jeopardy.COMP_DNTL,
Jeopardy.COMP_DNTL_DISC,
> Jeopardy.OUT_TO_BID, IIf([COMP_HLTH]=\"Mass Blue
> Cross\",\"YES\",IIf([COMP_HLTH]=\"Out of State Blue
> Cross\",\"YES\",IIf([COMP_HLTH]=\"CT Blue
> Cross\",\"YES\",IIf([COMP_HLTH]=\"Empire Blue
Cross\",\"YES\",\"NO\"))))
> AS OTHER_BC_PLAN, IIf([other_bc_plan]=\"yes\",[COMP_HLTH],\"\") AS
> BC_PLAN, Jeopardy.LG_RANKING, Jeopardy.LG_SCORE,
Jeopardy.DATE_NOTIFIED,
> Jeopardy.DATE_UPDATED, Now()-([Jeopardy]![DATE_UPDATED]) AS DATEDIFF,
> Now()-([Jeopardy]![DATE_ADDED]) AS DATEDIFF2,
> IIf([DateDiff]<8,\"*\",Null) AS CHANGE, IIf([DateDiff2]<8,\"+\",Null)
AS
> [ADD], Jeopardy.Rep_Id, Jeopardy.Rep_Name, tblIRIP_QA_NAMES.ADMIN_NAME
> AS MSS, AccountOwnership.ANALYST_NAME, AccountOwnership.UND_NAME,
> AccountOwnership.DNTL_UND_NAME, AccountOwnership.SERVICE_REP,
> AccountOwnership.SIZE, AccountOwnership.SIZE2, Jeopardy.CYCLE,
> Jeopardy.DENTAL_CYCLE, IIf([Jeopardy]![cycle] Is
Null,[Jeopardy]![DENTA-
L_CYCLE],IIf([Jeopardy]![cycle]=\"N/A\",[Jeopardy]![DENTAL_CYCLE],[Jeop-
> ardy]![cycle])) AS CYCLE2, AccountOwnership.Canc_Date,
> AccountOwnership.Dntl_Canc_Date, AccountOwnership.EFFDATE,
> AccountOwnership.Dntl_EFFDATE, AccountOwnership.TOTALHLTH,
> AccountOwnership.TOTALDNTL, [healthmate]+[classic] AS TotalCross,
> AccountOwnership.HEALTHMATE, AccountOwnership.CHIP,
> AccountOwnership.CLASSIC, AccountOwnership.BROKER,
> AccountOwnership.HLTH_BROKER_1, AccountOwnership_DSC.DISPOSITION,
> AccountOwnership_DSC.DISPOSITION_MONTH, IIf([DISPOSITION_month] Is Not
> Null,\"YES\",\"NO\") AS OC, IIf([DISPOSITION_month] Is Not
> Null,[DISPOSITION_month],Null) AS OC_MONTH
> FROM ((AccountOwnership_DSC RIGHT JOIN AccountOwnership ON
> AccountOwnership_DSC.Main = AccountOwnership.Main) RIGHT JOIN Jeopardy
> ON AccountOwnership.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAMES
ON
> AccountOwnership.REP_ID = tblIRIP_QA_NAMES.Rep_Id ORDER BY
> Jeopardy.DATE_UPDATED DESC; " );
>
> --
> Posted via http://dbforums.com|||In addition to Dan's post, note that there may sometimes be neater
alternatives to CASE:

... COALESCE(NULLIF(Jeopardy.cycle,'N/A'),Jeopardy.dental_cycle) AS cycle2,
...

--
David Portas
----
Please reply only to the newsgroup
--

IIF String Contains a value?

I want to create an IIF expression that changes the color of a field based on if a string value contains a 4 or 5 Any ideas on how to accomplish this?

Hi,

You can write an iif() expression in the background color property of that field.

The following expression will help:

iif(instr("string5",5) or instr("string4",4),"Gray","White")

You can change the color by choosing what u require from the constants provided else custon color.

Somiya

|||Thanks, but I think I need a little more help. The value I need to look for will be in a string like "3,4,6,8" so I think I need the Like conparison function, but I'm not sure of the syntax

The psudo code is this:

If instrI(String) contains a 4 or instrI(String) contains 5 display in Red else Black

Do you know what the syntax should be for IIF with a like conparison?|||

i think the same expression should work in a way similar to Like.

An iff() expression with Like operator would be in this case:

iif(("3,4,6,8" Like "*4*") or ("3,4,6,8" Like "*5*") ,"Red",Black")

* is for any 4 preceeding and followed with any number of characters

Somiya

iif stored procedure

Hi all,

I have a bunch of queries initially written for MS Access that contain iif
function.
Since I'm new in SQL Server and I don't like the idea of rewriting all
queries using CASE statement, I hope someone has written iif T-SQL stored
procedure.
If someone have T-SQL code of such procedure I would appreciate you post
this code here.

Thanks in advance!"Anabella" <x@.x.com> wrote in message news:cu86s9$pj0$1@.ls219.htnet.hr...
> Hi all,
> I have a bunch of queries initially written for MS Access that contain iif
> function.
> Since I'm new in SQL Server and I don't like the idea of rewriting all
> queries using CASE statement, I hope someone has written iif T-SQL stored
> procedure.
> If someone have T-SQL code of such procedure I would appreciate you post
> this code here.
> Thanks in advance!

Since IIF doesn't exist in TSQL, I don't think you have any other option.
The Upsizing Wizard might help, but the most reliable thing to do would be
to rewrite them.

Simon|||"Anabella" <x@.x.com> wrote in message news:cu86s9$pj0$1@.ls219.htnet.hr...
> Hi all,
> I have a bunch of queries initially written for MS Access that contain iif
> function.
> Since I'm new in SQL Server and I don't like the idea of rewriting all
> queries using CASE statement, I hope someone has written iif T-SQL stored
> procedure.
> If someone have T-SQL code of such procedure I would appreciate you post
> this code here.
> Thanks in advance!

I think maybe you could write a udf which took parameters.
I think handling the various comparisons might be hard work.
Off the top of my head, I think you'd have to change the syntax of your
query a bit so you handed the comparison as a parameter.
And... erm... changing your query is what you wanted to avoid.

I've got some other bad news for you as well.

MS Access sql is slightly different from sql server sql .
I would suggest you need to go through all your queries and check em out.
Changing iif to case statements is likely just part of the task you face.

--
Regards,
Andy O'Neill|||Andy O'Neill (aon14nocannedmeat@.lycos.co.uk) writes:
> I think maybe you could write a udf which took parameters.
> I think handling the various comparisons might be hard work.
> Off the top of my head, I think you'd have to change the syntax of your
> query a bit so you handed the comparison as a parameter.
> And... erm... changing your query is what you wanted to avoid.

Since boolean is not a datatype in SQL Server, you cannot write a
UDF which permits you to say:

SECECT dho.iif(@.x = @.y, 1, 2)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95F7EFAAF416CYazorman@.127.0.0.1...
> Andy O'Neill (aon14nocannedmeat@.lycos.co.uk) writes:
>> I think maybe you could write a udf which took parameters.
>> I think handling the various comparisons might be hard work.
>> Off the top of my head, I think you'd have to change the syntax of your
>> query a bit so you handed the comparison as a parameter.
>> And... erm... changing your query is what you wanted to avoid.
> Since boolean is not a datatype in SQL Server, you cannot write a
> UDF which permits you to say:
> SECECT dho.iif(@.x = @.y, 1, 2)

I had in mind handing over the comparison operation as a string.
So check if the comparison to be made is varchar and '=' or maybe '>' or
'<'.
If that makes sense.

That's the part I'm actually most confident I could handle.
I didn't even begin to think about the various datatypes one could have for
other parameters...

--
Regards,
Andy O'Neill

Iif Statment, IsNumeric, FormatCurrency

I noticed that regardless of the IsNumeric evaluation, the
Formatcurrency still fires. If I remove the FormatCurrency, and just
display the data, no problem. With the FormatCurrency in place, I get
an #error in the field if it is Alpha, but the proper information if it
is Numeric.
Example:
Iif(IsNumeric(Fields!item1.Value),FormatCurrency(Fields!item1.Value),"")
Has anyone else seen this before? If so, is there a workaround or is
there a problem with my logic?Howdy,
Both branches of the IIF function are evaluated without regard to the
logical expression. IIF just picks one of the evaluations to return.
(search this group for 'divide by zero error with iif' for more details
on the practicalities of this function)
Try something along the lines of
FormatCurrency(Iif(IsNumeric(Fields!item1.Value),Fields!item1.Value,-1))
HTH,
Sean G.|||Sean G.,
Thanks, I appreciate the assistance.
-Mal

IIF Statment to compare two values in joined tables

Hi

I'm trying to compare two varchars to check if they are the same, if they are the same then the color must turn red, if not then they must remain black

SELECT *

from members m, client c

where C.ClientID = m.ClientID

AND c.ClientID in (87,86)

AND m.email in ('dassd@.fdskjh.com','asdfas@.sdfd.net', etc...)

my results will give me two of the same email addresses but with different ClientID's, now when it

finds the same email it needs to make them both "RED"

Please help, any advice would be helpful

Kind Regards

Carel Greaves

Two ways occur to me:

- add a COUNT(c.ClientId) and GROUP BY m.email to your sql query, then check that count when displaying the data in your report - if the count is greater than 1 then you can colour the email field red

- use a row group in your report, grouping by email address, then have the other fields following - but if you use this approach you won't need to colour the email addresses red, as it will be instantly obvious which addresses are assigned to more than one client

IIF statment in an SQL statment.

I am trying to convert a logical and date fields into number fields. I am
using IIF() but I can't seem to get the syntax correct. Help Please.
SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
FROM Order2
GROUP BY MagazineName
The overall goal is to
1) count then number of records
2) count how many are renewal's
3) count how many are canceled
example:
CHILD 50 5 1
Scott BurkeHey Scott,
Use the CASE statement in SQL, not IIF. Like this:
SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag =false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
1 END as CLRFL
FROM Order2
GROUP BY MagazineName
Michael C
"Scott Burke" wrote:
> I am trying to convert a logical and date fields into number fields. I am
> using IIF() but I can't seem to get the syntax correct. Help Please.
> SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> FROM Order2
> GROUP BY MagazineName
> The overall goal is to
> 1) count then number of records
> 2) count how many are renewal's
> 3) count how many are canceled
> example:
> CHILD 50 5 1
> Scott Burke|||Hi Michael. Thanks for the suggestion. It worked exactly the way I wonted to.
The case statement looks a lot like an IIF() statement to me.
Time to do some research.
Thanks again.
Scott Burke
"Michael C" wrote:
> Hey Scott,
> Use the CASE statement in SQL, not IIF. Like this:
>
> SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag => false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
> 1 END as CLRFL
> FROM Order2
> GROUP BY MagazineName
>
> Michael C
> "Scott Burke" wrote:
> > I am trying to convert a logical and date fields into number fields. I am
> > using IIF() but I can't seem to get the syntax correct. Help Please.
> >
> > SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> > 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> > FROM Order2
> > GROUP BY MagazineName
> >
> > The overall goal is to
> > 1) count then number of records
> > 2) count how many are renewal's
> > 3) count how many are canceled
> >
> > example:
> > CHILD 50 5 1
> >
> > Scott Burke

IIF Statements in Reporting Services

Afternoon All,
I'm working on a report and I'm trying to include an IIF Statement
since there's a possiblity that I could get a division by zero error.
Here is my calculation:
=IIF( Fields!acdcalls.Value = 0, #0:00:00#, (Fields!anstime.Value \
Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
Fields!acdcalls.Value)\60) Mod 60,"\:00") &
Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
Also tried:
=IIF( Fields!acdcalls.Value = 0, "0:00:00", (Fields!anstime.Value \
Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
Fields!acdcalls.Value)\60) Mod 60,"\:00") &
Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
This seems to work if the calculation is not as complex but doesn't
like this one at all.
I would appreciate any suggestions.
Thanks,
JodyFor complicated expressions I would do such calculations in custom code.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jody Baldwin" <jody.baldwin@.gmail.com> wrote in message
news:1137705771.369654.288710@.f14g2000cwb.googlegroups.com...
> Afternoon All,
> I'm working on a report and I'm trying to include an IIF Statement
> since there's a possiblity that I could get a division by zero error.
> Here is my calculation:
> =IIF( Fields!acdcalls.Value = 0, #0:00:00#, (Fields!anstime.Value \
> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
> Also tried:
> =IIF( Fields!acdcalls.Value = 0, "0:00:00", (Fields!anstime.Value \
> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
> This seems to work if the calculation is not as complex but doesn't
> like this one at all.
> I would appreciate any suggestions.
> Thanks,
> Jody
>|||Reporting services equates both sides of an if before it execute it there
for
=IIF( 1=0,0,10/0)
will give an error therefore the only solution I have found is to use custom
code as suggested by the previous poster.
Thanks
Dale
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:O7Rw3sWHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> For complicated expressions I would do such calculations in custom code.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jody Baldwin" <jody.baldwin@.gmail.com> wrote in message
> news:1137705771.369654.288710@.f14g2000cwb.googlegroups.com...
>> Afternoon All,
>> I'm working on a report and I'm trying to include an IIF Statement
>> since there's a possiblity that I could get a division by zero error.
>> Here is my calculation:
>> =IIF( Fields!acdcalls.Value = 0, #0:00:00#, (Fields!anstime.Value \
>> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
>> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
>> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
>> Also tried:
>> =IIF( Fields!acdcalls.Value = 0, "0:00:00", (Fields!anstime.Value \
>> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
>> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
>> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
>> This seems to work if the calculation is not as complex but doesn't
>> like this one at all.
>> I would appreciate any suggestions.
>> Thanks,
>> Jody
>|||Thanks for the help... I created a custom function that fixed my
issues... Here is my code in case it can help someone else down the
road.
Public Function ConvertSecToTime(ByVal NumSec As Double, ByVal Calls As
Double) As String
Dim theTime As String
If NumSec = 0 Or Calls = 0 Then
theTime = "0:00:00"
Else
theTime = (NumSec \ Calls) \ 3600 & Format(((NumSec \ Calls) \ 60) Mod
60, "\:00") & Format((NumSec \ Calls) Mod 60, "\:00")
End If

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 Statements

Hi All,

I have a normal IIF statement that controls what text i see in a text box depending on the returned value from the database. This is fine and all is working well.

My question is: Is it possible to have say the first line of the text in bold and a different size to that of the first bit of data?

=IIF(Fields!Code1.Value = "Developed","Developed: " & First(Fields!DevelopedText.Value, "ResourceTexts"),Fields!Code1.Value)

My example above shows this but what i want is the word Developed: to be bold and a different sizer to what follows. is this possible? I'm thinking maybe i have to insert a bold tag maybe?

Any help would be greatly appreciated.

Ta

Dave

No, different formats in the control is not supported. RTF support will be eventually added in later versions.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||http://www.sqlservercentral.com/columnists/bknight/reportingservicesconditionalformatting.asp

IIF Statement with Date checking

Hello, I am using the following to try to find date ranges and do some work.
Here is the statement
=iif(fields!mbr_join_date.value = "7/01/2003", mbt_code, "Not Grandfathered")
I have looked at the query result and I see alot of dates that are this date
but it's not putting the MBT_Code in for the value. Anyone have any
suggestions. Should I convert the Date in the Query to a different format
currently it shows as yyyy-mm-dd 00:00:00 should I trim off the time or
convert it to a string value?
Thank you in advanceScrocker,
I'm not 100% sure if your doing this in the SQL or in a cell on the
report. If its in the report you need to write it like:
iif(fields!mbr_join_date.value = "7/01/2003", FIELDS!mbt_cod.VALUE, "Not
Grandfathered")
You can't reference the field name in a control without usin gthe FIELDS!
collection.
If its in your SQL, well, IIF's dont' work, so I'm thinking your not doing
it, but if you are then use a CASE statement
CASE WHEN mbr_join_date = '7/01/2003' THEN mbr_code ELSE 'Not Grandfathered'
END as mbr_code
Michael C
"scrocker" wrote:
> Hello, I am using the following to try to find date ranges and do some work.
> Here is the statement
> =iif(fields!mbr_join_date.value = "7/01/2003", mbt_code, "Not Grandfathered")
> I have looked at the query result and I see alot of dates that are this date
> but it's not putting the MBT_Code in for the value. Anyone have any
> suggestions. Should I convert the Date in the Query to a different format
> currently it shows as yyyy-mm-dd 00:00:00 should I trim off the time or
> convert it to a string value?
> Thank you in advance
>|||Thanks Michael, I will give that a shot, Yes it's in the report not in SQL
Michael C wrote:
>Scrocker,
> I'm not 100% sure if your doing this in the SQL or in a cell on the
>report. If its in the report you need to write it like:
>iif(fields!mbr_join_date.value = "7/01/2003", FIELDS!mbt_cod.VALUE, "Not
>Grandfathered")
>You can't reference the field name in a control without usin gthe FIELDS!
>collection.
>If its in your SQL, well, IIF's dont' work, so I'm thinking your not doing
>it, but if you are then use a CASE statement
>CASE WHEN mbr_join_date = '7/01/2003' THEN mbr_code ELSE 'Not Grandfathered'
>END as mbr_code
>Michael C
>> Hello, I am using the following to try to find date ranges and do some work.
>> Here is the statement
>[quoted text clipped - 7 lines]
>> convert it to a string value?
>> Thank you in advance|||You might try:
=iif(fields!mbr_join_date.value < "7/02/2003", mbt_code, "Not
Grandfathered")
Adding one day to the date value - you might have to specify the date
as
"2003-07-02 00:00:00" (yyyy-mm-dd 00:00:00 format)
to ensure you are setting the date/time to the very start of the
following day.

Iif statement to prevent divide by zero?

Hi- I'm trying to create a calculated field that is the percentage difference
between two database fields. To prevent a divide by zero, I tried making it:
= Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
This should provide the % diff, or in the case that dsPrice is 0, 1 (100%).
When I try to run the report, however, it comes back as a divide by zero for
fields where dsPrice = 0. Does reporting services evaluate both portions of
the Iif, then output one? How do I avoid this divide by zero error?
Thanks in advance!
Peter L.iif always evaluates both sides. try using the short circuit operator
'andalso' or 'orelse' in a function and add it to the code and call it from
the expression.|||If the correct zero value is 100%, you can just move the pieces around like
this:
= Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
That way, the division doesn't happen at all until the values are replaced.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
> Hi- I'm trying to create a calculated field that is the percentage
> difference
> between two database fields. To prevent a divide by zero, I tried making
> it:
> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
> This should provide the % diff, or in the case that dsPrice is 0, 1
> (100%).
> When I try to run the report, however, it comes back as a divide by zero
> for
> fields where dsPrice = 0. Does reporting services evaluate both portions
> of
> the Iif, then output one? How do I avoid this divide by zero error?
> Thanks in advance!
> Peter L.|||Whoops, I think that should have been more like this:
= Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
Iif(Fields!dsPrice.Value = 0, 1, Fields!dsPrice.Value)
Anyway, you get the idea!! :-)
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:eGkBJLo8EHA.1264@.TK2MSFTNGP12.phx.gbl...
> If the correct zero value is 100%, you can just move the pieces around
> like this:
> = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
> That way, the division doesn't happen at all until the values are
> replaced.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "plandry@.newsgroups.nospam"
> <plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
> news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
>> Hi- I'm trying to create a calculated field that is the percentage
>> difference
>> between two database fields. To prevent a divide by zero, I tried making
>> it:
>> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
>> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
>> This should provide the % diff, or in the case that dsPrice is 0, 1
>> (100%).
>> When I try to run the report, however, it comes back as a divide by zero
>> for
>> fields where dsPrice = 0. Does reporting services evaluate both portions
>> of
>> the Iif, then output one? How do I avoid this divide by zero error?
>> Thanks in advance!
>> Peter L.
>|||That did the trick... Thanks a bunch!
I will file that away in the "ninja reporting tricks" :)
"Jeff A. Stucker" wrote:
> Whoops, I think that should have been more like this:
> = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> Iif(Fields!dsPrice.Value = 0, 1, Fields!dsPrice.Value)
> Anyway, you get the idea!! :-)
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:eGkBJLo8EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > If the correct zero value is 100%, you can just move the pieces around
> > like this:
> >
> > = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> > Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
> >
> > That way, the division doesn't happen at all until the values are
> > replaced.
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "plandry@.newsgroups.nospam"
> > <plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
> > news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
> >> Hi- I'm trying to create a calculated field that is the percentage
> >> difference
> >> between two database fields. To prevent a divide by zero, I tried making
> >> it:
> >> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
> >> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
> >> This should provide the % diff, or in the case that dsPrice is 0, 1
> >> (100%).
> >> When I try to run the report, however, it comes back as a divide by zero
> >> for
> >> fields where dsPrice = 0. Does reporting services evaluate both portions
> >> of
> >> the Iif, then output one? How do I avoid this divide by zero error?
> >>
> >> Thanks in advance!
> >> Peter L.
> >
> >
>
>

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 problem

hi all,

i've browsed some of the smilar iif expression problems but no idea how to solve mine.

i get an #Error when i wrote the following expr :

=sum(iif(Fields!revenue_type.Value="R", Fields!amount.Value,0))

but no error when it's

=sum(iif(Fields!revenue_type.Value="R", Fields!amount.Value,0) - iif(Fields!revenue_type.Value="T", Fields!amount.Value,0))

If i filter the dataset and just take in data where revenue_type = "R" then there won't be error... how come?

did i do something wrong? please help... this seems so simple yet i couldn't get it..

oh btw, i wrote the expression in table footer|||Maybe somewhere Fields!revenue_type is "null", then accessing Fields!revenue_type.Value will generate error. Try checking (IsNothing(Fields!revenue_type.Value) = false and Fields!revenue_type.Value = "R").|||

hi Maciej, thanks for your reply but it still return #Error.

there's no null value for revenue_type

|||

Ok, after your post I've tried the same thing in one of my reports and got the same error (tip - it is good to read the "error list" ). Mine says:

Warning 1 [rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘textbox116’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type. c:\...\Obroty producentow.v1.2.rdl

So the problem is in aggregation varying data types. Simplest way to fix it:

=Sum(iff(<your bool>,CDbl(<your sum field>),CDbl(0))

CDbl is used only as a example (it will conver every number to "double" data type). You can use different conversion method (from Common functions\Conversion).

I hope this will help

Maciej

|||

thanks Maciej. it works! but i don't get why the expr needs to be converted to double data type...

cos i got that warning as well but didn't get it...

|||Maybe RS is not so clever Your field if decimal or duble or something and "0" is integer so RS gets lost |||You hit the nail on the head with "Maybe RS is not so clever." I experienced the same #error issue in some of my calculated columns in a table in my report but oddly not until i added 2 text boxes above the table to my report. The text boxes display a begin and end time from a separate data set query. When I added those, I got the #error issue. But when i remove them, it goes away. I did not edit the table with the calculated columns in any way. I was able to get the #error issue to go away by using the CDbl solution you described (THANKS!) and leave the text boxes with the datetimes on the report. But there has to be a bug somewhere because that makes no sense. MICROSOFT> PLEASE NOTE AND FIX!!!