Creating view Using IIf funtion in the Query

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)

Creating view Using IIf funtion in the Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-16 : 09:23:45

Prasanth writes "I want to create a view Using IIf function in the select statement. Give you a simpler version of what I'm trying to do.

create view ACTSCHDQTY as select
iif((Plates*PMult>Quant),Quant,Plates*PMult) as Qty from ORDSCHED where (Status='ACTV' OR Status='SCHD')

SQL Server Error
Error in list of function arguments: '>' not recognized.

I tried the following to check for the comparison operator

create view ACTSCHDQTY as select
iif(IsEmpty(Plates),Quant,Plates*PMult) as Qty from ORDSCHED where (Status='ACTV' OR Status='SCHD')
(Eventhough this is not what I want to do. I know I can Use IsNull instead)

I know that Sql server is not recognizing the IIf function
Why? Is there any other way to do it in one single Query?
Any help in this is apprciated
Thank You"

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-16 : 09:35:49

case when Plates * PMult > Quant then Quant else Plates * PMult end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqLover
Starting Member

10 Posts

Posted - 2003-12-16 : 11:41:36

quote:
Originally posted by nr

case when Plates * PMult > Quant then Quant else Plates * PMult end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Its aiways nice to be important
It's more important to be nice always

sqLover
Starting Member

10 Posts

Posted - 2003-12-16 : 11:52:54

quote:
Originally posted by sqLover
I'm the one originally posted this Question
I just registered in the forum.
I tried this and it doesnot work because I use Query Designer to create new views. It doesnot support Case structure
Is there any other way I can create views ?
Thanks for the reply
Prasanth

Its aiways nice to be important
It's more important to be nice always

Its aiways nice to be important
It's more important to be nice always

sqLover
Starting Member

10 Posts

Posted - 2003-12-16 : 13:57:02

I tried it in Query Analyser and it works
Thanks

Its aiways nice to be important
It's more important to be nice always

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources