How to mimic sql statement logic in cfml / coldfusion?

Question

I Am coming to a problem where I am trying to convert my sql statement to do the same logic in cfml (cfscript>) so, I am trying to mimic this sql statement to display the results inside my cfscript. can somebody help me solve this issue? thanks for the help.

SQL:

    select * from myapp.GGG_myphone where department_name 
        like (select distinct department_name from myapp.GGG_myphone
         where department_nbr like '#DEPT_FUND_NBR#'  ) 

Answer 1

@Scott, I am assuming that:

1. Each row from your select query represents a JSON similar to the one you have shown. After all, the column names match the keys in the JSON.

2. You already have or intend to have a number of such JSONs stored, each as a file. I infer this from your file-read code. Let's say the files are stored in a directory called 'jsons'.

3. What you are looking for is ColdFusion code that will select the JSON files that match the criteria in your select query.

   <cfscript>
   array function getJSONByDeptName(string departmentNbr)  {
   var JsonFiles = arrayNew(1); 
   var JsonFile = "";
   var JsonData = {};
   var collectionOfMatchingJsonData = arrayNew(1); 
   var departmentNamesList = "";
   
   /* Here, we assume the JSON files are stored in subdirectory 'jsons' within current directory*/
   JsonFiles = directorylist(expandPath('jsons'));
   
   if (arrayLen(JsonFiles) gt 0) {
       for (var fileNumber=1; fileNumber lte arrayLen(JsonFiles); fileNumber=fileNumber+1) {
           /* Get each file in turn*/
           JsonFile = fileRead(JsonFiles[fileNumber]);
   
           /*Read its JSON content. The result is an array containing one item of type struct*/
           jsonData = deserializeJSON(JsonFile);
   
           /* Reminder: jsonData[1] is a struct. Check whether the departmentNbr key in the struct 
              matches the input value of departmentNbr. 
              If it does, add the jsonData to the list, avoiding duplicate values of departmentName */     
           if (jsonData[1].departmentNbr eq arguments.departmentNbr and ListFindNoCase(departmentNamesList, jsonData[1].departmentName) eq 0) {
               arrayAppend(collectionOfMatchingJsonData,jsonData)
   
               /* Add department name to list.  */
               departmentNamesList = listAppend(departmentNamesList, jsonData[1].departmentName);
   
           }
   
       }
   
   }
   
   return collectionOfMatchingJsonData;    
   }
   
    // Test it, using departmentNbr '1982' 
    writedump(getJSONByDeptName('1982'));
    </cfscript>