• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

Referencing a alias within a case statement

I would like to compare data from one case statement that is an alias with another . have provided sample of what i want to do.

Want to reference ParentChild in ChildName if possilbe
SELECT Distinct StaffName ,StaffNumber,SeniorNumber,Level,SeniorName,Parent,
  CASE  

            WHEN SeniorName = @Root OR StaffName = @Root THEN StaffName
			ELSE @Root
      END AS [ParentChild],
	
 CASE 
		WHEN SeniorName =  ParentChild THEN StaffName
		END [ChildName]
	  FROM Children 
Order By StaffName
SET NOCOUNT ON

Open in new window

0
wolivier69
Asked:
wolivier69
  • 7
  • 5
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot refer to the alias, EXCEPT in the ORDER  BY clause.

the only "workarounds" are:
* repeat the expression
* put the query into a subquery (inline view), so you can use the alias there.
0
 
Pratima PharandeCommented:
try this
Select X.StaffName ,X.StaffNumber,X.SeniorNumber,X.Level,X.SeniorName,X.Parent,X.ParentChild 
,
 CASE 
		WHEN X.SeniorName =  X.ParentChild THEN X.StaffName
		END [ChildName]
From
(
SELECT Distinct StaffName ,StaffNumber,SeniorNumber,Level,SeniorName,Parent,
  CASE  

            WHEN SeniorName = @Root OR StaffName = @Root THEN StaffName
			ELSE @Root
      END AS [ParentChild],
	
 CASE 
		WHEN SeniorName =  ParentChild THEN StaffName
		END [ChildName]
	  FROM Children 
) X

Order By X.StaffName

Open in new window

0
 
wolivier69Author Commented:
Tried that pratima_mcs but get the same error saying that the column ParentChild is invalid
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Pratima PharandeCommented:
try
Select X.StaffName ,X.StaffNumber,X.SeniorNumber,X.Level,X.SeniorName,X.Parent,X.ParentChild 
,
 CASE 
		WHEN X.SeniorName =  X.ParentChild THEN X.StaffName
		END [ChildName]
From
(
SELECT Distinct StaffName ,StaffNumber,SeniorNumber,Level,SeniorName,Parent,
  CASE  

            WHEN SeniorName = @Root OR StaffName = @Root THEN StaffName
			ELSE @Root
      END AS [ParentChild]
	

) X

Order By X.StaffName

Open in new window

0
 
wolivier69Author Commented:
this is the full query that i have to maybe give a clearer picture
WITH Children(StaffName,StaffNumber,SeniorNumber, Level,SeniorName,Parent)
			AS(
			 SELECT Distinct StaffName,StaffNumber,SeniorNumber,1 as Level,SeniorName,@Root
                        From StaffADITSLookup  WITH (NOLOCK)
						WHERE StaffName = @Root
                        UNION all
                        SELECT m.StaffName,m.StaffNumber,m.SeniorNumber, l.Level + 1 as Level, 
						CASE 
							WHEN m.StaffName = @Root THEN l.StaffName
							ELSE m.SeniorName
						END[SeniorName],@Root
						FROM StaffADITSLookup m WITH (NOLOCK)
						INNER JOIN children l
                        ON l.StaffName = m.SeniorName 
			)

SELECT Distinct StaffName ,StaffNumber,SeniorNumber,Level,SeniorName,Parent,
  CASE  

            WHEN SeniorName = @Root OR StaffName = @Root THEN StaffName
			ELSE @Root
      END AS [ParentChild],
	
 CASE 
		WHEN SeniorName =  ParentChild THEN StaffName
		END [ChildName]
	  FROM Children 
Order By StaffName
SET NOCOUNT ON

Open in new window

0
 
Pratima PharandeCommented:
try the 2nd one that I suggested
0
 
wolivier69Author Commented:
I tried it still not working says invalid columns for almost everything. i dont see where you are calling the table Children // From Clause doesnt seem to use the table Children
0
 
Pratima PharandeCommented:
ohh sorry try this
Select X.StaffName ,X.StaffNumber,X.SeniorNumber,X.Level,X.SeniorName,X.Parent,X.ParentChild 
,
 CASE 
		WHEN X.SeniorName =  X.ParentChild THEN X.StaffName
		END [ChildName]
From
(
SELECT Distinct StaffName ,StaffNumber,SeniorNumber,Level,SeniorName,Parent,
  CASE  

            WHEN SeniorName = @Root OR StaffName = @Root THEN StaffName
			ELSE @Root
      END AS [ParentChild]
	
	  FROM Children 
) X

Order By X.StaffName

Open in new window

0
 
wolivier69Author Commented:
The code runs but just gives me null values for the ChildName column
0
 
Pratima PharandeCommented:
Provide some avlue in else instade of ?? i have specified
CASE
            WHEN X.SeniorName =  X.ParentChild THEN X.StaffName
                Else ??
            END [ChildName]


might be there is no match for X.SeniorName =  X.ParentChild  
Select X.StaffName ,X.StaffNumber,X.SeniorNumber,X.Level,X.SeniorName,X.Parent,X.ParentChild 
,
 CASE 
		WHEN X.SeniorName =  X.ParentChild THEN X.StaffName
                Else ??
		END [ChildName]
From
(
SELECT Distinct StaffName ,StaffNumber,SeniorNumber,Level,SeniorName,Parent,
  CASE  

            WHEN SeniorName = @Root OR StaffName = @Root THEN StaffName
			ELSE @Root
      END AS [ParentChild],
	
	  FROM Children 
) X

Order By X.StaffName

Open in new window

0
 
Aaron ShiloCommented:
hi please reffer to the answer by angelIII:
 
you cannot refer to the alias, EXCEPT in the ORDER  BY clause.

the only "workarounds" are:
* repeat the expression
* put the query into a subquery (inline view), so you can use the alias there
0
 
wolivier69Author Commented:
I will give you full points but want to know is it possible to loop through the table and where ever seniorName is = parentChid that it inserts that staff name.

this is basicaly a hierachy structure and i have the first level of data but want to know who the second level people report to.

Have first node = Parent
//@Root

Second Node sort of = ParentChild
//WHEN SeniorName = @Root OR StaffName = @Root THEN StaffName

Third node should be wherever parentChild occurs as a senior name
0
 
wolivier69Author Commented:
@pratima_mcs:
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I will need to see some data and expected results to be 100% clear of what you are trying to achieve.
0
 
wolivier69Author Commented:
was not 100% what i was looking for but was close found another way around to fix the problem
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now