Replacing IIF with IF Construct in SQL Server 7

[Mike Thomas]

I am migrating date from Access 2000 to SQL Server and am trying to deal
with replacing the IIF with an IF.

In the simplified code below, I am getting an error around the IF statement.
Could someone let me know what's wrong here?

CREATE PROCEDURE _dumptest AS
Select descr,
IF quantity > 1
unitprc
ELSE
0
AS cost
From invcustl
GO

Many thanks
Mike Thomas

 

[Philipp Stiefel]

I am migrating date from Access 2000 to SQL Server and am trying to deal
with replacing the IIF with an IF.

In the simplified code below, I am getting an error around the IF statement.
Could someone let me know what's wrong here?

You can't use IF in a SQL-Statement, use CASE instead.


SELECT descr,
CASE quantity > 1
THEN unitprc
ELSE 0
END AS cost
FROM invcustl


BTW: Use the Owner-Qualifier when referencing sql-server-objects.
E.g. not just " ... FROM invcustl" but " .. FROM dbo.invcustl"

Cheers
Phil

 

[Mike Thomas]

Many thanks Phil,

I think that got me past one of the problems.

Now, when I run the statement below in the Query Analyser, I receive the
error "Incorrect syntax near '>'"

Select descr,
Case 1 > 2
Then unitprc
else 0
end as cost
from dbo.invcustl

Do you know what would be causing that error?

Many thanks
Mike Thomas

 

[RoyVidar]

Many thanks Phil,

I think that got me past one of the problems.

Now, when I run the statement below in the Query Analyser, I receive the
error "Incorrect syntax near '>'"

Select descr,
Case 1 > 2
Then unitprc
else 0
end as cost
from dbo.invcustl

Do you know what would be causing that error?

Many thanks
Mike Thomas

I don't think 1 will ever be greater than 2;-)

select descr,
case when quantity > 1
then
unitprc
else
0
end as cost
From dbo.invcustl

 

[Mike Thomas]

Thanks Roy-Vidar,

You are right, 1 will never be greater than 2. However, the problem I am
trying to solve here is why I am getting an error around the '>' sign. I do
not see why the code as I have written it should not at least run, and at
least always return zero.

Select descr,
Case 1 > 2
Then unitprc
else 0
end as cost
from dbo.invcustl

Tnaks
Mike Thomas

 

[RoyVidar]

Thanks Roy-Vidar,

You are right, 1 will never be greater than 2. However, the problem I am
trying to solve here is why I am getting an error around the '>' sign. I do
not see why the code as I have written it should not at least run, and at
least always return zero.

Select descr,
Case 1 > 2
Then unitprc
else 0
end as cost
from dbo.invcustl

Tnaks
Mike Thomas

Was that with or without the little keyword "when" in the Case When
statement?

 

[Mike Thomas]

Many thanks Roy-Vidar you were exactly right.
Mike Thomas

 

[Philipp Stiefel]

I think that got me past one of the problems.

Now, when I run the statement below in the Query Analyser, I receive the
error "Incorrect syntax near '>'"

Select descr,
Case 1 > 2
Then unitprc
else 0
end as cost
from dbo.invcustl

Do you know what would be causing that error?

Sorry, I'm afraid I made a little mistake there. I should be:

Select descr,
Case WHEN 1 > 2
Then unitprc
else 0
end as cost
from dbo.invcustl

I just missed the WHEN.

Cheers
Phil