Computed Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Computed Column

Author

Topic

deadtrees
Starting Member

26 Posts

Posted - 2012-02-17 : 11:44:01

I think I'm running into an issue of a computed column and I need help. I want to add the three columns [MANUAL COMPARISONS], [COMPUTER COMPARISONS] and [EVAL COMPS] but I think because EVAL COMPS is a case statement with values that it's being treated as a computed field and then I'm trying to compute the computed column TOTAL COMPS off another computed column. I've read threads which advise wrapping the second computed column in a select statement which references the select statement as a Table. I tried that but it didn't work. Can someone help here?

Thanks.

SELECT CaseDetails.[Case Number] AS [CASE NUMBER]
	,Employees.[Last Name] AS [LAST NAME]
	,Examination.ExamStart AS [EXAM START DATE]
	,Examination.ExamEnd AS [EXAM END DATE]
	,(Case Examination.[Eval Only]
				WHEN 'Yes'
					THEN 1
				Else 0
	  END) AS [EVAL ONLY]
	,ISNULL(Examination.ComputerComparisons,0) AS [COMPUTER COMPARISONS]
	,ISNULL(Examination.ManualComparisons,0) AS [MANUAL COMPARISONS]
	,(CASE Examination.[Eval Only]
	            WHEN  1
					THEN 10
				ELSE 0
	  END) AS [EVAL COMPS]
	  ,[COMPUTER COMPARISONS]+ [MANUAL COMPARISONS] + [EVAL COMPS] AS [TOTAL COMPS]	
FROM LIMS_DATA_Production.dbo.Examination Examination
INNER 
	JOIN LIMS_DATA_Production.dbo.CaseDetails CaseDetails
	   ON CaseDetails.ID = Examination.[Case ID]
INNER 
    JOIN LIMS_DATA_Production.dbo.Employees Employees 
       ON Employees.ID = Examination.Examiner

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-17 : 11:52:17

[code]
SELECT *
,ISNULL(Examination.ComputerComparisons,0) AS [COMPUTER COMPARISONS]
,ISNULL(Examination.ManualComparisons,0) AS [MANUAL COMPARISONS]
,(CASE Examination.[Eval Only]
WHEN 1
THEN 10
ELSE 0
END) AS [EVAL COMPS]
,[COMPUTER COMPARISONS]+ [MANUAL COMPARISONS] + [EVAL COMPS] AS [TOTAL COMPS]
FROM (
SELECT CaseDetails.[Case Number] AS [CASE NUMBER]
,Employees.[Last Name] AS [LAST NAME]
,Examination.ExamStart AS [EXAM START DATE]
,Examination.ExamEnd AS [EXAM END DATE]
,(Case Examination.[Eval Only]
WHEN 'Yes'
THEN 1
Else 0
END) AS [EVAL ONLY]
FROM LIMS_DATA_Production.dbo.Examination Examination
INNER
JOIN LIMS_DATA_Production.dbo.CaseDetails CaseDetails
ON CaseDetails.ID = Examination.[Case ID]
INNER
JOIN LIMS_DATA_Production.dbo.Employees Employees
ON Employees.ID = Examination.Examiner
) AS XXX
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 13:08:19

quote:

Originally posted by X002548


SELECT *
  	,ISNULL(Examination.ComputerComparisons,0) AS [COMPUTER COMPARISONS]
	,ISNULL(Examination.ManualComparisons,0) AS [MANUAL COMPARISONS]
	,(CASE Examination.[Eval Only]
	            WHEN  1
					THEN 10
				ELSE 0
	  END) AS [EVAL COMPS]
	  ,[COMPUTER COMPARISONS]+ [MANUAL COMPARISONS] + [EVAL COMPS] AS [TOTAL COMPS]	
FROM (
SELECT CaseDetails.[Case Number] AS [CASE NUMBER]
	,Employees.[Last Name] AS [LAST NAME]
	,Examination.ExamStart AS [EXAM START DATE]
	,Examination.ExamEnd AS [EXAM END DATE]
	,(Case Examination.[Eval Only]
				WHEN 'Yes'
					THEN 1
				Else 0
	  END) AS [EVAL ONLY]
FROM LIMS_DATA_Production.dbo.Examination Examination
INNER 
	JOIN LIMS_DATA_Production.dbo.CaseDetails CaseDetails
	   ON CaseDetails.ID = Examination.[Case ID]
INNER 
    JOIN LIMS_DATA_Production.dbo.Employees Employees 
       ON Employees.ID = Examination.Examiner
       ) AS XXX

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Still wont work as [COMPUTER COMPARISONS],MANUAL COMPARISONS are same level

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 13:10:07

it should be


SELECT *,
	  ,[COMPUTER COMPARISONS]+ [MANUAL COMPARISONS] + [EVAL COMPS] AS [TOTAL COMPS]	
FROM
(
SELECT CaseDetails.[Case Number] AS [CASE NUMBER]
	,Employees.[Last Name] AS [LAST NAME]
	,Examination.ExamStart AS [EXAM START DATE]
	,Examination.ExamEnd AS [EXAM END DATE]
	,(Case Examination.[Eval Only]
				WHEN 'Yes'
					THEN 1
				Else 0
	  END) AS [EVAL ONLY]
	,ISNULL(Examination.ComputerComparisons,0) AS [COMPUTER COMPARISONS]
	,ISNULL(Examination.ManualComparisons,0) AS [MANUAL COMPARISONS]
	,(CASE Examination.[Eval Only]
	            WHEN  1
					THEN 10
				ELSE 0
	  END) AS [EVAL COMPS]
FROM LIMS_DATA_Production.dbo.Examination Examination
INNER 
	JOIN LIMS_DATA_Production.dbo.CaseDetails CaseDetails
	   ON CaseDetails.ID = Examination.[Case ID]
INNER 
    JOIN LIMS_DATA_Production.dbo.Employees Employees 
       ON Employees.ID = Examination.Examiner
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-17 : 13:12:41

..for the love of...

I'm suppose to READ these things now?

EDIT: Freak it...my long weekend must have started already, and I wasn't told



SELECT *
	  ,[COMPUTER COMPARISONS]+ [MANUAL COMPARISONS] + [EVAL COMPS] AS [TOTAL COMPS]	
FROM (
		SELECT *
			,(CASE Examination.[Eval Only]
						WHEN  1
						THEN 10
						ELSE 0
			  END)										AS [EVAL COMPS]
  			,ISNULL(Examination.ComputerComparisons,0)	AS [COMPUTER COMPARISONS]
			,ISNULL(Examination.ManualComparisons,0)	AS [MANUAL COMPARISONS]
		FROM (
					SELECT CaseDetails.[Case Number] AS [CASE NUMBER]
						,Employees.[Last Name]		 AS [LAST NAME]
						,Examination.ExamStart		 AS [EXAM START DATE]
						,Examination.ExamEnd		 AS [EXAM END DATE]
						,(Case Examination.[Eval Only]
									WHEN 'Yes'
										THEN 1
									Else 0
						  END)						 AS [EVAL ONLY]
					FROM LIMS_DATA_Production.dbo.Examination Examination
					INNER 
						JOIN LIMS_DATA_Production.dbo.CaseDetails CaseDetails
						   ON CaseDetails.ID = Examination.[Case ID]
					INNER 
						JOIN LIMS_DATA_Production.dbo.Employees Employees 
						   ON Employees.ID = Examination.Examiner
			   ) AS XXX
   ) AS YYY

deadtrees
Starting Member

26 Posts

Posted - 2012-02-17 : 15:00:44

Thank you, the last example was what I needed!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 15:01:42

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources