Case within a case within a case....

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Case within a case within a case....

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-06 : 12:02:42

Heres a bueatiful updat for ya using case after case after case... It runs, but I was wondering if there was something more efficient to do something like this. It's just one large data format but it gets run nightly on a large table.

update Resource_Interval_Property
set Interval_property_value = case
when Interval_property_type = 'oil allowables Ind' then case
when Interval_property_value = 'y' then 'O-38 Recieved'
when Interval_property_value = 'n' then 'O-38 not-recieved'
end
when Interval_property_type = 'Net Pay Isopach Ind' then case
when Interval_property_value = 'Y' then 'Isopached'
when Interval_property_value = 'M' then 'Mapable'
when Interval_property_value = 'H' then 'Horizontal'
end
when Interval_property_type = 'Pay Assignment Ind ' then case
when Interval_property_value = 'Y' then case
when interval_res = 'y' then 'Pay Assignment'
else 'Former Pay assignment'
end
when Interval_property_value = 'N' then 'No Assignment'
when Interval_property_value = 'J' then 'Joint Assignment'
end
end
where strat_resource = 'record'

I cut it off there and added the where... Theres actually a couple more cases added to the end of that. Any input on perhaps better syntax or methods for something like this would be helpful

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-06 : 12:14:59

Why not just create a table for the IP_type and IP_value combinations and add a column for the replacement value? Then do a 2-column join to that table:

UPDATE R SET interval_property_value=A.Replace_Value
FROM Resource_Interval_Property R
INNER JOIN Replacement_Values A
ON (R.interval_property_type=A.interval_property_type AND R.interval_property_value=A.interval_property_value)

The Replacement_Values table would look like:

interval_property_type    interval_property_value    Replace_Value
oil allowables Ind        y                          O-38 Received
oil allowables Ind        n                          O-38 not-received
Net Pay Isopach Ind       Y                          Isopached
Net Pay Isopach Ind       M                          Mapable
Net Pay Isopach Ind       H                          Horizontal

You get the idea...

You wouldn't even have to do this update, you can just do a simple join to this table whenever you need the results in the query. Unless you have several thousand combinations, it'll query just as fast as if the column was in Resource_Interval_Property.

Edited by - robvolk on 06/06/2002 12:17:01

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-06 : 12:32:30

Hmm, interesting angle. Never thought along those lines.

when Interval_property_type = 'Pay Assignment Ind ' then case
when Interval_property_value = 'Y' then case
when interval_res = 'y' then 'Pay Assignment'
else 'Former Pay assignment'
end
when Interval_property_value = 'N' then 'No Assignment'
when Interval_property_value = 'J' then 'Joint Assignment'
end

for that one I'd need to have 2 replacement values tables though. Hmm, I like the idea... I'll have to run a couple preformance tests (the second the eggheads here finally give me permissions to use the **&%&%^$*&%& profiler... sorry, hot topic in the office ;)

I wonder how deep it can go, because the more data I get in the project the more I'm seeing that it may be something like
when Interval_property_type = 'Pay Assignment Ind ' then case
when Interval_property_value = 'Y' then case
when interval_res = 'y' then case
when yetanothercolumn = 'yet another answer' case
....

until I'd be hitting something silly like 6 case statements inside one another (I love this project, shoot me now). That would require 6 different replacement tables would it not?

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-06 : 13:07:34

Ack, son of a.. shoot me now. Turns out Resource Interval Property is between 7million and 8 million rows as well as there are over 800 different combinations in that case clause.

Throwing out the update possiblity and just sticking to a view that does all those joins like you suggested... thanks Rob

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-07 : 03:27:55

<s>Create a new table containing the values following the "then..."
and join to that table on the values following the "when..." </s>

[Edit]--After I posted this message I read Rob's reply. That says it all.

Read up on "third normal form" (just for academic purposes) :)

Edited by - dataphile on 06/07/2002 03:32:27

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources