ColdFusion pass/fail problem

erikTsomik
erikTsomik used Ask the Experts™
on
I have 3 courses. and I am getting the completion for each of theses courses. How Can i say if all 3 courses are done then put pass. in it .
So far I have done this
<cfif PreReq.recordcount gt 0>
                <cfloop query="PreReq">
                        <cfif ISPREREQDONE eq 'PreReqDone'>
                                <cfset temp = QuerySetCell(TempQuery2, "Pass", '1')>
                        <cfelse>
                                <cfset temp = QuerySetCell(TempQuery2, "Fail", '0')>
                        </cfif> 
                </cfloop>
        </cfif>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

This will check each course fetched from the query (assuming the three are fetched)
If any of them fail, it will change the AllPassed variable to false...


<cfset allPassed = true>
<cfloop query="PreReq">
  <cfif preReq.status is "Fail">
     <cfset allPassed = false>
     </cfbreak>
  </cfif>
</cfloop>


<cfoutput>#allPassed#</cfoutput>
erikTsomikSystem Architect, CF programmer

Author

Commented:
i chnage my code to do this . But if i use cfbreak then i get an error saying that cfif required the ending tag

<cfset allPassed = true>
<cfif PreReq.recordcount gt 0>
      <cfloop query="PreReq">
        <cfif ISPREREQDONE neq 'PreReqDone'>
           <cfset allPassed = false>
                 
        </cfif>
      </cfloop>
</cfif>
<cfif PreReq.recordcount gt 0>
      <cfif allPassed eq true>
            <cfset temp = QuerySetCell(TempQuery2, "isMainCourseDisabled", 'No')>
      <cfelse>
            <cfset temp = QuerySetCell(TempQuery2, "isMainCourseDisabled", 'Yes')>
      </cfif>
<cfelse>
      <cfset temp = QuerySetCell(TempQuery2, "isMainCourseDisabled", 'No')>
</cfif>

Sorry it should be

<cfbreak>


Not  </cfbreak>
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

erikTsomikSystem Architect, CF programmer

Author

Commented:
Thank you. And also i rewrote the output to this
<cfif PreReq.recordcount gt 0>
      <cfif FindNoCase(allPassed,"true")>
            <cfset temp = QuerySetCell(TempQuery2, "isMainCourseDisabled", 'No')>
      <cfelse>
            <cfset temp = QuerySetCell(TempQuery2, "isMainCourseDisabled", 'Yes')>
      </cfif>
<cfelse>
      <cfset temp = QuerySetCell(TempQuery2, "isMainCourseDisabled", 'No')>
</cfif>
erikTsomikSystem Architect, CF programmer

Author

Commented:
Great

what is this for?

<cfif FindNoCase(allPassed,"true")>

Why not just

<cfif allPassed is true>

or even

<cfif allPassed>


Here's a bit shorter version..


 
<cfif PreReq.recordcount gt 0 or NOT allpassed>
   <cfset temp = QuerySetCell(TempQuery2, "isMainCourseDisabled", 'Yes')>
<cfelse>
   <cfset temp = QuerySetCell(TempQuery2, "isMainCourseDisabled", 'No')>
</cfif>

Open in new window

erikTsomikSystem Architect, CF programmer

Author

Commented:
thank you.I have another similar question about alternative courses. Do you want me to submit another another question?
erikTsomikSystem Architect, CF programmer

Author

Commented:
for the alternatives. I have 3 set of alternative 2 courses in each set . I need to only take the set  that has completion of all the courses in the set

<cfif Alternatives.recordcount gt 0>
            <cfloop query="Alternatives">
                   <!---if the main course is completed and the completion date for main course is before the completion of alt course USE main course.--->
                   <cfif isDate(TempQuery2.DateCompleted) and DateCompare(TempQuery2.DateCompleted,doneWhen) eq -1>
                        <cfset temp = QuerySetCell(TempQuery2, "useMainCourse", 'Yes')>
                  <cfelse>
                        <cfif FindNoCase(ISALTDONE,"ALtisDone") gt 0><!---if the ALT course is completed --->
                              <cfif TempQuery2.dueDate neq 0> <!---if the due date is not 0 (if the course not setup to run infinitely)--->
                                    <cfoutput>#DateCompare(doneWhen,TempQuery2.dueDate)#</cfoutput>
                                    <cfif DateCompare (TempQuery2.dueDate,doneWhen) eq 0>
                                          <cfset temp = QuerySetCell(TempQuery2, "useMainCourse", 'No')>
                                    <cfelseif DateCompare (doneWhen,TempQuery2.dueDate) eq 1>
                                          <cfset temp = QuerySetCell(TempQuery2, "useMainCourse", 'Yes')>
                                    <cfelseif DateCompare (doneWhen,TempQuery2.dueDate) eq -1>
                                          <cfset temp = QuerySetCell(TempQuery2, "useMainCourse", 'No')>
                                    <cfelse>
                                          <cfset temp = QuerySetCell(TempQuery2, "useMainCourse", 'No')>
                                    </cfif>
                              <cfelse>
                                    <cfset temp = QuerySetCell(TempQuery2, "useMainCourse", 'Yes')>
                              </cfif>
                        <cfelse>
                              <cfset temp = QuerySetCell(TempQuery2, "useMainCourse", 'No')>
                         </cfif>
                  </cfif>
             </cfloop>
      </cfif>
I would do that in the query itself.   I don't know what your column names are, but perhaps you can translate from this example...


 select  top 1
            SetName
         ,  sum(case when isCompleted = true then 1 else 0 end) as NumbCompleted
 from  theTable
 group by SetName
 order by 2 desc


The record returned will be the set with the Most completed.  Just check to see if that number is at least 3.  If it is, you have the alternative set, if not, there is no alternative set (none with three courses completed)

erikTsomikSystem Architect, CF programmer

Author

Commented:
but it could be 2 completed an 1 not
How do you know how many courses are needed in a set..

Take that number and compare it against the number completed
erikTsomikSystem Architect, CF programmer

Author

Commented:
because i bult the admin page where I set things up. And i know that 2 sets setup and have 3 courses in each of those set
> because i bult the admin page

Lol,  what I am saying is...  where do you find the data that says how many courses are required for a set.

If you have a bunch of sets, and each has a different number of required courses, then this information must be stored in the database somewhere, right?  

So, fetch that number from the database and compare it to the count.
erikTsomikSystem Architect, CF programmer

Author

Commented:
I do not store the number of courses, I just run the query through the set and get the all the courses
query . Below is the dump of the query which conatins all the alternatives, WorkAltSetID identifies the set
RESULTSET query
  ALTERNATIVENAME COURSEID DESCRIPTION DONEWHEN EVENTCATALOGID ISALTDONE SETDESCRIPTION TYPE WORKALTSETID WORKFLOWID

1 asd  0  Set 1  02/07/2009  2  ALtisDone  Set 1  3  1  4  
2 Bipolar Disorder_1  1793  set 2  0  0  ALtisNOtDone  set 2  2  8  4  
3 SU GS Advanced - Reminiscence  1927  set 2  0  0  ALtisNOtDone  set 2  1  8  4  
 
CACHED false  
EXECUTIONTIME 15  
SQL Select * from Alternatives  

This might work, the last column counts the records and compares them against the
number completed

select WorkAltSetID
        , count(*) as numbCourses
        , sum(case when ISALTDONE = 1 then 1 else 0 end) as numbCompleted
        , case when count(*) = sum(case when ISALTDONE = 1 then 1 else 0 end)  then 1
                  else 0
          end   as  AreAllCompleted
from table...

erikTsomikSystem Architect, CF programmer

Author

Commented:
when i execute this query i get the error message
:Query Of Queries syntax error.
Encountered "sum ( case. Incorrect Select List,


select WorkAltSetID
        , count(*) as numbCourses
        , sum(case when ISALTDONE = 1 then 1 else 0 end) as numbCompleted
        , case when count(*) = sum(case when ISALTDONE = 1 then 1 else 0 end)  then 1
                  else 0
          end   as  AreAllCompleted
            from Alternatives
erikTsomikSystem Architect, CF programmer

Author

Commented:
i am not sure that QoQ support the sum function

Don't do it as a QoQ, just regular query
erikTsomikSystem Architect, CF programmer

Author

Commented:
my reqular query is this
Select *
                        from FlowPrework
                        inner join FlowWork on FlowWork.WorkFlowID = FlowPrework.WorkFlowID
                        inner join Flow on Flow.FlowID = FlowWork.FlowID
                        where FlowPrework.WorkFlowID in (<cfqueryparam cfsqltype="cf_sql_integer" value="#workFLowID#">) and Flow.Status = 1

And then i have a subquery to find out the completion

So use that query and put in the right column names for the select count...  sample I provided.   I don't know what columns are with what table, so just replace my sample with the right columns..

Also..
You shouldn't do a   SELECT *
 when joining multiple tables, particularly when you have column names that will be duplicated from the different tables

erikTsomikSystem Architect, CF programmer

Author

Commented:
here is the query that i modify but i get an error :Column 'FlowWorkAltSet.WorkFlowID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Select  FlowWorkAltSet.WorkAltSetID,FlowWorkAltSet.WorkFlowID,FlowWorkAltSet.Description as Desc1,
FlowWorkAlt.FlowWorkAltID,FlowWorkAlt.type,FlowWorkAlt.Description as Desc2,FlowWorkAlt.CourseID,
FlowWorkAlt.EventCatalogID,  count(*) as numbCourses
       from FlowWorkAltSet
       inner join FlowWorkAlt on FlowWorkAlt.WorkAltSetID =FlowWorkAltSet.WorkAltSetID
   where FlowWorkAltSet.WorkFlowID in (4)
  group by (FlowWorkAltSet.WorkAltSetID)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial