[Resolved] Case - syntax error

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

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Case - syntax error

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-31 : 10:15:48

Getting syntax error on my "CASE" line: Incorrect syntax near '='.

DECLARE @pay_type char(10)
DECLARE @pay_text char(10)

select		dbo.Batch.ReportDate as date, 
	  	dbo.Item.CompanyItemId as cost_code,
		substring(dbo.Equipment.Name, 1, 20) as equipment_name,
	    	substring(dbo.Employee.Name, 1, 20) as employee_name,
	    	substring(dbo.JobCraft.Name, 1, 20) as job_craft,
	    	dbo.EmployeeLaborEvent.Hours labor_hours,
	    	substring(dbo.Equipment.CompanyEquipmentId, 1, 20) as equipment_id, 
	   	dbo.EquipmentLaborEvent.Hours as equipment_hours,
		CASE dbo.EmployeeLaborEvent.EmployeeLaborAttributeGuid when Null then @pay_type = '40' and @pay_text = 'Regular Hours' END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 10:18:39

you cant assign multiple column values using single case. use like this

DECLARE @pay_type char(10)
DECLARE @pay_text char(10)

select		dbo.Batch.ReportDate as date, 
	  	dbo.Item.CompanyItemId as cost_code,
		substring(dbo.Equipment.Name, 1, 20) as equipment_name,
	    	substring(dbo.Employee.Name, 1, 20) as employee_name,
	    	substring(dbo.JobCraft.Name, 1, 20) as job_craft,
	    	dbo.EmployeeLaborEvent.Hours labor_hours,
	    	substring(dbo.Equipment.CompanyEquipmentId, 1, 20) as equipment_id, 
	   	dbo.EquipmentLaborEvent.Hours as equipment_hours,
		dbo.EmployeeLaborEvent.EmployeeLaborAttributeGuid, 
                @pay_type =CASE dbo.EmployeeLaborEvent.EmployeeLaborAttributeGuid when Null then  '40' END,
 @pay_text = CASE dbo.EmployeeLaborEvent.EmployeeLaborAttributeGuid when Null then  'Regular Hours' END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 10:18:49

select @pay_type = CASE WHEN dbo.EmployeeLaborEvent.EmployeeLaborAttributeGuid ~~when~~ IS Null then '40' ELSE 'Regular Hours' END

KH
[spoiler]Time is always against us[/spoiler]

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-31 : 10:35:38

Get error on:

@pay_type =CASE dbo.EmployeeLaborEvent.EmployeeLaborAttributeGuid when Null then  '40' END,
 @pay_text = CASE dbo.EmployeeLaborEvent.EmployeeLaborAttributeGuid when Null then  'Regular Hours' END

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Trying to solve post no: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107755)

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-31 : 12:14:08

Resolved using two queries

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources