erikTsomik
asked on
Coldfusion query help URGENT
I am trying to build the dynamic cfquery but does not seems to be working correctly it works fine when there is a asingle course but when multiple it does not .
Please help
Please help
<cfquery name="getStepsCompleted" datasource="cfelsunrise" dbtype="ODBC">
select max(CompletionDate) as completiondate,CourseID
from employeetraining
where EmployeeID = #getPairs.GTLID#
<!---<cfif getPairs.CourseID eq 1600>
and CourseID in (1600)
</cfif>--->
<cfif getPairs.CourseID eq 1634>
or CourseID in (1647, 1648)
</cfif>
<cfif getPairs.CourseID eq 1635>
or CourseID in (1645,1646)
</cfif>
<cfif getPairs.CourseID eq 1636>
or CourseID in (1649,1650)
</cfif>
<cfif getPairs.CourseID eq 1632>
or CourseID in (1651,1652)
</cfif>
<cfif getPairs.CourseID eq 1637>
or CourseID in (1653,1654)
</cfif>
<cfif getPairs.CourseID eq 1633>
or CourseID in (1655,1656)
</cfif>
group by CourseID
</cfquery>
ASKER
i did not miss anyhting
If you look at the condition that where it gets added
If you look at the condition that where it gets added
Am I missing something? In the code you included in your question, the only AND clause is commented out. Also, if you are going to add an AND clause and a bunch of OR clauses, then you probably need to use parenthesis around all the OR statements or the AND clause is escaped in the second OR.
What you have is something like:
condition1 AND condition2 OR condition3 OR condition4
Which is the same as saying:
(condition1 AND condition2)
OR
(condition3)
OR
(condition4)
This works if you are trying to get information for the employee or the course. If I understand what you want (getting info for employee and course), then it should be more like:
(condition1) AND (condition2 OR condition3 OR condition4)
... which produces the logic:
(condition1 AND condition2)
OR
(condition1 AND condition3)
OR
(condition1 AND condition4)
The query is pretty straight forward and I can't really think of anything else it could be.
What you have is something like:
condition1 AND condition2 OR condition3 OR condition4
Which is the same as saying:
(condition1 AND condition2)
OR
(condition3)
OR
(condition4)
This works if you are trying to get information for the employee or the course. If I understand what you want (getting info for employee and course), then it should be more like:
(condition1) AND (condition2 OR condition3 OR condition4)
... which produces the logic:
(condition1 AND condition2)
OR
(condition1 AND condition3)
OR
(condition1 AND condition4)
The query is pretty straight forward and I can't really think of anything else it could be.
Erik,
If the "getPairs.CourseID" is supposed to return multiple values, then the problem will be in the way you output that from the "getPairs" query. Is it an array or list? If so, then you can try something like:
<cfset courseList = #ArrayToList(getPairs.Cour seID, ",")#>
<cfset courseList = #REReplace(courseList, "1634", "1647,1648", "ALL")#>
<cfset courseList = #REReplace(courseList, "1635", "1645,1646", "ALL")#>
<cfset courseList = #REReplace(courseList, "1636", "1649,1650", "ALL")#>
<cfset courseList = #REReplace(courseList, "1632", "1651,1652", "ALL")#>
<cfset courseList = #REReplace(courseList, "1637", "1653,1654", "ALL")#>
<cfset courseList = #REReplace(courseList, "1633", "1655,1656", "ALL")#>
<cfquery name="getStepsCompleted" datasource="cfelsunrise" dbtype="ODBC">
SELECT max(CompletionDate) as completiondate,CourseID
FROM employeetraining
WHERE EmployeeID = #getPairs.GTLID# AND
(CourseID in (#courseList#)
GROUP BY CourseID
</cfquery>
Otherwise, you may have to address the way the first query returns its values.
If the "getPairs.CourseID" is supposed to return multiple values, then the problem will be in the way you output that from the "getPairs" query. Is it an array or list? If so, then you can try something like:
<cfset courseList = #ArrayToList(getPairs.Cour
<cfset courseList = #REReplace(courseList, "1634", "1647,1648", "ALL")#>
<cfset courseList = #REReplace(courseList, "1635", "1645,1646", "ALL")#>
<cfset courseList = #REReplace(courseList, "1636", "1649,1650", "ALL")#>
<cfset courseList = #REReplace(courseList, "1632", "1651,1652", "ALL")#>
<cfset courseList = #REReplace(courseList, "1637", "1653,1654", "ALL")#>
<cfset courseList = #REReplace(courseList, "1633", "1655,1656", "ALL")#>
<cfquery name="getStepsCompleted" datasource="cfelsunrise" dbtype="ODBC">
SELECT max(CompletionDate) as completiondate,CourseID
FROM employeetraining
WHERE EmployeeID = #getPairs.GTLID# AND
(CourseID in (#courseList#)
GROUP BY CourseID
</cfquery>
Otherwise, you may have to address the way the first query returns its values.
Sorry ... missing parenthesis after "(CourseID in (#courseList#)". Should be:
(CourseID in (#courseList#))
(CourseID in (#courseList#))
ASKER
it is an array
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are missing an AND statement between the "EmployeeID = #getPairs.GTLID#" and the "CourseID". Try attached code.
Open in new window