Imbedded subroutine prefix error

I am trying to run this routine which works fine until I put the outside
Select in. The function consists of 4 Select statements that I need to do
to get counts of various comparisons in my tables. The problem is that the
inside PositionID has to refer to the outside PositionID and that is where I
get the error ("WHERE p3.PositionID = p.PositionID ").
How do I get it to refer this table (p).
SELECT c. CompanyID,PositionID,JobTitleShort,Poste
dData =
replace(convert(varchar,p.DateCreated,6),' ',''),
Qualified = (
SELECT Count(*)
FROM
(
SELECT
Cat1,Value1,Cat2,Value2,Cat3,Value3,Cat4
,Value4,Cat5,Value5,Cat6,Value6,Cat7
,Value7,
Cat8,Value8,TotalCats=Cat1+Cat2+Cat3+Cat
4+Cat5+Cat6+Cat7+Cat8,
TotalValues =
Value1+Value2+Value3+Value4+Value5+Value
6+Value7+Value8,
CriteriaStatus = CASE WHEN
(Cat1+Cat2+Cat3+Cat4+Cat5+Cat6+Cat7+Cat8
) =
(Value1+Value2+Value3+Value4+Value5+Valu
e6+Value7+Value8) THEN 'All'
WHEN
(Value1+Value2+Value3+Value4+Value5+Valu
e6+Value7+Value8) >=
((Cat1+Cat2+Cat3+Cat4+Cat5+Cat6+Cat7+Cat
8)/2) THEN 'Most'
ELSE 'Fails' END
FROM
(
SELECT Cat1=Case when OvertimeRequired = 1 then 1 else 0 end,
Value1=Case when OvertimeRequired = 1 then (Case when
WorkOvertime = 1 then 1 else 0 end) else 0 end,
Cat2=Case when SponserNonUS = 0 then 1 else 0 end,
Value2=Case when SponserNonUS = 0 then (Case when m.USCitizen
= 0 then 0 else 1 end) else 0 end,
Cat3=Case when p3.JobDistance > 0 then 1 else 0 end,
Value3 = Case when p3.JobDistance > 0 then (Case when
dbo.GetDistance(ZipCode,m.Zip) <= p3.JobDistance then 1 else 0 end) else 0
end,
Cat4=Case when p3.EducationLevel is not null then 1 else 0
end,
Value4=Case when p3.EducationLevel is not null then (Case
when m.EducationLevel >= p3.EducationLevel then 1 else 0 end) else 0 end,
Cat5=Case when p3.CareerLevel is not null then 1 else 0 end,
Value5=Case when p3.CareerLevel is not null then (Case when
m.CareerLevel >= p3.CareerLevel then 1 else 0 end) else 0 end,
Cat6=Case when p3.ExperienceLevel is not null then 1 else 0
end,
Value6=Case when p3.ExperienceLevel is not null then (Case
when m.ExperienceLevel >= p3.ExperienceLevel then 1 else 0 end) else 0 end,
Cat7=Case when p3.ScreenTestRequired = 1 then 1 else 0 end,
Value7=Case when p3.ScreenTestRequired = 1 then
(Case when ScreenTestScore >= p3.NotifyScreenMinScore then
1 else 0 end) else 0 end,
Cat8=Case when p3.SkillsTestRequired = 1 and
SkillsTestOffered is not null then 1 else 0 end,
Value8=Case when p3.SkillsTestRequired = 1 and
SkillsTestOffered is not null then
(Case when SkillsTestScore >= p3.NotifySkillsMinScore then
1 else 0 end) else 0 end
FROM applicant a
JOIN logon l on (a.UserID = l.UserID)
JOIN Position p3 on (a.PositionID = p3.PositionID)
JOIN ApplicantResume ar on (ar.ApplicantID = a.ApplicantID)
LEFT JOIN ApplicantPosition ap on (ap.ApplicantID =
a.ApplicantID)
LEFT JOIN MyInfo m on (m.UserID = a.UserID)
WHERE p3.PositionID = p.PositionID
) as a1
) as a2
WHERE CriteriaStatus = 'Most')
FROM position p
JOIN Companies c on (c.CompanyID = p.CompanyID)
The error I get is:
The column prefix 'p' does not match with a table name or alias name
used in the query.
Thanks,
Tomtshad (tfs@.dslextreme.com) writes:
> I am trying to run this routine which works fine until I put the outside
> Select in. The function consists of 4 Select statements that I need to
> do to get counts of various comparisons in my tables. The problem is
> that the inside PositionID has to refer to the outside PositionID and
> that is where I get the error ("WHERE p3.PositionID = p.PositionID ").
> How do I get it to refer this table (p).
Looking at your query, I will have to say that there is a whole lot of
fuzz just to get a COUNT(*). You must be able to simplify this. And maybe
even to the point you don't need to nest any derived tables.
Also, my experience is that subqueries in the SELECT list are often
expensive. Try to move the derived table to the FROM clause so you get
something like:
SELECT c.CompanyID, ... Qualified = d.cnt
FROM Positions p
JOIN Companies c ON ...
JOIN (SELECT position, cnt = COUNT(*)
FROM FROM applicant a
JOIN logon l on (a.UserID = l.UserID)
JOIN Position p3 on (a.PositionID = p3.PositionID)
JOIN ApplicantResume ar on (ar.ApplicantID = a.ApplicantID)
LEFT JOIN ApplicantPosition ap
on (ap.ApplicantID = a.ApplicantID)
LEFT JOIN MyInfo m on (m.UserID = a.UserID)
GROUP BY position) AS d ON p ON d.position = p.position
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm not sure if yours would work or not. I am just starting to look at it,
but I wasn't just trying to get the count().
The inside query is putting together a list of criteria and whether they
were met or not.
I then need to total the number of criteria and whether the user has met
each criteria or not (1=yes and 0= no). I use this total to tell whether
the user has met the criteria, met most or fails to meet them.
This is used in various reports. I now have a report that just gives me a
total number of users that have passed the criteria for each position. This
is why I need the inside querie to refer to the outside PositionID, but I
get an error on it. I have to do the inside 2 queries first to find out
whether users have passed or not.
It may not be the best way, but this was what I came up with a couple of
months ago on this group to make this work.
Thanks,
Tom
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9743B83E6219Yazorman@.127.0.0.1...
> tshad (tfs@.dslextreme.com) writes:
> Looking at your query, I will have to say that there is a whole lot of
> fuzz just to get a COUNT(*). You must be able to simplify this. And maybe
> even to the point you don't need to nest any derived tables.
> Also, my experience is that subqueries in the SELECT list are often
> expensive. Try to move the derived table to the FROM clause so you get
> something like:
> SELECT c.CompanyID, ... Qualified = d.cnt
> FROM Positions p
> JOIN Companies c ON ...
> JOIN (SELECT position, cnt = COUNT(*)
> FROM FROM applicant a
> JOIN logon l on (a.UserID = l.UserID)
> JOIN Position p3 on (a.PositionID = p3.PositionID)
> JOIN ApplicantResume ar on (ar.ApplicantID = a.ApplicantID)
> LEFT JOIN ApplicantPosition ap
> on (ap.ApplicantID = a.ApplicantID)
> LEFT JOIN MyInfo m on (m.UserID = a.UserID)
> GROUP BY position) AS d ON p ON d.position = p.position
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||tshad (tscheiderich@.ftsolutions.com) writes:
> I'm not sure if yours would work or not. I am just starting to look at
> it, but I wasn't just trying to get the count().
Ah, I see now that the outermost table had a WHERE clause. Still, all
the columns that comes before the definition of Criteria_status, has
no actual use in the query. (But I can understand that they are good
for debug.)
I can't say for sure that the outline that I gave will work for you,
as I don't have tables, nor sample data to test with (hint, hint!),
but I would encourage you to study the possibility.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97444A54ACE5Yazorman@.127.0.0.1...
> tshad (tscheiderich@.ftsolutions.com) writes:
> Ah, I see now that the outermost table had a WHERE clause. Still, all
> the columns that comes before the definition of Criteria_status, has
> no actual use in the query. (But I can understand that they are good
> for debug.)
>
Actually, I would be getting multiple records with Criteria_status equal to
either "All", "Most" or "Fail". But I am only interested in the ones that
are equal to "Most". But I have to read all the records and calculate the
Criterias and Values before I know if they fail or not.
I then need to filter out the "Most" ones and then count them.

> I can't say for sure that the outline that I gave will work for you,
> as I don't have tables, nor sample data to test with (hint, hint!),
> but I would encourage you to study the possibility.
I will.
But why was I getting the error?
And how can I get it work?
I have done subqueries before where the inside query references the outside
query. But I can't figure out why this one won't work.
Thanks,
Tom
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi tshad
I think I see what you're trying to do: in your set A2 you want to get
a count of rows BY PositionID, like so:
Position ID CountRows
1 6
2 3
3 10
etc.
Is this right?
To do this, you need to return a rowcount AND the position ID from A2 -
you can then join A2 to your other tables (position and Companies) on
PositionID, so that A2 doesn't have to try to refer to a column outside
itself. Instead of
SELECT Count(*) FROM (lots of SQL) AS A2
use
SELECT PositionID,Count(*) FROM (lots of SQL) AS A2 GROUP BY Position
ID.
Make this statement with the GROUP BY into a separate subquery, and
join it to the two other tables which you've put at the end:
SELECT c.CompanyID,p.[nb: qualified because there's now 2 PositionID
columns in the outer set]PositionID,JobTitleShort,PostedData =
replace(convert(varchar,p.DateCreated,6),' ',''),
totals.RowCount AS Qualified FROM
(SELECT PositionID,Count(*) FROM (lots of SQL) AS A2 GROUP BY
Position ID) totals
INNER JOIN
position p
ON totals.PositionID=p.PositionID
JOIN
Companies c
on (c.CompanyID = p.CompanyID)
WHERE... etc
By the way, you can also get rid of all the complicated calculation
within A2 - it's wasted, as having done all that calculation, SQL then
only returns a rowcount from the resulting set!
A2 (the SQL i've referred to as (lots of SQL) can be slimmed down to
something like this:
SELECT PositionID [qualify this, specifying which table it should come
from] FROM
applicant a
JOIN
logon l
etc
hope this helps.
cheers
Seb|||tshad (tscheiderich@.ftsolutions.com) writes:
> But why was I getting the error?
If you insist to get answer to that question, you better post the
CREATE TABLE statements for the table, so it's possible to play with
query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Fri, 6 Jan 2006 18:00:48 -0800, tshad wrote:
(snip)
>But why was I getting the error?
Hi Tom,
Normally, a derived table can't refer to columns from the outer query.
The table has to be materialized before the joins in the FROM clause can
be evaluated, so there's no way to know which row is referred to. (This
is the theoretic description - in reality, the optimizer will probably
choose a faster strategy).
If a derived table is used inside a subquery, it still can't refer to
other tables used in the subquery, but it can refer to the tables used
outside of the subquery. This is possible becuase the complete subquery
has to be re-evaluated for any row in the outer query anyway (again, in
theory).
It appears as if SQL Server is unable to recognise this situation if you
start nesting subqueries. I would consider this to be a bug. The very
simple script below will reproduce this behaviour on SQL Server 2000
SP4. I don't have SQL Server 2005 installed, so I can't tell if this is
fixed in SQL Server 2005.
CREATE TABLE t1 (a int, b int)
CREATE TABLE t2 (a int, b int)
go
-- Single derived table - no problem
SELECT (SELECT a
FROM (SELECT *
FROM t2
WHERE t2.b = t1.b
) AS Derived
) AS Subquery
FROM t1
go
-- Nested derived table - error
SELECT (SELECT a
FROM (SELECT *
FROM (SELECT *
FROM t2
WHERE t2.b = t1.b
) AS Inner_Derived
) AS Outer_Derived
) AS Subquery
FROM t1
go
DROP TABLE t1
DROP TABLE t2
go

>And how can I get it work?
Now that's the harder question, I guess.
Start with www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info) writes:
> It appears as if SQL Server is unable to recognise this situation if you
> start nesting subqueries. I would consider this to be a bug. The very
> simple script below will reproduce this behaviour on SQL Server 2000
> SP4. I don't have SQL Server 2005 installed, so I can't tell if this is
> fixed in SQL Server 2005.
Both your queries work on SQL 2005, so it appears to have been fixed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:h9k0s1pqtgusku3pmh58bqk47d67ab75uj@.
4ax.com...
> On Fri, 6 Jan 2006 18:00:48 -0800, tshad wrote:
> (snip)
> Hi Tom,
> Normally, a derived table can't refer to columns from the outer query.
> The table has to be materialized before the joins in the FROM clause can
> be evaluated, so there's no way to know which row is referred to. (This
> is the theoretic description - in reality, the optimizer will probably
> choose a faster strategy).
> If a derived table is used inside a subquery, it still can't refer to
> other tables used in the subquery, but it can refer to the tables used
> outside of the subquery. This is possible becuase the complete subquery
> has to be re-evaluated for any row in the outer query anyway (again, in
> theory).
> It appears as if SQL Server is unable to recognise this situation if you
> start nesting subqueries. I would consider this to be a bug. The very
> simple script below will reproduce this behaviour on SQL Server 2000
> SP4. I don't have SQL Server 2005 installed, so I can't tell if this is
> fixed in SQL Server 2005.
> CREATE TABLE t1 (a int, b int)
> CREATE TABLE t2 (a int, b int)
> go
> -- Single derived table - no problem
> SELECT (SELECT a
> FROM (SELECT *
> FROM t2
> WHERE t2.b = t1.b
> ) AS Derived
> ) AS Subquery
> FROM t1
> go
> -- Nested derived table - error
> SELECT (SELECT a
> FROM (SELECT *
> FROM (SELECT *
> FROM t2
> WHERE t2.b = t1.b
> ) AS Inner_Derived
> ) AS Outer_Derived
> ) AS Subquery
> FROM t1
> go
> DROP TABLE t1
> DROP TABLE t2
> go
Hi Hugo,
Yes, that is exactly what is happening. There may be a different way to
restructure the statement (maybe by a join to get rid of the inner table),
but I'm not sure how it would be done in my situation.
Maybe the inner_derived table has to reference the Outer_Derived and than
the Outer_Derived reference t1 somehow.

>
> Now that's the harder question, I guess.
> Start with www.aspfaq.com/5006.
This doesn't appear to be the faq you meant (or maybe it was) as doesn't
seem to have anything to do with query/subquery question.
Thanks,
Tom
> --
> Hugo Kornelis, SQL Server MVP

Images in SQL

Look up the usage and example of statements UPDATETEXT and WRITETEXT in SQL
Server Books Online.
AnithI looked those up and they just give examples of writing text to the fields.
I would like to know how to add a picture to the field.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OoEv9%23glGHA.4444@.TK2MSFTNGP02.phx.gbl...
> Look up the usage and example of statements UPDATETEXT and WRITETEXT in
> SQL Server Books Online.
> --
> Anith
>|||Image and nText fields are binary fields, like the old BLOB (binary large
object) fields.
To write an image into thsee fields you need to serialize the data into eith
er a
stream or use ADO or ADO.Net
A better question back to you is how and when do you want to get the picture
s
into the database?
Alternatively, you may wish to simply store the images somewhere and use a p
ath
to the raw image files, there are benefits and costs for both methods, in th
e
database or a pointer to a file.
HTH
JeffP...
<Preacher Man> wrote in message news:udRf$ChlGHA.4772@.TK2MSFTNGP04.phx.gbl...">
> I looked those up and they just give examples of writing text to the field
s.
> I would like to know how to add a picture to the field.
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OoEv9%23glGHA.4444@.TK2MSFTNGP02.phx.gbl...
>|||I am trying to write a simple application for printing employee badges. I
have VFP 9.0 that I will be using as the application writer.
Any ideas on how I should enter these images into the database? I can use a
VFP table or SQL is doesn't really matter to me in this case.
A path to the filename would be fine for me to use also, but how would I
implement that into a form?
Thanks for any info.
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23cB$WUhlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> Image and nText fields are binary fields, like the old BLOB (binary large
> object) fields.
> To write an image into thsee fields you need to serialize the data into
> either a
> stream or use ADO or ADO.Net
> A better question back to you is how and when do you want to get the
> pictures
> into the database?
> Alternatively, you may wish to simply store the images somewhere and use a
> path
> to the raw image files, there are benefits and costs for both methods, in
> the
> database or a pointer to a file.
> HTH
> JeffP...
>
> <Preacher Man> wrote in message
> news:udRf$ChlGHA.4772@.TK2MSFTNGP04.phx.gbl...
>|||Sorry, I'm out of the loop on VFP since 3.2... but google this...
image data into SQL
HTH
JeffP...
<Preacher Man> wrote in message news:%23pQTiXhlGHA.3740@.TK2MSFTNGP02.phx.gbl...en">
> I am trying to write a simple application for printing employee badges. I
> have VFP 9.0 that I will be using as the application writer.
> Any ideas on how I should enter these images into the database? I can use
a
> VFP table or SQL is doesn't really matter to me in this case.
> A path to the filename would be fine for me to use also, but how would I
> implement that into a form?
> Thanks for any info.
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:%23cB$WUhlGHA.4792@.TK2MSFTNGP02.phx.gbl...
>|||I notice that SQL 2000 has a datatype of image.
My question is after I setup the table and fields how do I populate the
table with image data?|||Look up the usage and example of statements UPDATETEXT and WRITETEXT in SQL
Server Books Online.
Anith|||I looked those up and they just give examples of writing text to the fields.
I would like to know how to add a picture to the field.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OoEv9%23glGHA.4444@.TK2MSFTNGP02.phx.gbl...
> Look up the usage and example of statements UPDATETEXT and WRITETEXT in
> SQL Server Books Online.
> --
> Anith
>|||Image and nText fields are binary fields, like the old BLOB (binary large
object) fields.
To write an image into thsee fields you need to serialize the data into eith
er a
stream or use ADO or ADO.Net
A better question back to you is how and when do you want to get the picture
s
into the database?
Alternatively, you may wish to simply store the images somewhere and use a p
ath
to the raw image files, there are benefits and costs for both methods, in th
e
database or a pointer to a file.
HTH
JeffP...
<Preacher Man> wrote in message news:udRf$ChlGHA.4772@.TK2MSFTNGP04.phx.gbl...">
> I looked those up and they just give examples of writing text to the field
s.
> I would like to know how to add a picture to the field.
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OoEv9%23glGHA.4444@.TK2MSFTNGP02.phx.gbl...
>|||I am trying to write a simple application for printing employee badges. I
have VFP 9.0 that I will be using as the application writer.
Any ideas on how I should enter these images into the database? I can use a
VFP table or SQL is doesn't really matter to me in this case.
A path to the filename would be fine for me to use also, but how would I
implement that into a form?
Thanks for any info.
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23cB$WUhlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> Image and nText fields are binary fields, like the old BLOB (binary large
> object) fields.
> To write an image into thsee fields you need to serialize the data into
> either a
> stream or use ADO or ADO.Net
> A better question back to you is how and when do you want to get the
> pictures
> into the database?
> Alternatively, you may wish to simply store the images somewhere and use a
> path
> to the raw image files, there are benefits and costs for both methods, in
> the
> database or a pointer to a file.
> HTH
> JeffP...
>
> <Preacher Man> wrote in message
> news:udRf$ChlGHA.4772@.TK2MSFTNGP04.phx.gbl...
>

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 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 and format statements

I am trying to format a database field to a Year format and check the results
against a Year value (the 2002 would become a paramater entered by the user).
Then if the results are true, then format the database field to display
month year. Below is the iif statement:
=iif(format(Fields!CREATION_DATE.Value, "yyyy") = 2002),
Format(Fields!CREATION_DATE.Value, "MMM yy"), 0)
I am getting an error stating: Argument not specified for parameter
'FalsePart' of 'Public Function IIF(Expression As Boolean, TruePart As
Object, FalsePart As Object) As Object'.
ThanksHey Mike:
You just got an extra paranthesis up there:
Try this:
=iif(format(Fields!CREATION_DATE.Value, "yyyy") = 2002,
Format(Fields!CREATION_DATE.Value, "MMM yy"), 0)
"Mike" wrote:
> I am trying to format a database field to a Year format and check the results
> against a Year value (the 2002 would become a paramater entered by the user).
> Then if the results are true, then format the database field to display
> month year. Below is the iif statement:
> =iif(format(Fields!CREATION_DATE.Value, "yyyy") = 2002),
> Format(Fields!CREATION_DATE.Value, "MMM yy"), 0)
> I am getting an error stating: Argument not specified for parameter
> 'FalsePart' of 'Public Function IIF(Expression As Boolean, TruePart As
> Object, FalsePart As Object) As Object'.
>
> Thanks

iif and case statements

Hi all,

I have to translate an Access query into sql. The query has the
following statement. I know SQL doesn't support iif, so can someone tell
me how to use the case statement to get the same result?

select field1,
IIf(Grand_total-50>0, grand_total-50, 0) AS field2,
field3

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!CASE WHEN grand_total>50 THEN grand_total-50 ELSE 0 END

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

"Hammy Hammy" <chris@.thehams.ca> wrote in message
news:3f5e54ee$0$62085$75868355@.news.frii.net...
> Hi all,
> I have to translate an Access query into sql. The query has the
> following statement. I know SQL doesn't support iif, so can someone tell
> me how to use the case statement to get the same result?
> select field1,
> IIf(Grand_total-50>0, grand_total-50, 0) AS field2,
> field3
> Thanks.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hammy Hammy <chris@.thehams.ca> wrote in message news:<3f5e54ee$0$62085$75868355@.news.frii.net>...
> Hi all,
> I have to translate an Access query into sql. The query has the
> following statement. I know SQL doesn't support iif, so can someone tell
> me how to use the case statement to get the same result?
> select field1,
> IIf(Grand_total-50>0, grand_total-50, 0) AS field2,
> field3
> Thanks.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

select field1,
case when grand_total > 50 then grand_total - 50 else 0 end as 'field2',
field 3
from ...

Simon

Ignoring Noise words in SQL 2005 Full Text Search

To ignore the noise words in the query microsoft remommends to execute the following statements, by which we can take advantage of the new transformation of noise words in CONTAINS queries:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'transform noise words', 1
GO
RECONFIGURE
GO

But this never works. Can any one please suggest how to make this work.(without modifying the full text text file)

Did you restart the service, this is a non-runtime value, so you will have to restart the service to apply the change.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I had restarted the Full Text Index Service, but still it did not work

Also tried re-starting SQL Service, still it did not work.

Ignoring Noise words in SQL 2005 Full Text Search

To ignore the noise words in the query microsoft remommends to execute the following statements, by which we can take advantage of the new transformation of noise words in CONTAINS queries:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'transform noise words', 1
GO
RECONFIGURE
GO

But this never works. Can any one please suggest how to make this work.(without modifying the full text text file)

Did you restart the service, this is a non-runtime value, so you will have to restart the service to apply the change.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I had restarted the Full Text Index Service, but still it did not work

Also tried re-starting SQL Service, still it did not work.

Ignoring Noise words in SQL 2005 Full Text Search

To ignore the noise words in the query microsoft remommends to execute the following statements, by which we can take advantage of the new transformation of noise words in CONTAINS queries:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'transform noise words', 1
GO
RECONFIGURE
GO

But this never works. Can any one please suggest how to make this work.(without modifying the full text text file)

Did you restart the service, this is a non-runtime value, so you will have to restart the service to apply the change.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I had restarted the Full Text Index Service, but still it did not work

Also tried re-starting SQL Service, still it did not work.