Is Transcender Exam Wrong?
-
Hi,
I am stuck on a transcender question I got wrong for exam 70-229. Transcender claims the following is the correct Answer:
SELECT
ProductName,
UnitsInStock,
ReorderLevel =
CASE WHEN ReorderLevel IS NOT NULL THEN ReorderLevel ELSE 'Not Specified' END
FROM
Products
This answer is based on the Products table in the Northwind db, but the default constraint on ReorderLevel column is 0, so it does work until you actually make one of the values in this column NULL then it breaks because of a datatype mismatch. In the transcender notes it claims that Reorderlevel is an alias and not the column name, I can't figure out how to do that with a case statement????????
-
What was the question ?
Technically it is incorrect and needs to CAST() the ReOrderLevel so that the same column can hold numbers and 'Not Specified'.
Depends on what the question was getting at.
-
Shortened Question:
The data type for Reorder Level is smallint and the column allows nulls. You need to produce a report that lists all products, their currently available quantities and reorder levels. For each product for which the reorder level is not specified, the character string, 'Not Specified', must appear in the report.
In the notes it says that ReorderLevel is not a column name but an alias, so if that were true would this query work and how? I don't see how since they never us the 'AS' keyword to specify an alias for the column and I couldn't figure out how to use an alias for the column with the case statement, If anybody knows please give me an example.
Thanks!
-
ReorderLevel =
CASE
WHEN ReorderLevel IS NOT NULL THEN ReorderLevel
ELSE 'Not Specified'
END
The "Reorderlevel" in red font is the alias. This is old-style T-SQL aliasing inherited from Sybase.
The alternative and more standard syntax is:
CASE
WHEN ReorderLevel IS NOT NULL THEN ReorderLevel
ELSE 'Not Specified'
END As ReorderLevel
-
Query ANalyzer doesn't like to run eithier of those statements because of a datatype mismatch. I still think it is incorrect without the cast and I can't seem to get the syntax correct even with the cast.
-
I agree, the answer is technically incorrect without the cast.
The syntax with the cast is below. This runs in my Northwind with no errors:
SELECT
ProductName,
UnitsInStock,
CASE
WHEN ReorderLevel IS NOT NULL THEN Cast(ReorderLevel as varchar)
ELSE 'Not Specified'
END As ReorderLevel
FROM
Products
-
I see what I was doing wrong with the cast now, Thanks for the correct answer!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply