Results 1 to 5 of 5

Thread: Nested CASE

  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: 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)?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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):
    Code:
    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

  3. #3
    Join Date
    Jul 2003
    Posts
    123
    This CASE is tested and works to my satisfaction... Looks a lot cleaner too than my first atempt.

    Code:
    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!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2003
    Posts
    123
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •