We help IT Professionals succeed at work.
Get Started

Conversion failed when converting the varchar value '; ' to data type int.

484 Views
Last Modified: 2015-03-17
I am having an issue with this query does anyone know why? therror is
Conversion failed when converting the varchar value '; ' to data type int.

 Select * FROM(  SELECT JobDownholeEquipment.StartTime as 'StartTime', 
 Cast(Job.JobID as char(25)) AS 'Location', 'Job' AS 'Event',  
 ('Tool Type: '+ Case JobType WHEN 0 Then 'surefire' else 'wildfire' end + '; '+'Run Number: '+ JobDownholeEquipment.RunID + '; ' + 'Tool Size: ' + JobDownholeEquipment.ToolSize + '; ' +   'Company: ' + Job.Company + '; ' + 'Rig: '+ Job.Rig + '; ' + 'Rig Number: ' +  Job.RigNumber + '; ' + 'Well Name: '+ Job.WellName ) AS 'Event Notes'  
 FROM (Job INNER JOIN JobDownholeEquipment ON Job.JobID = JobDownholeEquipment.JobID) 
 INNER JOIN SerializedAssets ON JobDownholeEquipment.ToolNumber = SerializedAssets.SerialNumber  
 WHERE JobDownholeEquipment.ToolNumber = 2 
 AND SerializedAssets.ToolType = 'TN' AND Job.Company <> 'Test Company'           
 AND JobDownholeEquipment.StartTime BETWEEN          '2012-01-01 00:00:00' AND '2015-12-31 23:59:59'   
 UNION  SELECT ToolAssemblies.UploadTime as 'StartTime', l.Location as 'Location', 'Tool Communication' AS 'Event', 
 ('Tool Size: ' + ToolSize + '; '+ 'Controller: '+ControllerSN +'; '+ 'Battery Gauge: '+BatteryGaugeSN +'; '+ 'Battery: ' + BatterySN + '; '+ 'Magnetics: '+ MagneticsSN) AS 'Event Notes'  
 FROM ToolAssemblies  JOIN   Locations as l ON l.LocationIndex = ToolAssemblies.DefaultLocationIndex  
 WHERE ToolAssemblies.ToolNumber= 2           AND ToolAssemblies.UploadTime BETWEEN          '2012-01-01 00:00:00' AND '2015-12-31 23:59:59'          
 UNION         SELECT iis.UploadDate as 'StartTime',l.Location as 'Location', '' as 'Event', 
 'Parts History reconcile' as 'Event Notes'          FROM Inventory_SerializedAssets iis         
 JOIN   Locations as l ON l.LocationIndex = iis.LocationID          
 WHERE iis.SerialNumber = 2  AND iis.ToolType = 'TN'        
 AND iis.UploadDate 
 BETWEEN            '2012-01-01 00:00:00' AND '2015-12-31 23:59:59'    )X  
 Order By StartTime DESC

Open in new window

Comment
Watch Question
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
This problem has been solved!
Unlock 2 Answers and 3 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE