Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    63

    Unanswered: Using Aliases in where clauses combined with 'Case'

    Hello all,

    Here is some SQL code which has a simple task,
    combine data from two tables and calculate one column. Then query the result

    Code:
    select 	basedata.ID,
    		basedata.DPSLeader, 
    		reason.CorrIssue,
    		case when 	reason.SorCitem = 'Standard'
    					and basedata.costcode in ('ERI','EFI','xx-RRFI') 
    					and reason.LABEL <> 'ManPort Service'
    			then 'Y'
    			else 'N'
    		end as ExpendedFlw
    from 	#local_basedata as basedata, 
    		#local_Reason as reason
    where 
    		basedata.DPSLeader = 'EBB' and
    		reason.CorrIssue = 'Y' and
    		ExpendedFlw = 'Y'
    the issue is with the last clause of the "where" statement, which causes an error due to "ExpendedFlw" being an Invalid Column Name.
    If I remove this clause, I see a result so the SQL works fine except for the last where clause.

    Is there a reason why this will not work?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by penfold1992 View Post
    Is there a reason why this will not work?
    Yes, the sequence in which the parts of a SQL SELECT statement execute. Due to that sequence, the WHERE clause is evaluated before the expressions in the SELECT list are evaluated. This means that the ExpendedFlw expression hasn't been evaluated yet when the WHERE clause is being evaluated.

    In some cases, there isn't any way to work around this because the order of evaluation is critical. In the simple case that you've posted, you can work around it by making the existing SELECT an inline expression aka a dynamic view. As a more general answer, you need to reproduce the CASE expression in the WHERE clause, like:

    Code:
    select 	basedata.ID,
    		basedata.DPSLeader, 
    		reason.CorrIssue,
    		case when 	reason.SorCitem = 'Standard'
    					and basedata.costcode in ('ERI','EFI','xx-RRFI') 
    					and reason.LABEL <> 'ManPort Service'
    			then 'Y'
    			else 'N'
    		end as ExpendedFlw
    from 	#local_basedata as basedata, 
    		#local_Reason as reason
    where 
    		basedata.DPSLeader = 'EBB' and
    		reason.CorrIssue = 'Y' and
    		'Y' = case when 	reason.SorCitem = 'Standard'
    					and basedata.costcode in ('ERI','EFI','xx-RRFI') 
    					and reason.LABEL <> 'ManPort Service'
    			then 'Y'
    			else 'N'
    		end
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2012
    Posts
    63
    perfect, thanks Pat! I expected this was the case but just wanted confirmation.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    why make it even that complex????
    Isn't this the same as writing:

    Code:
    select 	basedata.ID,
    		basedata.DPSLeader, 
    		reason.CorrIssue,
    from 	#local_basedata as basedata, 
    		#local_Reason as reason
    where 
    		basedata.DPSLeader = 'EBB' and
    		reason.CorrIssue = 'Y' and
    		reason.SorCitem = 'Standard'
    					and basedata.costcode in ('ERI','EFI','xx-RRFI') 
    					and reason.LABEL <> 'ManPort Service'
    Notice I also, removed this CASE from the select statement, because all rows would be a Y, as that is what you are limiting it to. This should, also, be cheaper costing SQL. Remember to ALWAYS use KISS principle(no not the rock band), stands for Keep It Simple, Stupid
    Dave

Posting Permissions

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