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 |
|
Heinz23
Yak Posting Veteran
84 Posts |
 Posted - 2008-10-12 : 04:54:21
|
Hi all,how could I make this query more easy?SELECT ML.CostCenter, ML.Position MA.CreationDate, MA.RowType, case when MA.CostCenter = ML.CostCenter Then 'Internal' else 'External' end as Place, case when MA.CostCenter = ML.CostCenter Then 'Initiator' else 'Participant' end as Role, case when MA.CostCenter = ML.CostCenter Then MA.CC1 else MA.CC2 as CostCenterName FROM MarkLines ML INNER JOIN Marks MA ON ML.MarkID = MA.MarkID ORDER BY MA.CreationDate DESC You see I have 3 times the same select, and probably there might be another 5 values I need to add based on this select. Wouldn't it be easier to compute it only once per row and then return the values? I try to use BEGIN and END at then THEN-ELSE-clause but was not successfull there.... Any ideas?Many thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-12 : 05:05:40
|
| If you're asking for reducing the case when then i will say current query is simplest way you can write them .since each one is to set values for different fields like Place,Role,.. i think you will need seperate case ...when for each. the alternative would be to use IF... ELSE but in that case you would require to repeat the SELECT batch for each case which i think is not better than the posted query. |
 |
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2008-10-13 : 06:32:13
|
| Hi Visakh16,thanks for your reply. I just thought that it would decrease the processing time if I only need the condition once instead of currently 3 times (later on maybe 7-8 times). But using an IF-Clause at the beginning (so that I have 2 different selects) would be probably best in my case, thanks! |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-13 : 11:07:22
|
| Like Visakh16 said, your current approach is the best and most optimized way of dealing with this situation. Using Case..When will only add a scalar computation into your query, but with IF..ELSE you will repeat the selects and therefore double your total cost. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-13 : 11:13:18
|
| And you can compare both methods by analysing th execution plan for both. |
|
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2008-10-13 : 13:22:33
|
Ah, I was confused... I thought of something like this one below, but I couldn't do it as MA.Costcenter and ML.Costcenter are no parameters but values in the rows.... In another SP I had something like "IF @Action = 'Update'..." and I mixed it up...  So I'll use the solution I posted originally, thanks for your advises!IF MA.CostCenter = ML.Costcenter BEGINSELECT ....ENDELSEBEGINSELECT....END |
|
|
|
|
|
|
|
|
Return several values based on existing values