Solved

Coldfusion query help URGENT

Posted on 2008-06-10
7
174 Views
Last Modified: 2013-12-24
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

<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>

Open in new window

0
Comment
Question by:erikTsomik
  • 5
  • 2
7 Comments
 
LVL 4

Expert Comment

by:msfletch
Comment Utility
Erik,

You are missing an AND statement between the "EmployeeID = #getPairs.GTLID#" and the "CourseID". Try attached code.
<cfset courseList = "">

<cfif getPairs.CourseID eq 1634>

	<cfif (courseList) IS NOT "">

		<cfset courseList = "#courseList#,1647, 1648">

	<cfelse>

		<cfset courseList = "1647, 1648">

	</cfif>

</cfif>

<cfif getPairs.CourseID eq 1635>

	<cfif (courseList) IS NOT "">

		<cfset courseList = "#courseList#,1645,1646">

	<cfelse>

		<cfset courseList = "1645,1646">

	</cfif>

</cfif>

<cfif getPairs.CourseID eq 1636>

	<cfif (courseList) IS NOT "">

		<cfset courseList = "#courseList#,1649,1650">

	<cfelse>

		<cfset courseList = "1649,1650">

	</cfif>

</cfif>

<cfif getPairs.CourseID eq 1632>

	<cfif (courseList) IS NOT "">

		<cfset courseList = "#courseList#,1651,1652">

	<cfelse>

		<cfset courseList = "1651,1652">

	</cfif>

</cfif>

<cfif getPairs.CourseID eq 1637>

	<cfif (courseList) IS NOT "">

		<cfset courseList = "#courseList#,1653,1654">

	<cfelse>

		<cfset courseList = "1653,1654">

	</cfif>

</cfif>

<cfif getPairs.CourseID eq 1633>

	<cfif (courseList) IS NOT "">

		<cfset courseList = "#courseList#,1655,1656">

	<cfelse>

		<cfset courseList = "1655,1656">

	</cfif>

</cfif>
 

<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>

Open in new window

0
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
i did not miss anyhting
If you look at the condition that where it gets added
0
 
LVL 4

Expert Comment

by:msfletch
Comment Utility
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Expert Comment

by:msfletch
Comment Utility
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.CourseID, ",")#>
<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.
0
 
LVL 4

Expert Comment

by:msfletch
Comment Utility
Sorry ... missing parenthesis after "(CourseID in (#courseList#)". Should be:

(CourseID in (#courseList#))
0
 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
it is an array
0
 
LVL 4

Accepted Solution

by:
msfletch earned 500 total points
Comment Utility
In that case, an EQ will not work in the WHERE clause. Easiest is to convert it to a list so you can then swap out the CourseIDs as you do currently in the WHERE clause, and then simply include the list in the IN statement.

Did the included code snippet not work?
<cfset courseList = #ArrayToList(getPairs.CourseID, ",")#>

<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>

Open in new window

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to setup ssl access to internal local machine 4 97
JKS to store upstart data 2 64
Problem to echo 6 51
Coldfusion RegEx 8 47
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now