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#'  )

Not related to your question, but your sql query will crash if the subquery returns more than one row. — Dan Bracuk, Jul 22 '20 at 19:18
yeah something like that. just want to mimic of what I am doing in my sql statement but in cfml (cfscript) — Scott, Jul 22 '20 at 19:32
@JamesAMohler you understand my problem, of what I want to do? — Scott, Jul 22 '20 at 19:53
What version of ColdFusion are you using? That could influence any potential solution. Is this just an exercise in filtering data in CF or are you trying to avoid filtering data in the database, where it is likely more appropriate? — Adrian J. Moreno, Jul 22 '20 at 20:24
I am using CF 2016. Basically I am moving from sql view to retrieving data from an api — Scott, Jul 22 '20 at 20:48
@Scott, your JSON is incorrect as it stands. You should add ] at the end. — BKBK, Jul 23 '20 at 11:06

score 0 · Answer 1 · answered Jul 22 '20 at 20:31

This is more of a comment than an answer, but here goes. Are you trying to do this

<cfscript>
jsonData = [...];


filteredJsonData = jsonData.filter(function(row) {
    return row.department_nbr == variables.DEPT_FUND_NBR; 
})
</cfscript>

What the ArrayFilter() function does, is it returns a new array that is a subset of the original. The function inside must return true or false. If it is true, it will be a part of the new array. I used the member function approach because I don't like typing.

For more info. on ArrayFilter(), see https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-a-b/arrayfilter.html

Note: that we have to put the dept_fund_nbr into a scope that is visible to the inner function. It is likely that there is a better scope for your code.

My solution is not complete, try some stuff out on your data. I think my suggestion will get you 75% there, but have to fill in the blanks on the rest. — James A Mohler, Jul 22 '20 at 20:52
is my above comment helpful? What other blanks you need to fill? — Scott, Jul 22 '20 at 20:55

score 0 · Accepted Answer · answered Jul 23 '20 at 13:38

0

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

answered Jul 23 '20 at 13:38

BKBK

484
2
9

Thanks for your answer. However, when I place your code inside my current code the `writeDump` shows an empty Array – Scott Jul 23 '20 at 14:45
and quick question is it getting all my fields that I have in my sql statement `select *` ? – Scott Jul 23 '20 at 14:46
@Scott, it is very likely that the paths don't match. Are your JSON files stored in a directory called 'jsons' and is this directory in the same location as the CFM page that contains the above code? – BKBK Jul 23 '20 at 14:59
json file is stored on the desktop and the code files is store in a folder – Scott Jul 23 '20 at 15:01
The above solution gets all the fields contained in each JSON file that matches the `departmentNbr` criterion. – BKBK Jul 23 '20 at 15:03
also I think I have a empty struct created already `myApiOutput = {}` should.I replace your `var JsonData = {};`? – Scott Jul 23 '20 at 15:06
@Scott, it will make life easier to either comment on my code or on yours. But not on both at the same time. My code is based on the 3 assumptions I mentioned. Your code might be based on assumptions I am unaware of. – BKBK Jul 23 '20 at 15:11
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/218450/discussion-between-scott-and-bkbk). – Scott Jul 23 '20 at 15:13
Do you have a collection of JSON files on the desktop? If so, then you could adapt my code as follows. (1) Create the directory called jsons on your desktop; (2) Copy the JSON fles into it; (3) In my code, replace `expandPath('jsons')` with `"C:\Users\Desktop\jsons"`. – BKBK Jul 23 '20 at 15:18

How to mimic sql statement logic in cfml / coldfusion?

2 Answers2