Replacing IIF with IF Construct in SQL Server 7

  • Thread starter Thread starter Mike Thomas
  • Start date Start date
M

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
 
Mike Thomas said:
I am migrating date from Access 2000 to SQL Server and am trying to deal
with replacing the IIF with an IF.
Click to expand...
In the simplified code below, I am getting an error around the IF statement.
Could someone let me know what's wrong here?
Click to expand...

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
 
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
 
Mike Thomas wrote in message said:
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
Click to expand...

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
 
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
 
Mike Thomas wrote in message said:
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
Click to expand...

Was that with or without the little keyword "when" in the Case When
statement?
 
Mike said:
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?
Click to expand...

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
 
Back
Top