nctran
asked on
Iff function
Hi all,
I want to use IIF function in the select statement ie:
Seclect field1, iif(field1 = 0, field2, field3) as ResultField, iff(field4 = 0 , 'capital', 'city') as textfield
from atableName
It doesnt work, I have so far encountered an error : {Error in list of function arguments: '=' not recognized.Unable to parse query text.}
if anyone has come across this problem or experienced what caused the problem, Pleas help
Many thanks,
CT
I want to use IIF function in the select statement ie:
Seclect field1, iif(field1 = 0, field2, field3) as ResultField, iff(field4 = 0 , 'capital', 'city') as textfield
from atableName
It doesnt work, I have so far encountered an error : {Error in list of function arguments: '=' not recognized.Unable to parse query text.}
if anyone has come across this problem or experienced what caused the problem, Pleas help
Many thanks,
CT
Last Comment
frankytee
is this Access or SQL Server? IFF is not valid in MS SQL
use CASE
Seclect field1,
CASE field1
when 0 then field2
else field3
END as ResultField,
CASE field4
when 0 then 'capital'
else 'city'
END as textfield
from atableName
Seclect field1,
CASE field1
when 0 then field2
else field3
END as ResultField,
CASE field4
when 0 then 'capital'
else 'city'
END as textfield
from atableName
ASKER
Thank Frankytee,
"use case" statement is always return an error 'incorrect syntax', When do you need to use "USE CASE" statement?
And why the derived columns using "case" statement can not be used to create a calculated field?
for example
Seclect field1,
CASE field1
when 0 then field2
else field3
END as ResultField,
CASE field4
when 0 then 'capital'
else 'city'
END as textfield,
field5-ResultField as OtherField
from atableName
it returns an error : 'invalid column name ResultField'
{where field2, field3 and field5 are of integer type}
Should I have to do something extra in order to use the ResultField like above?
Thank heap for your help.
CT.
"use case" statement is always return an error 'incorrect syntax', When do you need to use "USE CASE" statement?
And why the derived columns using "case" statement can not be used to create a calculated field?
for example
Seclect field1,
CASE field1
when 0 then field2
else field3
END as ResultField,
CASE field4
when 0 then 'capital'
else 'city'
END as textfield,
field5-ResultField as OtherField
from atableName
it returns an error : 'invalid column name ResultField'
{where field2, field3 and field5 are of integer type}
Should I have to do something extra in order to use the ResultField like above?
Thank heap for your help.
CT.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
