none
Trouble with SWITCH Statement - weird behavoir RRS feed

  • Question

  • Hi Folks -

    I"m trying to combine logic into 1 query statement but having trouble with the 'Data Type Mismatch' error.

    I have it split up like such and it's working fine (other columns removed to make these less cumbersome):

    INSERT INTO [Global_Monthly_Headcount] (
    	[Cost Center - Long Name]
    	)
    SELECT
    	SWITCH(
    		[Host Country Cost Center - ID] IS NULL, LongName([Cost Center - ID])
    	)
    FROM [LT-Global_Monthly_Headcount]
    LEFT JOIN [LT-SAP_CC_CMSO_Mapping] ON
    	SWITCH(
    		[Host Country Cost Center - ID] IS NULL, LongName([LT-Global_Monthly_Headcount].[Cost Center - ID])) = [LT-SAP_CC_CMSO_Mapping].[Long_Code]
    WHERE 
    	[Host Country Cost Center - ID] IS NULL AND [Cost Center - ID] IS NOT NULL;

    The second one:

    INSERT INTO [Global_Monthly_Headcount] (
    	[Cost Center - Long Name]
    	)
    SELECT
    	SWITCH(
    		[Host Country Cost Center - ID] IS NOT NULL, LongName([Host Country Cost Center - ID])
    	)
    FROM [LT-Global_Monthly_Headcount]
    LEFT JOIN [LT-SAP_CC_CMSO_Mapping] ON
    	SWITCH(
    		[Host Country Cost Center - ID] IS NOT NULL, LongName([LT-Global_Monthly_Headcount].[Hot County Cost Center - ID])) = [LT-SAP_CC_CMSO_Mapping].[Long_Code]
    WHERE 
    	[Host Country Cost Center - ID] IS NOT NULL;

    However, I want to combine them and I have tried everything, but no luck.  Here is what I think should work but still running into the same 'Datatype mismatch' error:

    INSERT INTO [Global_Monthly_Headcount] (
    	[Cost Center - Long Name]
    	)
    SELECT
    	SWITCH(
    		[Host Country Cost Center - ID] IS NULL, LongName([Cost Center - ID]),
    		[Host Country Cost Center - ID] IS NOT NULL, LongName([Host Country Cost Center - ID])
    	)
    FROM [LT-Global_Monthly_Headcount]
    LEFT JOIN [LT-SAP_CC_CMSO_Mapping] ON
    	SWITCH(
    		[Host Country Cost Center - ID] IS NULL, LongName([LT-Global_Monthly_Headcount].[Cost Center - ID]),
    		[Host Country Cost Center - ID] IS NOT NULL, LongName([LT-Global_Monthly_Headcount].[Host Country Cost Center - ID])
    	) = [LT-SAP_CC_CMSO_Mapping].[Long_Code]
    WHERE 
    	SWITCH(
    		[Host Country Cost Center - ID] IS NULL, LongName([LT-Global_Monthly_Headcount].[Cost Center - ID]),
    		[Host Country Cost Center - ID] IS NOT NULL, LongName([Host Country Cost Center - ID])		
    	) AND [Cost Center - ID] IS NOT NULL;
    	

    What I suspect is happening is a NULL is getting passed to the LEFT JOIN. What's odd is that my WHERE clause should solve for that but for some reason it seems to be passed BEFORE the WHERE criteria is executed.

    There are situations where [Host Country Cost Center - ID] IS NULL as well as [Cost Center - ID] which may cause it (but I am ignoring those instances with [Company - ID] IS NOT NULL). But when I try to put an AND in the switch within the LEFT JOIN, it gives me an error about missing )],.  Can I not use a SWITCH statement with an AND in a LEFT JOIN?

    This is what I would try to do:

    I added the AND clause to each SWTICH:

    INSERT INTO [Global_Monthly_Headcount] (
    	[Cost Center - Long Name]
    	)
    SELECT
    	SWITCH(
    		[Host Country Cost Center - ID] IS NULL AND [Cost Center - ID] IS NOT NULL, LongName([Cost Center - ID]),
    		[Host Country Cost Center - ID] IS NOT NULL, LongName([Host Country Cost Center - ID])
    	)
    FROM [LT-Global_Monthly_Headcount]
    LEFT JOIN [LT-SAP_CC_CMSO_Mapping] ON
    	SWITCH(
    		[Host Country Cost Center - ID] IS NULL AND [Cost Center - ID] IS NOT NULL, LongName([LT-Global_Monthly_Headcount].[Cost Center - ID]),
    		[Host Country Cost Center - ID] IS NOT NULL, LongName([LT-Global_Monthly_Headcount].[Host Country Cost Center - ID])
    	) = [LT-SAP_CC_CMSO_Mapping].[Long_Code]
    WHERE 
    	SWITCH(
    		[Host Country Cost Center - ID] IS NULL AND [Cost Center - ID] IS NOT NULL, LongName([LT-Global_Monthly_Headcount].[Cost Center - ID]),
    		[Host Country Cost Center - ID] IS NOT NULL, LongName([Host Country Cost Center - ID])		
    	) AND [Cost Center - ID] IS NOT NULL;

    Any ideas? Thank you!



    • Edited by cdtakacs1 Tuesday, June 30, 2020 3:43 PM
    Tuesday, June 30, 2020 2:57 PM

Answers

  • It appears your WHERE is still off if you want at least one of [Host Country Cost Center - ID] or [Cost Center - ID]  to be non-null, i.e.

    perhaps

    WHERE [Host Country Cost Center - ID] IS NULL OR [Cost Center - ID] IS NOT NULL;

    should be

    WHERE [Host Country Cost Center - ID] IS NOT NULL OR [Cost Center - ID] IS NOT NULL;

    ?

    -Bruce

    • Marked as answer by cdtakacs1 Tuesday, June 30, 2020 11:11 PM
    Tuesday, June 30, 2020 8:19 PM

All replies

  • Hmm, this seems more complicated than perhaps it needs to be...first I would explore using Nz() instead of switch().  Nz would allow you to substitute the value of [Cost Center - ID] for [Host Country Cost Center- ID] when [Host Country Cost Center- ID] is null, e.g. nz([Host Country Cost Center - ID],[Cost Center - ID]) will return the value of [Cost Center -ID] only when [Host Country Cost Center - ID] is null, otherwise it returns [Host Country Cost Center - ID].

    Keep in mind that SWITCH is going to evaluate all of the test expressions in your list even if the very first one is true...this can result in inefficiencies and unexpected side effects.

    Also your combined SQL does not adequately take into account situations where both of these fields are null.  Your WHERE clause there is treating SWITCH() as though it returns a true/false value rather than a "LongName()" value, e.g. "WHERE SWITCH(...) AND [Cost Center - ID] IS NOT NULL" . This is likely the source of your data type mismatch error.  Perhaps you mean to say " WHERE [Host Country Cost Center- ID] IS NOT NULL OR [Cost Center - ID] IS NOT NULL" instead if you only want to return rows where one or both of these values are non-null?

    -Bruce

    Tuesday, June 30, 2020 5:57 PM
  • Hmm, this seems more complicated than perhaps it needs to be...first I would explore using Nz() instead of switch().  Nz would allow you to substitute the value of [Cost Center - ID] for [Host Country Cost Center- ID] when [Host Country Cost Center- ID] is null, e.g. nz([Host Country Cost Center - ID],[Cost Center - ID]) will return the value of [Cost Center -ID] only when [Host Country Cost Center - ID] is null, otherwise it returns [Host Country Cost Center - ID].

    Keep in mind that SWITCH is going to evaluate all of the test expressions in your list even if the very first one is true...this can result in inefficiencies and unexpected side effects.

    Also your combined SQL does not adequately take into account situations where both of these fields are null.  Your WHERE clause there is treating SWITCH() as though it returns a true/false value rather than a "LongName()" value, e.g. "WHERE SWITCH(...) AND [Cost Center - ID] IS NOT NULL" . This is likely the source of your data type mismatch error.  Perhaps you mean to say " WHERE [Host Country Cost Center- ID] IS NOT NULL OR [Cost Center - ID] IS NOT NULL" instead if you only want to return rows where one or both of these values are non-null?

    -Bruce

    Hi Bruce -

    Thank you! I do think Nz would work now that you mention it.  However, I'm still getting hte mimatch error using this logic:

    INSERT INTO [Global_Monthly_Headcount] (
    	[Cost Center - Long Name]
    	)
    SELECT
    	LongName(Nz([Host Country Cost Center - ID], [Cost Center - ID]))
    FROM [LT-Global_Monthly_Headcount]
    LEFT JOIN [LT-SAP_CC_CMSO_Mapping] ON
    	LongName(Nz([LT-Global_Monthly_Headcount].[Host Country Cost Center - ID], [LT-Global_Monthly_Headcount].[Cost Center - ID])) = [LT-SAP_CC_CMSO_Mapping].[Long_Code]
    WHERE 
    	[Host Country Cost Center - ID] IS NULL OR [Cost Center - ID] IS NOT NULL;
    	

    Let me know your thoughts. Thanks!

    Tuesday, June 30, 2020 6:24 PM
  • It appears your WHERE is still off if you want at least one of [Host Country Cost Center - ID] or [Cost Center - ID]  to be non-null, i.e.

    perhaps

    WHERE [Host Country Cost Center - ID] IS NULL OR [Cost Center - ID] IS NOT NULL;

    should be

    WHERE [Host Country Cost Center - ID] IS NOT NULL OR [Cost Center - ID] IS NOT NULL;

    ?

    -Bruce

    • Marked as answer by cdtakacs1 Tuesday, June 30, 2020 11:11 PM
    Tuesday, June 30, 2020 8:19 PM
  • It appears your WHERE is still off if you want at least one of [Host Country Cost Center - ID] or [Cost Center - ID]  to be non-null, i.e.

    perhaps

    WHERE [Host Country Cost Center - ID] IS NULL OR [Cost Center - ID] IS NOT NULL;

    should be

    WHERE [Host Country Cost Center - ID] IS NOT NULL OR [Cost Center - ID] IS NOT NULL;

    ?

    -Bruce

    Ah yes, that was my mistake. Thought I did that. Thank you, Bruce. That worked great!!!!
    Tuesday, June 30, 2020 11:11 PM