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:
x
select * from myapp.GGG_myphone where department_name
like (select distinct department_name from myapp.GGG_myphone
where department_nbr like '#DEPT_FUND_NBR#' )
Advertisement
Answer
@Scott, I am assuming that:
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.
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’.
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>