CF query fails in CF but works in sqlserver
I have the following cfquery wich fails in CF and the sql in the error code is exactly the same as listed here and will work fine when placed back in sqlserver..
code will work when this section is removed:
and t.EmployeeNumber in
(select std_user_id
from HED.dbo.EDD_CPR_EMP
where TERMINATION_DATE is null)
why it is not working with above code and how to make it work??
my code:
<html>
<head>
<title>Retrieving data from Data Base</title>
</head>
<body>
<!--- Set Datasource(s) --->
<cfset intellex_datasource = "Intelex">
<CFQUERY NAME="get_data" datasource="#intellex_datasource#">
select distinct
c.CourseTypeName Type,
substring(s.WorkGroupName,1,2) Dept,
t.EmployeeNumber Win2k,
t.FirstNam FirstName,
t.LastName LastName,
a.CourseName Course,
b.NextDate NextDate
from trnTraining a,trnEmployeeTraining b, trnCourseType c, trnEmployeeWorkgroup r, trnWorkgroups s, sysEmployee t
where b.PriorityId = 'Y'
and a.CourseId = b.CourseId
and a.CourseTypeId = c.CourseTypeId
and c.CourseTypeName = 'SP'
and r.employeeID = b.employeeID
and r.employeeID = t.employeeID
and t.EmployeeNumber in
(select std_user_id
from HED.dbo.EDD_CPR_EMP
where TERMINATION_DATE is null)
and t.deleted is NULL
and r.workgroupID = s.workgroupid
and len(s.WorkGroupName) <= 2
and b.NextDate >= getdate()
order by t.LastName, t.EmployeeNumber , a.CourseName
;
</cfquery>
<h4>Employee Data Based on Criteria from Form</h4>
<cfoutput query="get_data">
#Get_data.type#, 'Check it', #Get_data.win2k#<br>
</cfoutput>
</body>
</html>
AdChoices