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 |
|
bosefus
Starting Member
1 Post |
 Posted - 2003-06-23 : 15:50:22
|
| I'm trying to create a view that uses derived columnsto derive columns. Here is a snippet of the view:SELECT ASSET_GROUP_ID, REHAB_CYCLES, ACTN_DATE1, ACTN_FLAG1, CASE WHEN REHAB_CYCLES - ACTN_FLAG1 > 0 THEN ACTN_FLAG1 + 1 ELSE 0 END AS ACTN_FLAG2,CASE WHEN REHAB_CYCLES - ACTN_FLAG2 > 0 THEN ACTN_FLAG2 + 1 ELSE 0 END AS ACTN_FLAG3FROM V_ACTION_BLDUP_4There are actually a total of 15 actions to be calculated but here is the snippet with 3 actions. With this query i get a error stating that column ACTN_FLAG2 does not exist i believe since it's a derived column within the same select statement. So I chose to rewrite the query as follows:SELECT ASSET_GROUP_ID, REHAB_CYCLES, ACTN_DATE1, ACTN_FLAG1, ACTN_FLAG2,ACTN_FLAG3 = CASE WHEN REHAB_CYCLES - ACTN_FLAG2 > 0 THEN ACTN_FLAG2 + 1 ELSE 0 ENDFROM( SELECT ASSET_GROUP_ID, REHAB_CYCLES, ACTN_DATE1, ACTN_FLAG1, ACTN_FLAG2 = CASE WHEN REHAB_CYCLES - ACTN_FLAG1 > 0 THEN ACTN_FLAG1 + 1 ELSE 0 END FROM V_ACTION_BLDUP_4) AS STEP1This one actually works as it should but when I add in all 15 actions to be calculated the query is quite large and when run doesn't return. It eats up memory and processor but will not give me a result set. I waited 2 hours for it to return but it didn't so i stopped the process. That's unacceptable for this view. Is there another way I can rewrite it? Please keep in mind that there are 15 actions and not just 3 as in the example above. Thanks. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-23 : 16:20:47
|
| this looks like you are trying to toggle the action flags when they get >= REHAB_CYCLES?e.g. REHAB_CYCLES = 2, ACTN_FLAG1 = 1ACTN_FLAG1 = 1 ACTN_FLAG2 = 2ACTN_FLAG3 = 0ACTN_FLAG4 = 1ACTN_FLAG5 = 0ACTN_FLAG6 = 1If that's the case then there is a much simpler way to do it by comparing parity against the one that gets equal to REHAB_CYCLES.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-06-23 : 17:49:49
|
| You should try normalizing your database.From what you've posted:SELECT ASSET_GROUP_ID, REHAB_CYCLES, ACTN_DATE1, ACTN_FLAG1, ...etc...Let me guess: is there ACTN_DATE2, ACTN_DATE3, ACTN_DATE4, etc ?if so, you need to normalize your database and store each of those values in seperate ROWS in your table, not a seperate groups of COLUMNS. - Jeff |
|
|
|
|
|
|
|
|
Derived columns