|
-
Using IIf() in a SELECT statement
Hi,
I am trying to execute the code below in a stored procedure, but the compiler returns variously:
'IIf' is not a recognized function name
'IsEmpty' is not a recognized function name
Can someone please show me how to get the conditional field value?
Many thanks!!!
SELECT @par_strSquen AS strSquen,
s.strAcctID,
IIf (IsEmpty(@par_strScenStep),s.strScenStep,@par_strS cenStep) AS strScenStep
FROM Schedule AS s ...
-
Using IIf() in a SELECT statement (reply)
I don't IIF is SQL Server function, i know it is VB function.
Rau
------------
Laura Grahame at 7/6/01 3:15:58 PM
Hi,
I am trying to execute the code below in a stored procedure, but the compiler returns variously:
'IIf' is not a recognized function name
'IsEmpty' is not a recognized function name
Can someone please show me how to get the conditional field value?
Many thanks!!!
SELECT @par_strSquen AS strSquen,
s.strAcctID,
IIf (IsEmpty(@par_strScenStep),s.strScenStep,@par_strS cenStep) AS strScenStep
FROM Schedule AS s ...
-
Using IIf() in a SELECT statement (reply)
I beleive,
IIF and IsEmpty are functions (OLAP services support functions) of Visual Basic for Applications
Check Books Online and search for isempty
you can see the heading Visual Basic for applications
and whichever functions are mention '*' are not supported.
IIF and IsEmpty are the one withn '*' which is not supported.
- Anu
------------
Laura Grahame at 7/6/01 3:15:58 PM
Hi,
I am trying to execute the code below in a stored procedure, but the compiler returns variously:
'IIf' is not a recognized function name
'IsEmpty' is not a recognized function name
Can someone please show me how to get the conditional field value?
Many thanks!!!
SELECT @par_strSquen AS strSquen,
s.strAcctID,
IIf (IsEmpty(@par_strScenStep),s.strScenStep,@par_strS cenStep) AS strScenStep
FROM Schedule AS s ...
-
Using IIf() in a SELECT statement (reply)
TRY TO USE THE WHEN INSTAED IIF. Its the alternative to IIF in SQL Server. IIF WORKs FINE WHEN YOU EMBED IT IN ACCESS SQL CODE.
HERE IS THE EXAMPLE:
Use a SELECT statement with a simple CASE function
Within a SELECT statement, a simple CASE function allows only an equality check; no other comparisons are made. This example uses the CASE function to alter the display of book categories to make them more understandable.
USE pubs
GO
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO
Here is the result set:
Category Shortened Title Price
------------------- ------------------------- --------------------------
Business You Can Combat Computer S 2.99
Business Cooking with Computers: S 11.95
Business The Busy Executive's Data 19.99
Business Straight Talk About Compu 19.99
avg
==========================
13.73
Category Shortened Title Price
------------------- ------------------------- --------------------------
Modern Cooking The Gourmet Microwave 2.99
Modern Cooking Silicon Valley Gastronomi 19.99
avg
==========================
11.49
Category Shortened Title Price
------------------- ------------------------- --------------------------
Popular Computing Secrets of Silicon Valley 20.00
Popular Computing But Is It User Friendly? 22.95
avg
==========================
21.48
Category Shortened Title Price
------------------- ------------------------- --------------------------
Psychology Life Without Fear 7.00
Psychology Emotional Security: A New 7.99
Psychology Is Anger the Enemy? 10.95
Psychology Prolonged Data Deprivatio 19.99
Psychology Computer Phobic AND Non-P 21.59
avg
==========================
13.50
Category Shortened Title Price
------------------- ------------------------- --------------------------
Traditional Cooking Fifty Years in Buckingham 11.95
Traditional Cooking Sushi, Anyone? 14.99
Traditional Cooking Onions, Leeks, and Garlic 20.95
avg
==========================
15.96
(21 row(s) affected)
HOPE IT WILL WORK.
AHMED
------------
rau at 7/6/01 3:24:59 PM
I don't IIF is SQL Server function, i know it is VB function.
Rau
------------
Laura Grahame at 7/6/01 3:15:58 PM
Hi,
I am trying to execute the code below in a stored procedure, but the compiler returns variously:
'IIf' is not a recognized function name
'IsEmpty' is not a recognized function name
Can someone please show me how to get the conditional field value?
Many thanks!!!
SELECT @par_strSquen AS strSquen,
s.strAcctID,
IIf (IsEmpty(@par_strScenStep),s.strScenStep,@par_strS cenStep) AS strScenStep
FROM Schedule AS s ...
-
Using IIf() in a SELECT statement (reply)
Ahmed,
It worked like a charm! Thanks so much for your help! :-))
Laura
------------
AHMED at 7/6/01 4:33:48 PM
TRY TO USE THE WHEN INSTAED IIF. Its the alternative to IIF in SQL Server. IIF WORKs FINE WHEN YOU EMBED IT IN ACCESS SQL CODE.
HERE IS THE EXAMPLE:
Use a SELECT statement with a simple CASE function
Within a SELECT statement, a simple CASE function allows only an equality check; no other comparisons are made. This example uses the CASE function to alter the display of book categories to make them more understandable.
USE pubs
GO
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO
Here is the result set:
Category Shortened Title Price
------------------- ------------------------- --------------------------
Business You Can Combat Computer S 2.99
Business Cooking with Computers: S 11.95
Business The Busy Executive's Data 19.99
Business Straight Talk About Compu 19.99
avg
==========================
13.73
Category Shortened Title Price
------------------- ------------------------- --------------------------
Modern Cooking The Gourmet Microwave 2.99
Modern Cooking Silicon Valley Gastronomi 19.99
avg
==========================
11.49
Category Shortened Title Price
------------------- ------------------------- --------------------------
Popular Computing Secrets of Silicon Valley 20.00
Popular Computing But Is It User Friendly? 22.95
avg
==========================
21.48
Category Shortened Title Price
------------------- ------------------------- --------------------------
Psychology Life Without Fear 7.00
Psychology Emotional Security: A New 7.99
Psychology Is Anger the Enemy? 10.95
Psychology Prolonged Data Deprivatio 19.99
Psychology Computer Phobic AND Non-P 21.59
avg
==========================
13.50
Category Shortened Title Price
------------------- ------------------------- --------------------------
Traditional Cooking Fifty Years in Buckingham 11.95
Traditional Cooking Sushi, Anyone? 14.99
Traditional Cooking Onions, Leeks, and Garlic 20.95
avg
==========================
15.96
(21 row(s) affected)
HOPE IT WILL WORK.
AHMED
------------
rau at 7/6/01 3:24:59 PM
I don't IIF is SQL Server function, i know it is VB function.
Rau
------------
Laura Grahame at 7/6/01 3:15:58 PM
Hi,
I am trying to execute the code below in a stored procedure, but the compiler returns variously:
'IIf' is not a recognized function name
'IsEmpty' is not a recognized function name
Can someone please show me how to get the conditional field value?
Many thanks!!!
SELECT @par_strSquen AS strSquen,
s.strAcctID,
IIf (IsEmpty(@par_strScenStep),s.strScenStep,@par_strS cenStep) AS strScenStep
FROM Schedule AS s ...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|
Reply With Quote