How to reference an aliased column 2

[jadams0173]

I need to reference an aliased column from my select list in another part of my select list.

I have it currently where I just repeat the way I got the aliased column but it's hard to ready and looks bad to me. If this "look" is ok then I'll go with it. Just want to try to be as friendly as I can be to me six months from now and others if they try to read the code as to what I've done.

I think I needed the aliased as a derived table but i'm not exactly sure how to do it.

SELECT 
		 DATEDIFF(DAY,A.START_DATE, A.SCHED_DATE)as LEAD_TIME,
		A.COST_FMLY,
		CASE 
			WHEN A.MIN_RATE =0 
				THEN 0 
			ELSE 
				CONVERT(DECIMAL(5,5),1/CONVERT(FLOAT,A.MIN_RATE))
		 END AS BDS_UNIT_HR ,	
		rtrim(A.[Description])"PROCESS_DEF",
		CASE A.PASS
			WHEN 1 THEN COALESCE(C.PRIMLINE,'N-A')
			WHEN 2 THEN COALESCE(B.PRIMLINE,'N-A')
			ELSE 'N-A' 
		END AS OTHER_SIDE,
		
		[BLUE]COALESCE(E.LINEID,	CASE 
							A.MOTYPE
								WHEN  'AI' THEN 7
								WHEN  'HLA' THEN 9
								WHEN  'PTH' THEN 8
								ELSE 10
						END) AS PRIM_LINE [/BLUE],
		
		COALESCE(D.SCHEDULED_LINE,[BLUE]COALESCE(E.LINEID,	CASE 
												A.MOTYPE
													WHEN  'AI' THEN 7
													WHEN  'HLA' THEN 9
													WHEN  'PTH' THEN 8
													ELSE 10
											END)) AS SCHEDULED_TO[/BLUE]

from vw_MyPrimaryLine A
	LEFT JOIN (SELECT MO_NUMBER, ITEM, TB,PASS, PRIMLINE
				 FROM vw_MyPrimaryLine
				WHERE PASS = 1 ) B
		ON A.MO_NUMBER = B.MO_NUMBER
	LEFT JOIN (SELECT MO_NUMBER, ITEM, TB, PASS,PRIMLINE
				FROM vw_MyPrimaryLine
				WHERE PASS = 2) C
		ON A.MO_NUMBER = B.MO_NUMBER 
			AND C.MO_NUMBER = B.MO_NUMBER
	LEFT JOIN SCHEDULE_DATA D
		ON D.MO = A.MO_NUMBER
	LEFT JOIN LINE_NAMES E
		ON A.PRIMLINE = E.LINEDESC

 

[bborissov]

It looks OK (w/o any testing), just one thing. In your second COALESCE, there is no need to use that function twice:

....
 COALESCE(D.SCHEDULED_LINE,
          E.LINEID,
          CASE A.MOTYPE
               WHEN  'AI'  THEN 7
               WHEN  'HLA' THEN 9
               WHEN  'PTH' THEN 8
               ELSE 10 END) AS SCHEDULED_TO
...

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

I'm not sure what do you mean here? To me the query looks OK, though you may put some comments inline

 

[gmmastros]

It looks like the big case statement is coming from the A alias, which is vw_MyPrimaryLine. Sounds to me like this is a view. What you could do is modify the view definition to include another column for that case statement. Then, you would be able to remove the case statement from this code, and replace it with the new column you create in the view.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

Hey all. Thanks for the replies.

bborissov,
Thanks for pointing that out. I was so caught up in making it work, I forgot that coalesce returns the first non null value.

Markros,
I was just trying to make the query more readable.

Hey george,
Yeah, it is a view and what you are saying does make perfect sense. That may be the best approach.

Thanks all.