Replacing IIF with IF Construct in SQL Server 7

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike Thomas - 11 Nov 2005 14:34 GMT

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

Reply to this Message

Philipp Stiefel - 11 Nov 2005 15:12 GMT

> 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

Reply to this Message

Mike Thomas - 11 Nov 2005 17:01 GMT

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 am migrating date from Access 2000 to SQL Server and am trying to deal
>> with replacing the IIF with an IF.
[quoted text clipped - 17 lines]
> Cheers
> Phil

Reply to this Message

RoyVidar - 11 Nov 2005 18:09 GMT

Mike Thomas wrote in message <e9zZSGu5FHA.2036@TK2MSFTNGP14.phx.gbl> :

> Many thanks Phil,
>
[quoted text clipped - 14 lines]
> 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

Signature

Roy-Vidar

Reply to this Message

Mike Thomas - 11 Nov 2005 18:23 GMT

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 <e9zZSGu5FHA.2036@TK2MSFTNGP14.phx.gbl> :
>> Many thanks Phil,
[quoted text clipped - 26 lines]
> end as cost
> From dbo.invcustl

Reply to this Message

RoyVidar - 11 Nov 2005 18:38 GMT

Mike Thomas wrote in message <OuZlI0u5FHA.1464@tk2msftngp13.phx.gbl> :

> Thanks Roy-Vidar,
>
[quoted text clipped - 45 lines]
>>
>> -- Roy-Vidar

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

Signature

Roy-Vidar

Reply to this Message

Mike Thomas - 11 Nov 2005 20:05 GMT

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

> Mike Thomas wrote in message <OuZlI0u5FHA.1464@tk2msftngp13.phx.gbl> :
>> Thanks Roy-Vidar,
[quoted text clipped - 49 lines]
> Was that with or without the little keyword "when" in the Case When
> statement?

Reply to this Message

Philipp Stiefel - 12 Nov 2005 09:49 GMT

> I think that got me past one of the problems.
>
[quoted text clipped - 9 lines]
>
> 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

Reply to this Message