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.
| Author |
Topic |
|
scottishcalvin
Starting Member
10 Posts |
 Posted - 2010-09-08 : 06:06:08
|
| Is there a limitation about a CASE() statement only being able to return a number/text/EvaluatedExpression ? I have the code below to seperate/turn a stored date into a year and a month.CASE WHEN THETABLE.CLSDT = 0 THEN 'Still Open' ELSE YEAR(DATE(693596-1+THETABLE.CLSDT-1)) END AS CLOSED_YEAR,CASE WHEN THETABLE.CLSDT = 0 THEN 'Still Open' ELSE CASE MONTH(DATE(693596-1+THETABLE.CLSDT-1)) WHEN 1 THEN 'a) January' WHEN 2 THEN 'b) February' WHEN 3 THEN 'c) March' WHEN 4 THEN 'd) April' WHEN 5 THEN 'e) May' WHEN 6 THEN 'f) June' WHEN 7 THEN 'g) July' WHEN 8 THEN 'h) August' WHEN 9 THEN 'i) September' WHEN 10 THEN 'j) October' WHEN 11 THEN 'k) November' ELSE 'l) December' END END AS CLOSED_MONTHWhen there is a number, it works fine but when it's equal to 0 (ie the file is open) then it only returns "Still Open" for the month. Given that the month column will always be a text expression, I presume this is something about only evaluating the year (rather than returning text) in some instances? When I experimented and replaced the calculated year with 'test' then it seemed to work |
|
|
scottishcalvin
Starting Member
10 Posts |
Posted - 2010-09-08 : 06:34:48
|
| A colleague pointed out to me that it's because a column can only hold one data type so:ELSE CAST(YEAR(DATE(693596-1+THETABLE.CLSDT-1)) AS VARCHAR(20)) END AS CLOSED_YEAR,I'd tried this earlier with TEXT rather than VARCHAR but it didn't make a difference, but that's because you can't convert straight from INT to TEXT (I think?) Anyway, that's why I'd assumed it was something to do with the CASE statement. |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-09-09 : 14:11:00
|
| >> Is there a limitation about a CASE() statement [sic] only being able to return a number/text/EvaluatedExpression ? <<First of all, there is no such thing as a CASE statement in SQL. We have a CASE expression. By definition, an expression has one and only one data type in SQL. So of course you cannot get different types back. Secondly, a table is made of rows which all have the same structure. The rows are made of columns which have one and only one data type. Again, by definition, you cannot have rows with mix3ed structures. The CASE expression looks down the list of THEN and ELSE clauses and finds the highest data type. That is the data type of the expression. If "cls_dt" means "something_close_date", then I would use temporal data types and not do all of this confusing numeric/string/temporal casting you have. SQL is strongly typed and does not behave like COBOL. Usually it is handy to have a table of reporting periods with start and end dates so you can use a BETWEEN predicate to aggregate thigns into them. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
CASE gives a text expression OR calculated number?