Replace Null value with "?" - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Replace Null value with "?"

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-12-12 : 13:00:04

I nee to replace Null values with '?' and wonder how to do that:

SELECT	dbo.Batch.ReportDate as job_date, 
	  	dbo.Item.CompanyItemId as cost_code,
		pay_type = '40',
		pay_text = 'Regular Time',
		coalesce(substring(dbo.Equipment.CompanyEquipmentId, 1, 10), '?') as equipment_id, 
                coalesce(substring(dbo.Equipment.Name, 1, 20), '?') as equipment_name,
                coalesce(substring(dbo.Employee.Name, 1, 12), '?') as employee_name,	    	
                substring(dbo.JobCraft.Name, 1, 15) as job_craft,
	    	--dbo.EmployeeLaborEvent.Hours labor_hours,
	    	dbo.EquipmentLaborEvent.Hours as equipment_hours,
                           --e.Start,
		--e.[Stop],
		--e.Lunch,
		--e.Rest
                             substring(dbo.EventStatusType.Name, 1, 1) as event_name,  	
                             dbo.EventStatusType.Name as event_name  	
FROM		Job
inner join	[Event] on dbo.Event.Jobguid = dbo.Job.Jobguid
 inner join            dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
inner join	Item on dbo.Item.Itemguid = dbo.Event.Itemguid
inner join	dbo.EmployeeLaborEvent on dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid 
inner join	dbo.Employee on dbo.Employee.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid
inner join	dbo.JobCraft on dbo.JobCraft.JobCraftGuid = dbo.EmployeeLaborEvent.JobCraftGuid
inner join	dbo.Batch on dbo.Batch.Batchguid = dbo.Event.Batchguid
left join   	dbo.Event e1 on e1.RelatedEventGuid = dbo.Event.EventGuid
left join	              dbo.EquipmentLaborEvent on dbo.EquipmentLaborEvent.EventGuid = e1.Eventguid
left join	              dbo.Equipment on dbo.Equipment.EquipmentGuid = dbo.EquipmentLaborEvent.EquipmentGuid 
WHERE               dbo.Job.CompanyJobId = @JobNumber 
                            and dbo.Item.CompanyItemId = @ItemNumber 
                            and ( @DateFrom IS  Null OR dbo.Batch.Reportdate >=  @DateFrom)
                            and (@DateTo IS  Null OR dbo.Batch.ReportDate <= @DateTo)
                            and dbo.EmployeeLaborEvent.Hours <> 0
                            and dbo.Equipment.CompanyEquipmentId <> Null

SELECT           * from #LaborListTable
ORDER BY	job_date, cost_code, employee_name