Using case instead of IIF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using case instead of IIF

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-09-30 : 11:43:44

So I had the following query:


SELECT DISTINCT [Scratchpad1].Employee_Number, IIf([dif]<0,[Break]+[dif],[Break]) AS Paid, [ScratchPad1].name, 
[ScratchPad1].Date, [ScratchPad1].Day, IIf([OnTime1]>=225 And [OnTime1]<450,[OnTime1]+15,[OnTime1]+30) AS Break,
 Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Break] AS dif INTO Scratchpad2

FROM ScratchPad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
 IIf([OnTime1]>=225 And [OnTime1]<450,[OnTime1]+15,[OnTime1]+30), [ScratchPad1].LogIn

HAVING (((Sum([1_1ScratchPad].OnTime1))>=225));

which was written in Access by someone else and I've changed it to this:


SELECT DISTINCT [Scratchpad1].EmployeeNumber,  [ScratchPad1].Day,
case when [OnTime1]>=225 then [OnTime1] <=450 when [OnTime1]+15 then [OnTime1]+30) AS Breaks,
 Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaks] AS dif INTO Scratchpad2

FROM ScratchPad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
 IIf([OnTime1]>=225 And [OnTime1]<450,[OnTime1]+15,[OnTime1]+30), [ScratchPad1].LogIn

But I'm getting this error:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '<'.

Can someone please point out to me what's wrong with the syntax as I've written it?

Thank you

Doug

jleitao
Posting Yak Master

100 Posts

Posted - 2010-09-30 : 12:06:57

Look at this line:
...
case when [OnTime1]>=225 then [OnTime1] <=450 when [OnTime1]+15 then [OnTime1]+30) AS Breaks,
...

you have "... then [OnTime1] <=450 ..."

i think you need "...And [OnTime1]<450 then [OnTime1]+15 ..."

SELECT DISTINCT [Scratchpad1].EmployeeNumber, [ScratchPad1].Day,
case
when [OnTime1]>=225 AND [OnTime1] <=450
then [OnTime1]+15
ELSE [OnTime1]+30
END AS Breaks,
Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaks] AS dif INTO Scratchpad2

FROM ScratchPad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
IIf([OnTime1]>=225 And [OnTime1]<450,[OnTime1]+15,[OnTime1]+30), [ScratchPad1].LogIn

however i think your query is not right...
you have:
"...
case
when [OnTime1]>=225 AND [OnTime1] <=450
...
IIf([OnTime1]>=225 And [OnTime1]<450,
..."

dougancil
Posting Yak Master

217 Posts

Posted - 2010-09-30 : 12:25:26

Jleitao,

Thank you and sorry about the query. I'd added some things for testing but hadn't removed them before posting. Here is the new query:


SELECT DISTINCT [Scratchpad1].EmployeeNumber,  [ScratchPad1].Day,
case when [OnTime1]>=225 and [OnTime1] <=450 or [OnTime1]+15 then [OnTime1]+30 AS Breaks,
 Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaks] AS dif INTO Scratchpad2
FROM Scratchpad1

now it's telling me that my error is:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'then'.

jleitao
Posting Yak Master

100 Posts

Posted - 2010-09-30 : 12:36:31

you welcome.

I think you need:

SELECT DISTINCT [Scratchpad1].EmployeeNumber, [ScratchPad1].Day,
case when [OnTime1]>=225 and [OnTime1] <=450
THEN [OnTime1]+15
ELSE [OnTime1]+30 end
AS Breaks,
Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaks] AS dif INTO Scratchpad2
FROM Scratchpad1

And you need a "group by" because you have a agregate function (SUM) in your select

dougancil
Posting Yak Master

217 Posts

Posted - 2010-09-30 : 13:02:59

Jl,

I do have a group by statement, I just didn't include it in this thread. I'm trying to correct what I can piece by piece.

Here's the last pieces of this query:

[code]
SELECT DISTINCT [Scratchpad1].EmployeeNumber, [ScratchPad1].Day,
case when [OnTime1]>=225 and [OnTime1] <450
THEN [OnTime1]+15
ELSE [OnTime1]+30 end
AS Breaks,
Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaks] AS dif INTO Scratchpad2
FROM Scratchpad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
case when [OnTime1]>=225 And [OnTime1]<450
Then [OnTime1]+15
Else [OnTime1]+30 [Scratchpad1].Login

HAVING (((Sum([1_1ScratchPad].OnTime1))>=225));

I know that the syntax for the sum is incorrect and I have yet to find an equivalent for "having," but all else is good. Would the best choice to replace "Having" be grouped?

Thank you

Doug

dougancil
Posting Yak Master

217 Posts

Posted - 2010-09-30 : 14:18:35

Jl,

Sorry ... evidently I left a few things out of my query:

Here is the query as I got it :

SELECT DISTINCT [Scratchpad1].Employee_Number, IIf([dif]<0,[Break]+[dif],[Break]) AS Paid, [ScratchPad1].name,
[ScratchPad1].Date, [ScratchPad1].Day, IIf([OnTime1]>=225 And [OnTime1]<450,[OnTime1]+15,[OnTime1]+30) AS Break,
Sum([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Break] AS dif INTO Scratchpad2

FROM ScratchPad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
IIf([OnTime1]>=225 And [OnTime1]<450,[OnTime1]+15,[OnTime1]+30), [ScratchPad1].LogIn

HAVING (((Sum([1_1ScratchPad].OnTime1))>=225));

Here is how I've corrected it thus far:

SELECT DISTINCT [Scratchpad1].EmployeeNumber,
case when [dif]<0 and [Break]+[dif]
[Breaktime] AS Paid
[ScratchPad1].Date,[ScratchPad1].Day,
case when [OnTime1]>=225 and [OnTime1] <450
THEN [OnTime1]+15
ELSE [OnTime1]+30 end
AS [Breaktime],
Sum ([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaktime] AS dif INTO Scratchpad2
FROM Scratchpad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
case when [OnTime1]>=225 And [OnTime1]<450
Then [OnTime1]+15
Else [OnTime1]+30
End

But here's where the issues are
Here:
[Scratchpad1].EmployeeNumber,
case when [dif]<0 and [Break]+[dif]
[Breaktime] AS Paid

and here:
HAVING (((Sum([1_1ScratchPad].OnTime1))>=225));

Any help is appreciated.

Thanks

Doug

jleitao
Posting Yak Master

100 Posts

Posted - 2010-09-30 : 19:28:12

Hi again,

the CASE sintax is:

CASE
WHEN <Condition1> THEN <ACTION if condition1 is true>
WHEN <Condition2> THEN <ACTION if condition2 is true>
WHEN ....
ELSE <Action if all the previous condition are false>
END AS <COLUMN ALIAS>

so you need change your code to:
[Scratchpad1].EmployeeNumber,
CASE WHEN [dif]<0
THEN [Break]+[dif]
ELSE [Breaktime]
END AS Paid

hope this help.

jleitao

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-01 : 10:59:08

JL,

I have this now

SELECT DISTINCT [Scratchpad1].EmployeeNumber,
case when [dif]<0 then [Break]+[dif]
else [Breaktime]
END AS [Paid],
[ScratchPad1].Date,[ScratchPad1].Day,
case when [OnTime1]>=225 then [OnTime1]<450
THEN [OnTime1]+15
ELSE [OnTime1]+30
AS [Breaktime],
Sum ([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaktime] AS dif INTO Scratchpad2
FROM Scratchpad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
case when [OnTime1]>=225 And [OnTime1]<450
Then [OnTime1]+15
Else [OnTime1]+30
End

and I'm getting the following error:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '<'.

and I'm not seeing what the issue is with the syntax of the query, because I didn't change this from when I was working on it yesterday with you advice. I'm sure it's something simple I'm not seeing. Your help is appreciated.

Thanks

Doug

jleitao
Posting Yak Master

100 Posts

Posted - 2010-10-01 : 11:16:18

change your second case by:

case when [OnTime1]>=225 and [OnTime1] <450
THEN [OnTime1]+15
ELSE [OnTime1]+30 end
AS [Breaktime],

You have a "then" in the midle of the condition.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-01 : 11:37:06

Jl what about this having statement:

HAVING (((Format$((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")) Between [Start (YY/MM/DD)] And [End (YY/MM/DD)]));

Is that better as a group by?

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-01 : 11:49:22

Oh and when I'm running this query I get the following error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Breaktime'.

but I thought this
case when [OnTime1]>=225 then [OnTime1]<450
THEN [OnTime1]+15
ELSE [OnTime1]+30
AS [Breaktime],

is creating a column named breaktime. Am I wrong?

jleitao
Posting Yak Master

100 Posts

Posted - 2010-10-01 : 12:40:13

I don't understand what you need in the having statement. having works as the same way as WHERE statement.
Can you explain a little more your question?

About the [Breaktime] column, he doesn't exist in the Scratchpad1 table. You just can use table columns in your SQL command.
You are creating the column but you cant use it cause it doesn't exist yet.

Your query should be:

SELECT DISTINCT [Scratchpad1].EmployeeNumber,
case when [dif]<0 then [Break]+[dif]
else
(
case when [OnTime1]>=225 AND [OnTime1]<450
THEN [OnTime1]+15
ELSE [OnTime1]+30
END
)
END AS [Paid],
[ScratchPad1].Date,[ScratchPad1].Day,
case when [OnTime1]>=225 AND [OnTime1]<450
THEN [OnTime1]+15
ELSE [OnTime1]+30
END AS [Breaktime],
Sum ([ScratchPad1
...

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-01 : 13:40:20

JL,

With that said, should the where go before or after the End Statement in the query?

Like this:

SELECT DISTINCT [Scratchpad1].EmployeeNumber,
case when [dif]<0 then [Breaktime]+[dif]
else
(
case when [OnTime1]>=225 AND [OnTime1]<450
THEN [OnTime1]+15
ELSE [OnTime1]+30
END
)
END AS [Paid],
[ScratchPad1].Date,[ScratchPad1].Day,
case when [OnTime1]>=225 and [OnTime1]<450
THEN [OnTime1]+15
ELSE [OnTime1]+30 end
AS [Breaktime],
Sum ([ScratchPad1].OnTime1) AS SumOfOnTime1, [ScratchPad1].LogIn, [LogIn]-[Breaktime] AS dif INTO Scratchpad2
FROM Scratchpad1

GROUP BY [ScratchPad1].EmployeeNumber, [ScratchPad1].Name, [ScratchPad1].Date, [ScratchPad1].Day,
case when [OnTime1]>=225 And [OnTime1]<450
Then [OnTime1]+15
Else [OnTime1]+30
Where (((Sum([ScratchPad1].OnTime1))>=225));
End

or after the end? Also when trying to run the query with the where statement, it's telling me this:

Server: Msg 156, Level 15, State 1, Line 49
Incorrect syntax near the keyword 'Where'.
Server: Msg 170, Level 15, State 1, Line 49
Line 49: Incorrect syntax near 'Sum'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources