Mimicing the same logic as my sql statement not working with cfscript/cfml?

Question

I am coming to a problem where I am moving from sql query to use of an api. So, I started doing the same thing that my sql statement is doing. But for some reason it is retrieving all the job status and job class and pay grades. However, what my expected results should be is where when I delete a job_class which is something like 0707 from the json or from the database it will show on my screen that that job class is missing if no job class is missing then it will just show a Struct (empty). Can anyone tell me what I am doing wrong in my code? thanks for the help.

SQL:

select distinct job_class, PAY_GRADE 
from app.my_databaseFone 
where job_status like 'Active' 
order by job_class

Code:

<cffunction  name="my_api"
    access="private"
    description="my api data">
    
     <cfset qFullmyApi = fileRead("C:\Users\Desktop\myjson.json")>  
     <cfset jsonData = deserializeJSON(qFullmyApi) />  
 
     
      <cfscript>
     
     
        myAPIOutput = {};
     
     
    
      for (item in jsonData) {

 
        
        if (structKeyExists(myAPIOutput, item.jobStatus)) {
           
              matchedValue = false; 
                 for (i=1; i <= arrayLen(myAPIOutput[item.jobStatus]); i=i+1) {
                          
                   if (item.jobClass == myAPIOutput[item.jobStatus][i].job_class
                    && item.payGrade == myAPIOutput[item.jobStatus][i].pay_grade) {
    
                         matchedValue = true;
                            break;
                    
    
                   }
      
              }
               if (matchedValue == false) {
    
                   arrayAppend(myAPIOutput[item.jobStatus], {"JOB_CLASS":item.jobClass,
                   "PAY_GRADE": item.payGrade});
    
               }
            } else {
                
                myAPIOutput[item.jobStatus] = [{"JOB_CLASS":item.jobClass,
                 "PAY_GRADE": item.payGrade}]; 
           }
    
        }
            
        return myAPIOutput;
    
          
    
      </cfscript>
</cffunction>

Are on a Mac or PC and do you know how? And why are you asking the same questions as @Scott? — James A Mohler, Aug 08 '20 at 22:49
on a mac why whatsup? I am Scott lol I got limited on my questions So I am on my other account I had it earlier — , Aug 08 '20 at 22:53
Do you have screenshots for this question? That is Shift-⌘-4 and pick what you need. The put it into the question. We still need to know what the input and what the output is supposed to be. — James A Mohler, Aug 08 '20 at 22:56
So, my query output is this: https://imgur.com/a/mN5tdOX .. However, my code is doing the same thing but it is getting all the job status.. Do you see the where clause? I want to get only the active job classes — , Aug 08 '20 at 23:02
I know your account has been closed, but consider this. You need to try stuff. You need to `writedump()` intermediate results. You need to break big problems into smaller problems. And if the data is too complicated, try simpler data. You can't just show the same code and ask the same questions. Also, SO has an easy image insert functionality. Be familiar with it. Part of using SO and Stack Exchange is writing good questions. I sometimes just look a things that have nothing to do with programming, just to see what works and what doesn't. — James A Mohler, Aug 09 '20 at 02:55
Just between you and me. 90% of what I write is garbage. But being able to find the 10% that is good is what makes me a (hopefully) a good programmer. I try stuff. When I am doing CF work, I love `` and `writedump()`. When I do JS, I find myself adding in `console.log(JSON.stringify())` all the time. At this stage, it seems like you need to learn to debug. I hope this helps. — James A Mohler, Aug 09 '20 at 02:59

score 0 · Answer 1 · answered Aug 08 '20 at 23:23

In your WHERE clause, you filter for job_status like 'Active' (btw, no need for like here, use = instead) but there is NO similar filter in your ColdFusion code.

myAPIOutput will include all job_status types not just Active ones. Just display the ones in myAPIOutput['Active'] and that should match to your SQL query. If there is no active jobs at all, myAPIOutput['Active'] not exists

Mimicing the same logic as my sql statement not working with cfscript/cfml?

1 Answers1