December 6, 2004 at 12:02 pm
I am writing query based on couple of table. I have one field in one of my table which store 1 or 0 insted of Pass / Fail.
I am running query and i am getting my resutls but above field show me 1 or 0. If i want a see Pass or Fail insted of 1 or 0. how should i write my query.
Exam:
SELECT TestLog.SerialNumber, TestLog.PassFail, IIf([PassFail]=1,'Pass','Fail') AS Expr1
FROM TestLog;
Gave me error message. Can you guys fix my query.
December 6, 2004 at 12:32 pm
Try this:
SELECT SerialNumber, PassFail,
CASE
WHEN [PassFail] = 1
THE 'Pass'
ELSE 'Fail'
END AS Expr1
FROM TestLog
(Note, this does not include any test for a NULL or any other number..., you may want to look up CASE in BOL and see if you want to include more...)
I wasn't born stupid - I had to study.
December 6, 2004 at 1:10 pm
DHaval I am sure that your query would work in Access and Excel. This is one of the areas that SQL differs from those applications. Once you get used to CASE, CONVERT, ISNULL, and some other functions, you will find SQL much more friendly and easier to use.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
December 7, 2004 at 6:20 am
Hi,
Since I don't know what your error message says, I would suggest you use a CASE instead of the IIf.
(I may be mistaken, but I don't think the IIf function will return values based on a value comparison i.e. PassFail = 1. )
I would suggest you substitute the following in place of your IIf.
CASE PassFail WHEN 1 THEN 'Pass' ELSE 'Fail' END AS Pass_Fail
Greg H
December 7, 2004 at 7:40 pm
In this case using "case" is the best way. However, for more complex resolutions this would just be another table that holds the integer value and a corresponding value, which you would then join to. That's the most common way for handling this for integers (but for bits use case).
cl
Signature is NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply