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 |
|
pgmr1998
Yak Posting Veteran
66 Posts |
 Posted - 2010-10-08 : 14:41:10
|
| I calculate a field called TimeCode, but I would like to reference its value in the calculation of another field later on in the code. How can this be done without repeating the calc for TimeCode again?Here is the code for TimeCode field:CASE WHEN RIGHT(TS_NonWorkEventBooking.EventName,1) = ')' THEN LEFT(RIGHT(TS_NonWorkEventBooking.EventName,3),2) ELSE RIGHT(LEFT(TM_CategoryValue.Name,3),2) END AS [TimeCode], |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-10-08 : 16:17:10
|
| Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html Your real problem is that you don't know how SQL works. It is an all-at-once language; whole rows and table come into being as a single unit of work. This is not left to right processing. You also don't know that a column is nothing like a field. For example, if the table were properly designed, you would not have to cut up the data as if you were still writing COBOL in 1960. If time_code is a meaningful data element, it will be in a column with proper constraints. You can force the computation into a CTE; here is a skeleton.WITH Something (something_key, time_code)AS(SELECT something_key, CASE WHEN RIGHT(event_name, 1) = ')' THEN LEFT(RIGHT(event_name3), 2) ELSE RIGHT(LEFT(something_name3), 2) END AS time_code FROM TM_CategoryValue AS C, TS_NonWorkEventBooking AS B WHERE ???)SELECT .. FROM Something, ;--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 |
|
|
|
|
|
|
|
Value Reference