Crete a column based on a value in a created colum

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

Crete a column based on a value in a created colum

Author

Topic

Alan1018
Starting Member

13 Posts

Posted - 2014-06-20 : 09:34:08

I am trying to add a column to query based on the value of another column in the query.

I first tried creating a calculated field in SSRS 2008 with this statement:
=IIF(Fields!ChargeableFlag.Value=1,Fields!Negamt.Value,0)
The report runs but I get a "#ERROR" when I place the field on the report.

I next tried creating a new column with the SQL statement:

SELECT Project.ProjectCode AS PC, Project.StatusCode AS SC, Time.StandardHours AS Hours,
Time.StandardChargeAmt AS StdAmt, Time.TaskUID as UID,
Time.StandardChargeRate as Rate, ChargeableFlag, 'Bill' =

Case
When TaskRule.ChargeableFlag = 0 Then 'Non-Bill'
When TaskRule.ChargeableFlag = 1 Then 'Billable'
End ,

--This statement replaces null values in NegotiatedChargeAmt with values from StandardChargeAmt
--and creates a new column Negamt
Negamt = isnull(nullif(Time.NegotiatedChargeAmt, 0), Time.StandardChargeAmt),

'BLAmt' =
Case
When TaskRule.ChargeableFlag = 1 Then Negamt
End

FROM Project INNER JOIN
TaskRule ON Project.ProjectCode = TaskRule.ProjectCode AND Project.RevisionNum = TaskRule.RevisionNum INNER JOIN
Time ON TaskRule.ProjectCode = Time.ProjectCode AND TaskRule.TaskUID = Time.TaskUID

This query, less the case statement for BLAmt creates the dataset for the SSRS. Adding the Case statement for the BLAmt produces the error:
"Invalid column name 'Negamt'."

Thanks,
Alan

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-20 : 11:20:14

You can't reference a derived column like that. You can either reuse the logic for Negamt in BLAmt or use a derived table:

-- Reuse logic
SELECT Project.ProjectCode AS PC, Project.StatusCode AS SC, Time.StandardHours AS Hours, 
	Time.StandardChargeAmt AS StdAmt, Time.TaskUID as UID, 
	Time.StandardChargeRate as Rate, ChargeableFlag, 'Bill' =

	Case
	When TaskRule.ChargeableFlag = 0 Then 'Non-Bill'
	When TaskRule.ChargeableFlag = 1 Then 'Billable'
	End , 

	--This statement replaces null values in NegotiatedChargeAmt with values from StandardChargeAmt
	--and creates a new column Negamt 
	Negamt = isnull(nullif(Time.NegotiatedChargeAmt, 0), Time.StandardChargeAmt),

	'BLAmt' =
	Case 
	When TaskRule.ChargeableFlag = 1 Then isnull(nullif(Time.NegotiatedChargeAmt, 0), Time.StandardChargeAmt)
	End
	...

-- Or Derived Table
SELECT
	* -- REplace with column names
	,Case 
		When ChargeableFlag = 1 Then Negamt
		End
FROM
	(
		SELECT Project.ProjectCode AS PC, Project.StatusCode AS SC, Time.StandardHours AS Hours, 
			Time.StandardChargeAmt AS StdAmt, Time.TaskUID as UID, 
			Time.StandardChargeRate as Rate, ChargeableFlag, 'Bill' =

			Case
			When TaskRule.ChargeableFlag = 0 Then 'Non-Bill'
			When TaskRule.ChargeableFlag = 1 Then 'Billable'
			End , 

			--This statement replaces null values in NegotiatedChargeAmt with values from StandardChargeAmt
			--and creates a new column Negamt 
			Negamt = isnull(nullif(Time.NegotiatedChargeAmt, 0), Time.StandardChargeAmt),
			ChargeableFlag
			...
	) AS T

Alan1018
Starting Member

13 Posts

Posted - 2014-06-20 : 11:33:27

Thank you, I was going nuts, 4 SQl books and Google didn't yield an answer but your code is great.

Thanks,
Alan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources