Nested Iif in View / Stored Procedure

  • Thread starter Thread starter Candace
  • Start date Start date
C

Candace

How do you write a nested Iif statement in a view or a
stored procedure?

Listed below is what I have in a query in Access, I
realize [Day_CVAD_Date] will become dbo.Day_CVAD_Date and
I'm assuming the word Quarter is in my Alias column. So
specifically my question is what syntax do I use in
my 'Column' column?

Quarter: IIf(Month([Day_CVAD_Date])=4 Or Month
([Day_CVAD_Date])=5 Or Month([Day_CVAD_Date])=6,1,IIf(Month
([Day_CVAD_Date])=7 Or Month([Day_CVAD_Date])=8 Or Month
([Day_CVAD_Date])=9,2,IIf(Month([Day_CVAD_Date])=10 Or
Month([Day_CVAD_Date])=11 Or Month([Day_CVAD_Date])
=12,3,IIf(Month([Day_CVAD_Date])=1 Or Month
([Day_CVAD_Date])=2 Or Month([Day_CVAD_Date])=3,4,0))))

Candace
 
I got that far: Here's what I have and I'm not sure what
to do with respect to the expression names (Quarter,
Positive_Infection, Short_CVAD, Long_CVAD) I am figuring I
need to declare them but where do I do this?

Thanks
Candace

Alter PROCEDURE Sub_Annual_Histogram @Start_Date
datetime, @End_Date datetime, @Nursing_Unit nvarchar(25),
@Team nvarchar(50), @Program nvarchar(50), @Type_of_Vein
nvarchar(20)

AS SELECT CASE WHEN ((Month(dbo.Day_CVAD_Date) = 4 OR Month
(dbo.Day_CVAD_Date) = 5 OR Month(dbo.Day_CVAD_Date) = 6))
THEN Quarter = 1
WHEN ((Month(dbo.Day_CVAD_Date) = 7 OR Month
(dbo.Day_CVAD_Date) = 8 OR Month(dbo.Day_CVAD_Date) = 9)
THEN Quarter = 2
WHEN ((Month(dbo.Day_CVAD_Date) = 10 OR Month
(dbo.Day_CVAD_Date) = 11 OR Month(dbo.Day_CVAD_Date) = 12)
THEN Quarter = 3
WHEN ((Month(dbo.Day_CVAD_Date) = 1 OR Month
(dbo.Day_CVAD_Date) = 2 OR Month(dbo.Day_CVAD_Date) = 3))
THEN Quarter = 4
CASE WHEN (dbo.Infection_Nosocomial = 'Yes' AND
(dbo.Infection_Type = 'Blood' OR dbo.Infection_Type
= 'Both')) THEN Positive_Infection = 1
ELSE Positive_Infection = 0
CASE WHEN (dbo.Device_CVAD_Length = 'Short ') THEN
Short_CVAD = 1
ELSE Short_CVAD = 0
CASE WHEN (dbo.Device_CVAD_Length = 'Long') THEN
Long_CVAD = 1
ELSE Long_CVAD = 0
FROM dbo.Device INNER JOIN
dbo.Day ON
dbo.Device.Device_New_CVAD_ID = dbo.Day.Day_New_CVAD_ID
INNER JOIN
dbo.Patient_Care_Area ON
dbo.Day.Day_Patient_Care_Area_ID =
dbo.Patient_Care_Area.Patient_Care_Area_ID
INNER JOIN
dbo.Infection ON
dbo.Day.Day_ID = dbo.Infection.Infection_Day_ID
WHERE (dbo.Patient_Care_Area.Patient_Care_Area_Program
LIKE @Program) AND
(dbo.Patient_Care_Area.Patient_Care_Area_Team_Grouping
LIKE @Team) AND
(dbo.Patient_Care_Area.Patient_Care_Area_Nursing_Unit
LIKE @Nursing_Unit) AND
(dbo.Device.Device_Vein_Line LIKE @Type_of_Vein) AND
(dbo.Day.Day_CVAD_Date BETWEEN @Start_Date AND
@End_Date))
-----Original Message-----
IIF statements become CASE statements in SQL Server stored procedures

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

How do you write a nested Iif statement in a view or a
stored procedure?

Listed below is what I have in a query in Access, I
realize [Day_CVAD_Date] will become dbo.Day_CVAD_Date and
I'm assuming the word Quarter is in my Alias column. So
specifically my question is what syntax do I use in
my 'Column' column?

Quarter: IIf(Month([Day_CVAD_Date])=4 Or Month
([Day_CVAD_Date])=5 Or Month([Day_CVAD_Date])=6,1,IIf (Month
([Day_CVAD_Date])=7 Or Month([Day_CVAD_Date])=8 Or Month
([Day_CVAD_Date])=9,2,IIf(Month([Day_CVAD_Date])=10 Or
Month([Day_CVAD_Date])=11 Or Month([Day_CVAD_Date])
=12,3,IIf(Month([Day_CVAD_Date])=1 Or Month
([Day_CVAD_Date])=2 Or Month([Day_CVAD_Date])=3,4,0))))

Candace
Click to expand...


.
Click to expand...
 
Each CASE expression must end with the word END. It's like the closing
parenthesis in an IIf.

I got that far: Here's what I have and I'm not sure what
to do with respect to the expression names (Quarter,
Positive_Infection, Short_CVAD, Long_CVAD) I am figuring I
need to declare them but where do I do this?

Thanks
Candace

Alter PROCEDURE Sub_Annual_Histogram @Start_Date
datetime, @End_Date datetime, @Nursing_Unit nvarchar(25),
@Team nvarchar(50), @Program nvarchar(50), @Type_of_Vein
nvarchar(20)

AS SELECT CASE WHEN ((Month(dbo.Day_CVAD_Date) = 4 OR Month
(dbo.Day_CVAD_Date) = 5 OR Month(dbo.Day_CVAD_Date) = 6))
THEN Quarter = 1
WHEN ((Month(dbo.Day_CVAD_Date) = 7 OR Month
(dbo.Day_CVAD_Date) = 8 OR Month(dbo.Day_CVAD_Date) = 9)
THEN Quarter = 2
WHEN ((Month(dbo.Day_CVAD_Date) = 10 OR Month
(dbo.Day_CVAD_Date) = 11 OR Month(dbo.Day_CVAD_Date) = 12)
THEN Quarter = 3
WHEN ((Month(dbo.Day_CVAD_Date) = 1 OR Month
(dbo.Day_CVAD_Date) = 2 OR Month(dbo.Day_CVAD_Date) = 3))
THEN Quarter = 4
CASE WHEN (dbo.Infection_Nosocomial = 'Yes' AND
(dbo.Infection_Type = 'Blood' OR dbo.Infection_Type
= 'Both')) THEN Positive_Infection = 1
ELSE Positive_Infection = 0
CASE WHEN (dbo.Device_CVAD_Length = 'Short ') THEN
Short_CVAD = 1
ELSE Short_CVAD = 0
CASE WHEN (dbo.Device_CVAD_Length = 'Long') THEN
Long_CVAD = 1
ELSE Long_CVAD = 0
FROM dbo.Device INNER JOIN
dbo.Day ON
dbo.Device.Device_New_CVAD_ID = dbo.Day.Day_New_CVAD_ID
INNER JOIN
dbo.Patient_Care_Area ON
dbo.Day.Day_Patient_Care_Area_ID =
dbo.Patient_Care_Area.Patient_Care_Area_ID
INNER JOIN
dbo.Infection ON
dbo.Day.Day_ID = dbo.Infection.Infection_Day_ID
WHERE (dbo.Patient_Care_Area.Patient_Care_Area_Program
LIKE @Program) AND
(dbo.Patient_Care_Area.Patient_Care_Area_Team_Grouping
LIKE @Team) AND
(dbo.Patient_Care_Area.Patient_Care_Area_Nursing_Unit
LIKE @Nursing_Unit) AND
(dbo.Device.Device_Vein_Line LIKE @Type_of_Vein) AND
(dbo.Day.Day_CVAD_Date BETWEEN @Start_Date AND
@End_Date))
-----Original Message-----
IIF statements become CASE statements in SQL Server stored procedures

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

How do you write a nested Iif statement in a view or a
stored procedure?

Listed below is what I have in a query in Access, I
realize [Day_CVAD_Date] will become dbo.Day_CVAD_Date and
I'm assuming the word Quarter is in my Alias column. So
specifically my question is what syntax do I use in
my 'Column' column?

Quarter: IIf(Month([Day_CVAD_Date])=4 Or Month
([Day_CVAD_Date])=5 Or Month([Day_CVAD_Date])=6,1,IIf (Month
([Day_CVAD_Date])=7 Or Month([Day_CVAD_Date])=8 Or Month
([Day_CVAD_Date])=9,2,IIf(Month([Day_CVAD_Date])=10 Or
Month([Day_CVAD_Date])=11 Or Month([Day_CVAD_Date])
=12,3,IIf(Month([Day_CVAD_Date])=1 Or Month
([Day_CVAD_Date])=2 Or Month([Day_CVAD_Date])=3,4,0))))

Candace
Click to expand...


.
Click to expand...
Click to expand...
 
See below. I added "as whatever" after each Case When statement. All that
does is name the field...same as "Quarter: some experession" in Access.

Please let me know if that works and was what you were looking for

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

Candace said:
I got that far: Here's what I have and I'm not sure what
to do with respect to the expression names (Quarter,
Positive_Infection, Short_CVAD, Long_CVAD) I am figuring I
need to declare them but where do I do this?

Thanks
Candace

Alter PROCEDURE Sub_Annual_Histogram @Start_Date
datetime, @End_Date datetime, @Nursing_Unit nvarchar(25),
@Team nvarchar(50), @Program nvarchar(50), @Type_of_Vein
nvarchar(20)

AS SELECT CASE WHEN ((Month(dbo.Day_CVAD_Date) = 4 OR Month
(dbo.Day_CVAD_Date) = 5 OR Month(dbo.Day_CVAD_Date) = 6))
THEN Quarter = 1 as Quarter
WHEN ((Month(dbo.Day_CVAD_Date) = 7 OR Month
(dbo.Day_CVAD_Date) = 8 OR Month(dbo.Day_CVAD_Date) = 9)
THEN Quarter = 2 as Quarter
WHEN ((Month(dbo.Day_CVAD_Date) = 10 OR Month
(dbo.Day_CVAD_Date) = 11 OR Month(dbo.Day_CVAD_Date) = 12)
THEN Quarter = 3 as Quarter
WHEN ((Month(dbo.Day_CVAD_Date) = 1 OR Month
(dbo.Day_CVAD_Date) = 2 OR Month(dbo.Day_CVAD_Date) = 3))
THEN Quarter = 4 as Quarter
CASE WHEN (dbo.Infection_Nosocomial = 'Yes' AND
(dbo.Infection_Type = 'Blood' OR dbo.Infection_Type
= 'Both')) THEN Positive_Infection = 1
ELSE Positive_Infection = 0 as Positive_Infection
CASE WHEN (dbo.Device_CVAD_Length = 'Short ') THEN
Short_CVAD = 1
ELSE Short_CVAD = 0 as Short_CVAD
CASE WHEN (dbo.Device_CVAD_Length = 'Long') THEN
Long_CVAD = 1
ELSE Long_CVAD = 0 as Long_CVAD
FROM dbo.Device INNER JOIN
dbo.Day ON
dbo.Device.Device_New_CVAD_ID = dbo.Day.Day_New_CVAD_ID
INNER JOIN
dbo.Patient_Care_Area ON
dbo.Day.Day_Patient_Care_Area_ID =
dbo.Patient_Care_Area.Patient_Care_Area_ID
INNER JOIN
dbo.Infection ON
dbo.Day.Day_ID = dbo.Infection.Infection_Day_ID
WHERE (dbo.Patient_Care_Area.Patient_Care_Area_Program
LIKE @Program) AND
(dbo.Patient_Care_Area.Patient_Care_Area_Team_Grouping
LIKE @Team) AND
(dbo.Patient_Care_Area.Patient_Care_Area_Nursing_Unit
LIKE @Nursing_Unit) AND
(dbo.Device.Device_Vein_Line LIKE @Type_of_Vein) AND
(dbo.Day.Day_CVAD_Date BETWEEN @Start_Date AND
@End_Date))
-----Original Message-----
IIF statements become CASE statements in SQL Server stored procedures

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

How do you write a nested Iif statement in a view or a
stored procedure?

Listed below is what I have in a query in Access, I
realize [Day_CVAD_Date] will become dbo.Day_CVAD_Date and
I'm assuming the word Quarter is in my Alias column. So
specifically my question is what syntax do I use in
my 'Column' column?

Quarter: IIf(Month([Day_CVAD_Date])=4 Or Month
([Day_CVAD_Date])=5 Or Month([Day_CVAD_Date])=6,1,IIf (Month
([Day_CVAD_Date])=7 Or Month([Day_CVAD_Date])=8 Or Month
([Day_CVAD_Date])=9,2,IIf(Month([Day_CVAD_Date])=10 Or
Month([Day_CVAD_Date])=11 Or Month([Day_CVAD_Date])
=12,3,IIf(Month([Day_CVAD_Date])=1 Or Month
([Day_CVAD_Date])=2 Or Month([Day_CVAD_Date])=3,4,0))))

Candace
Click to expand...


.
Click to expand...
Click to expand...
 
Got that. I also changed my variable name Quarter to
Fiscal_Qtr because I wanted to make sure I was not trying
to use a reserved word.

Here's what I have now, when I try to save it I get the
error "ADO error: Line 3: Incorrect syntax near '='.
Line 3 coincides with
THEN Fiscal_Qtr = 1
I think I have this error because the expression/variable
names (Fiscal_Qtr, Positive_Infection, Short_CVAD,
Long_CVAD) are not declared. Where do I declare them,
where to I return them?

Alter PROCEDURE Sub_Annual_Histogram @Start_Date
datetime, @End_Date datetime, @Nursing_Unit nvarchar(25),
@Team nvarchar(50), @Program nvarchar(50), @Type_of_Vein
nvarchar(20)

AS SELECT CASE WHEN ((Month(dbo.Day_CVAD_Date) = 4 OR Month
(dbo.Day_CVAD_Date) = 5 OR Month(dbo.Day_CVAD_Date) = 6))
THEN Fiscal_Qtr = 1
WHEN ((Month(dbo.Day_CVAD_Date) = 7 OR Month
(dbo.Day_CVAD_Date) = 8 OR Month(dbo.Day_CVAD_Date) = 9)
THEN Fiscal_Qtr = 2
WHEN ((Month(dbo.Day_CVAD_Date) = 10 OR Month
(dbo.Day_CVAD_Date) = 11 OR Month(dbo.Day_CVAD_Date) = 12)
THEN Fiscal_Qtr = 3
WHEN ((Month(dbo.Day_CVAD_Date) = 1 OR Month
(dbo.Day_CVAD_Date) = 2 OR Month(dbo.Day_CVAD_Date) = 3))
THEN Fiscal_Qtr = 4 END
CASE WHEN (dbo.Infection_Nosocomial = 'Yes' AND
(dbo.Infection_Type = 'Blood' OR dbo.Infection_Type
= 'Both')) THEN Positive_Infection = 1
ELSE Positive_Infection = 0 END
CASE WHEN (dbo.Device_CVAD_Length = 'Short ') THEN
Short_CVAD = 1
ELSE Short_CVAD = 0 END
CASE WHEN (dbo.Device_CVAD_Length = 'Long') THEN
Long_CVAD = 1
ELSE Long_CVAD = 0 END
FROM dbo.Device INNER JOIN
dbo.Day ON
dbo.Device.Device_New_CVAD_ID = dbo.Day.Day_New_CVAD_ID
INNER JOIN
dbo.Patient_Care_Area ON
dbo.Day.Day_Patient_Care_Area_ID =
dbo.Patient_Care_Area.Patient_Care_Area_ID
INNER JOIN
dbo.Infection ON
dbo.Day.Day_ID = dbo.Infection.Infection_Day_ID
WHERE (dbo.Patient_Care_Area.Patient_Care_Area_Program
LIKE @Program) AND
(dbo.Patient_Care_Area.Patient_Care_Area_Team_Grouping
LIKE @Team) AND
(dbo.Patient_Care_Area.Patient_Care_Area_Nursing_Unit
LIKE @Nursing_Unit) AND
(dbo.Device.Device_Vein_Line LIKE @Type_of_Vein) AND
(dbo.Day.Day_CVAD_Date BETWEEN @Start_Date AND
@End_Date))
 
I don't see what the problem yet, but you could simplify the syntax (and thus
make it easier to debug) by using the IN() operator instead of all those OR
junctions for different criteria on the same expression, e.g. CASE WHEN
Month(dbo.Day_CVAD_Date) IN (4,5,6) THEN 1 ...
 
Yes that was the sort of thing I was looking for but it
still does not work. I was getting the same error message
"ADO error: Line 3: Incorrect syntax near '='.
Line 3 coincides with THEN Fiscal_Qtr = 1

From what I can gather the computer would have no idea
what the expression names (Quarter, Positive_Infection,
Short_CVAD, Long_CVAD) are so I declared them as outputs
as well. I read in a book that if you want to output a
value you need to do say so when you declare it and when
you execute it. THis is what I have now.

I was thinking that declaring them as outputs then doing
the AS Quarter was duplication so I tried this both
separately and then together, I get the same error. It
does not seem to like it when I set the variable to a
value.

Alter PROCEDURE Sub_Annual_Histogram @Start_Date
datetime, @End_Date datetime, @Nursing_Unit nvarchar(25),
@Team nvarchar(50), @Program nvarchar(50), @Type_of_Vein
nvarchar(20),
@Fiscal_Quarter int OUTPUT, @Positive_Infection int
OUTPUT, @Short_CVAD int OUTPUT, @Long_CVAD int OUTPUT

AS SELECT CASE WHEN (Month(dbo.Day_CVAD_Date) IN (4,5,6))
THEN @Fiscal_Quarter = 1 as @Fiscal_Quarter OUTPUT
WHEN (Month(dbo.Day_CVAD_Date) IN (7,8,9)) THEN
@Fiscal_Quarter = 2 as @Fiscal_Quarter OUTPUT
WHEN (Month(dbo.Day_CVAD_Date) IN (10,11,12)) THEN
@Fiscal_Quarter = 3 as @Fiscal_Quarter OUTPUT
WHEN (Month(dbo.Day_CVAD_Date) IN (1,2,3)) THEN
@Fiscal_Quarter = 4 as @Fiscal_Quarter OUTPUT END
CASE WHEN (dbo.Infection_Nosocomial = 'Yes' AND
(dbo.Infection_Type = 'Blood' OR dbo.Infection_Type
= 'Both')) THEN @Positive_Infection = 1 AS
@Positive_Infection OUTPUT
ELSE @Positive_Infection = 0 AS @Positive_Infection
OUTPUT END
CASE WHEN (dbo.Device_CVAD_Length = 'Short ') THEN
@Short_CVAD = 1 AS @Short_CVAD OUTPUT
ELSE @Short_CVAD = 0 AS @Short_CVAD OUTPUT END
CASE WHEN (dbo.Device_CVAD_Length = 'Long') THEN
@Long_CVAD = 1 AS @Long_CVAD OUTPUT
ELSE @Long_CVAD = 0 AS @Long_CVAD OUTPUT END
FROM dbo.Device INNER JOIN
dbo.Day ON
dbo.Device.Device_New_CVAD_ID = dbo.Day.Day_New_CVAD_ID
INNER JOIN
dbo.Patient_Care_Area ON
dbo.Day.Day_Patient_Care_Area_ID =
dbo.Patient_Care_Area.Patient_Care_Area_ID
INNER JOIN
dbo.Infection ON
dbo.Day.Day_ID = dbo.Infection.Infection_Day_ID
WHERE (dbo.Patient_Care_Area.Patient_Care_Area_Program
LIKE @Program) AND
(dbo.Patient_Care_Area.Patient_Care_Area_Team_Grouping
LIKE @Team) AND
(dbo.Patient_Care_Area.Patient_Care_Area_Nursing_Unit
LIKE @Nursing_Unit) AND
(dbo.Device.Device_Vein_Line LIKE @Type_of_Vein) AND
(dbo.Day.Day_CVAD_Date BETWEEN @Start_Date AND
@End_Date))
 
Candace,

SQL can't be structured the way you have it. Also, remember that SQL is set
based, not procedural. Looking at your code it would seem that you are
expecting only one row to be returned. Using the following set-based
operation should give you the results in a recordset:

ALTER PROCEDURE Sub_Annual_Histogram
@Start_Date datetime,
@End_Date datetime,
@Nursing_Unit varchar(25),
@Team varchar(50),
@Program varchar(50),
@Type_of_Vein varchar(20),

AS

SELECT CASE WHEN (Month(Day_CVAD_Date) IN (4,5,6)) THEN
1
WHEN (Month(Day_CVAD_Date) IN (7,8,9)) THEN
2
WHEN (Month(Day_CVAD_Date) IN (10,11,12)) THEN
3
WHEN (Month(Day_CVAD_Date) IN (1,2,3)) THEN
4 END as Fiscal_Quarter,
CASE WHEN (Infection_Nosocomial = 'Yes' AND
(Infection_Type = 'Blood' OR
Infection_Type = 'Both')) THEN
1 ELSE 0 END AS Positive_Infection,
CASE WHEN (Device_CVAD_Length = 'Short ') THEN
1 ELSE 0 END AS Short_CVAD,
CASE WHEN (Device_CVAD_Length = 'Long') THEN
1 ELSE 0 END AS Long_CVAD
FROM Device d INNER JOIN Day ON
d.Device_New_CVAD_ID = Day.Day_New_CVAD_ID
INNER JOIN
Patient_Care_Area pca ON
Day.Day_Patient_Care_Area_ID = pca.Patient_Care_Area_ID
INNER JOIN
Infection i ON
Day.Day_ID = i.Infection_Day_ID
WHERE (pca.Patient_Care_Area_Program LIKE @Program) AND
(pca.Patient_Care_Area_Team_Grouping LIKE @Team) AND
(pca.Patient_Care_Area_Nursing_Unit LIKE @Nursing_Unit) AND
(d.Device_Vein_Line LIKE @Type_of_Vein) AND
(Day.Day_CVAD_Date BETWEEN @Start_Date AND @End_Date)

I hope that helps.

-Daran
 
That helps alot, my stored procedure will finally save. I
tried opening the report and got another error.

The expression 'The expression 'Sum(Iif(([Fiscal_Quarter]
=1) and ([Short_CVAD]=1),1,0))' is invalid.@Aggregate
functions are only allowed on output fields of the record
source.@1@
1' is invalid
Aggregate functions are only allowed on output fields of
the record source.

So how do I modify my stored procedure to make these
variables output fields? From what I understand I need to
have the word OUTPUT when I declare each variable and the
word OUTPUT when I execute it.
 
Back
Top