Nested CASE

Hello,

I've got a SP that selects the best price from a table that has all info collected into it. Selecting the price is easy, I use COALESCE.

But I want to have a column next to it that contains which price that was choosen. I used CASE and nested it... worked fine until I reached the 10th level, there is a limit there.

"Case expressions may only be nested to level 10."

I'm sure som people will puke when they see this code and I'm open to suggestions on how to do it in another way. I can always do it in two queries, but it should be possible to do it in one.

I was looking at IF, THEN, ELSE, but I don't find any way to use it in a query, just to determine WHICH query will be run.

Here is my SP (how can I get it in a nice grey area like som people post?):

CREATE PROCEDURE dbo.ProcCOST_SET_TC AS

/* Empty TC table */
truncate table dbo.COST_TC

/* Collect info */
INSERT INTO dbo.COST_TC
SELECT REGION,PROJECT,CPN,
COALESCE (
Contract_usd,
SITEINPUT_sitecontract_usd,
SITEINPUT_lastPO_usd,
SITEINPUT_lastreceipt_usd,
SITEINPUT_other_usd,
SITEINPUT_wac_usd,
SYSTEM_Min_ContractPrice_usd,
SYSTEM_Min_OpenOrder_usd,
SYSTEM_Last_Receipt_usd,
SYSTEM_Min_WAC_usd,
[BP Q-1]
),
Case Contract_usd WHEN IsNull(Contract_USD,0) THEN 'Contract' ELSE
Case SITEINPUT_sitecontract_usd WHEN IsNull(SITEINPUT_sitecontract_usd,0) THEN 'SITEINPUT Site Contract' ELSE
Case SITEINPUT_lastPO_usd WHEN IsNull(SITEINPUT_lastPO_usd,0) THEN 'SITEINPUT Last PO' ELSE
Case SITEINPUT_lastreceipt_usd WHEN IsNull(SITEINPUT_lastreceipt_usd,0) THEN 'SITEINPUT Last Receipt' ELSE
Case SITEINPUT_other_usd WHEN IsNull(SITEINPUT_other_usd,0) THEN 'SITEINPUT Other' ELSE
Case SITEINPUT_wac_usd WHEN IsNull(SITEINPUT_wac_usd,0) THEN 'SITEINPUT WAC' ELSE
Case SYSTEM_Min_ContractPrice_usd WHEN IsNull(SYSTEM_Min_ContractPrice_usd,0) THEN 'Min Contract Price' ELSE
Case SYSTEM_Min_OpenOrder_usd WHEN IsNull(SYSTEM_Min_OpenOrder_usd,0) THEN 'Min Open Order' ELSE
Case SYSTEM_Last_Receipt_usd WHEN IsNull(SYSTEM_Last_Receipt_usd,0) THEN 'Last Receipt' ELSE
Case SYSTEM_Min_WAC_usd WHEN IsNull(SYSTEM_Min_WAC_usd,0) THEN 'Min WAC' ELSE
Case [BP Q-1] WHEN IsNull([BP Q-1],0) THEN 'BP Q-1' ELSE
'NO DATA' END END END END END END END END END END END
FROM COST_AllInfo
GO

---

Suggestions (don't hit me to hard)?Use the [ code] and [ /code] markers before and after your code so that vBulletin will treat it as code. I've put a space after the opening bracket so that they'll be visible in my posting, you must remove the spaces for them to take affect.

Turn them all into one CASE, something like (untested, of course):CREATE PROCEDURE dbo.ProcCOST_SET_TC AS

/* Empty TC table */
truncate table dbo.COST_TC

/* Collect info */
INSERT INTO dbo.COST_TC
SELECT REGION,PROJECT,CPN,
COALESCE (
Contract_usd,
SITEINPUT_sitecontract_usd,
SITEINPUT_lastPO_usd,
SITEINPUT_lastreceipt_usd,
SITEINPUT_other_usd,
SITEINPUT_wac_usd,
SYSTEM_Min_ContractPrice_usd,
SYSTEM_Min_OpenOrder_usd,
SYSTEM_Last_Receipt_usd,
SYSTEM_Min_WAC_usd,
[BP Q-1]
),
Case Contract_usd WHEN IsNull(Contract_USD,0) THEN 'Contract'
WHEN SITEINPUT_sitecontract_usd WHEN IsNull(SITEINPUT_sitecontract_usd,0) THEN 'SITEINPUT Site Contract'
WHEN SITEINPUT_lastPO_usd WHEN IsNull(SITEINPUT_lastPO_usd,0) THEN 'SITEINPUT Last PO'
WHEN SITEINPUT_lastreceipt_usd WHEN IsNull(SITEINPUT_lastreceipt_usd,0) THEN 'SITEINPUT Last Receipt'
WHEN SITEINPUT_other_usd WHEN IsNull(SITEINPUT_other_usd,0) THEN 'SITEINPUT Other'
WHEN SITEINPUT_wac_usd WHEN IsNull(SITEINPUT_wac_usd,0) THEN 'SITEINPUT WAC'
WHEN SYSTEM_Min_ContractPrice_usd WHEN IsNull(SYSTEM_Min_ContractPrice_usd,0) THEN 'Min Contract Price' ELSE
WHEN SYSTEM_Min_OpenOrder_usd WHEN IsNull(SYSTEM_Min_OpenOrder_usd,0) THEN 'Min Open Order'
WHEN SYSTEM_Last_Receipt_usd WHEN IsNull(SYSTEM_Last_Receipt_usd,0) THEN 'Last Receipt'
WHEN SYSTEM_Min_WAC_usd WHEN IsNull(SYSTEM_Min_WAC_usd,0) THEN 'Min WAC'
WHEN [BP Q-1] WHEN IsNull([BP Q-1],0) THEN 'BP Q-1'
ELSE 'NO DATA' END
FROM COST_AllInfo

RETURN
GO-PatP|||This CASE is tested and works to my satisfaction... Looks a lot cleaner too than my first atempt.

CASE
WHEN isnull(VPA_average_USD,0) > 0 THEN 'VPA'
WHEN isnull(SITEINPUT_sitecontract_usd,0) > 0 THEN 'SITEINPUT Site Contract'
WHEN isnull(SITEINPUT_lastPO_usd,0) > 0 THEN 'SITEINPUT Last PO'
WHEN isnull(SITEINPUT_lastreceipt_usd,0) > 0 THEN 'SITEINPUT Last Receipt'
WHEN isnull(SITEINPUT_other_usd,0) > 0 THEN 'SITEINPUT Other'
WHEN isnull(SITEINPUT_wac_usd,0) > 0 THEN 'SITEINPUT WAC'
WHEN isnull(SYSTEM_Min_ContractPrice_usd,0) > 0 THEN 'Min Contract Price'
WHEN isnull(SYSTEM_Min_OpenOrder_usd,0) > 0 THEN 'Min Open Order'
WHEN isnull(SYSTEM_Last_Receipt_usd,0) > 0 THEN 'Last Receipt'
WHEN isnull(SYSTEM_Min_WAC_usd,0) > 0 THEN 'Min WAC'
ELSE 'NO DATA'
END

Thanks for pointing me in the right direction!|||How do you figure your COALESCE statement is going to return the best price? I don't know what "best price" means (lowest? highest? best for whom?), but I'm sure COALESCE doesn't know either. It just returns the first non-null value from your parameter list. So Contract_usd will be returned because it is the first value, not the "best" value.|||The "Best Price" is order by priority, not by value. So the order of the columns in the COALESCE determines the priority order.

The first priority is "VPA_average_USD", if there is one, use it no matter what the value is. (I see I used the actual column name instead of "Contract_usd" I used in my first post)

The last priority is the price used last quarter.