Parsing a query in coldfusion

Question

I have a query as below which

 <cfquery name="qryGetXXX" datasource="#sDataSource#">
     SELECT        COUNT(*) AS TotalCount,Quality.Qualitydesc
        FROM          QualityCheck INNER JOIN Quality 
        ON QualityCheck.QualityID = Quality.qualityID
        WHERE DATEDIFF(d,DueDate,GETDATE()) >= 90
        GROUP BY quality.qualityDesc               
  </cfquery>

will result in

1) *total count* 21 *QualityDesc*   IO
2) *total count* 1  *QualityDesc*   Max
3) *total count* 1  *QualityDesc*   Min
4) *total count* 1  *QualityDesc*   Other
5) *total count* 3  *QualityDesc*   Reg

In order to get the first row I am using,

<cfif #qryGetXXX.RecordCount# gt 0  >
   <cfloop query="qryGetXXX" startrow="1" endrow="1">
      <cfset XXXTimeTotal =#qryGetXXX.TotalCount# >
   </cfloop>
 <cfelse>
    <cfset XXTotal = 0 >
 </cfif>

which is checking for the first the recordcount of the whole query but is there any way I can check whether the first row (i.e. startrow 1 and endrow 1) is has a value and then if the startrow 2 and endrow 2 has a value and so on? Can I put the results in an array and will that be easier?

Frank,

You are correct, i am new to coldfusion, hence all the confusion.The query provides me with the results i.e i have the result set as explained above, however i cannot figure out a way to check every row individually. For example i have to check if there are results for the first row and if it does i have to pass that value to my output, and if doesnot i have to put in no value was entered or '0'. I need to do this for the five rows and there will always only be 5 rows of results, which is why i was using start and endrow, however start and endrow donot allow me the flexibility to check for empty row values. In essence i would like to check the row as something like below.

<!--- check the first row of resuslt i.e. Max values ---!>
    <cfif startrow1.endrow1  gt 0  >
        <cfset nIOCount =#qryGetXXX.TotalCount# >
    <cfelse>
        <cfset nIOCount = '0'>
    </cfif>  
    <!--- then check the second row resuslts Max ---!>
    <cfif startrow2.endrow2  gt 0  >
        <cfset nMaxCount =#qryGetXXX.TotalCount# >
    <cfelse>
        <cfset nMaxCount = '0'>
    </cfif>  
 <!---Output the values---!>

Totals

<tr>
    <th>IO</th>
    <td>#nIoCount#</td>

</tr>
<tr>
    <th>Max </th>
    <td>#nMaxCount#</td>

</tr>
<tr>

I know i cannot reference startrow and endrow as i want, so i am looking for a way to reference each row individually in the result set some other way. Any suggestions?

thanks

You can loop through the query without startrow and endrow and that will loop through all the rows. — Matt Busche, Jun 04 '14 at 22:05
To see if the the row is empty or has a value and if there is no value just skip that row or assign no value to that row and then move to the next row and so on. — user2799983, Jun 04 '14 at 22:09
I am displaying each row, i can get the running total with the initial count i believe. — user2799983, Jun 04 '14 at 22:32
Your query columns and the columns you're Referencing don't match up. This question could use a lot of clarification. — Matt Busche, Jun 04 '14 at 22:37
You still have not explained your goal in plain english (not code). *Why* are you trying to do this? What is the ultimate goal? [XY Problem](http://meta.stackexchange.com/a/66378). — Leigh, Jun 05 '14 at 00:34
*RE: i am new to coldfusion* @user2799983 - That is one of the reasons we are asking you to describe the problem you are trying to solve - not the code you *think* will solve it. If you are unfamiliar with CF, it is entirely possible the above is not the best way to solve the problem. I suspect what you really need is a simple OUTER JOIN. However, until you explain exactly *what* you are trying to achieve - all we can do is offer guesses. — Leigh, Jun 05 '14 at 17:20
I want to manipulate each row of the result individually, As explained above i need to check every row for a value, and if does not have a value i need to publish/output a zero, and i cant find a way with coldfusion to accomplish my problem. The SQL query itself is rendering exact results as i want, but i am stumbling on the coldfusion part. — user2799983, Jun 05 '14 at 17:30
That is still describing what you think is the solution, rather than the problem. I have no idea what you mean by *"does not have a value"*. If you dump the `qryGetXXX` query, what is wrong with the initial results? In other words, what problem **with the data** are you trying to solve here? Is the query missing counts for some of the descriptions, ie "Qualitydesc"? — Leigh, Jun 05 '14 at 18:27
Leigh, The problem is that if the query returns no value for a particular description i.e. if 1) *total count* is 0 for the *QualityDesc* IO, then i have to output 0 in my page, if not i have to output the value itself. once again result set i.e. each row of the result has to be looked at or manipulated one at a time and i cant see a way in coldfusion to do this. Man this is the best way i can explain this i donot know of any other way to explain this. The count and the query are fine. — user2799983, Jun 05 '14 at 18:50
(EDIT) *the query returns no value for a particular description* Okay, if you mean the description "IO" is not included in query results at all, then that is what I suspected. Since you are using an INNER join, the query will only return counts for descriptions that exist in both tables. If you want to include *all* description, even if there is no match in "QualityCheck", you need to use an OUTER join instead. — Leigh, Jun 05 '14 at 19:02
(Edit) Otherwise, you need to explain what "empty row values" means to you. Your sample above does not contain anything that looks like an empty total value to me. BTW, if you are having difficultly describing something, you can always include a screen shot to clarify. Like they say "a picture is worth a thousand words.". — Leigh, Jun 05 '14 at 19:22
Leigh, my dumb behind finally understands what you meant by outer join, it behooves you to slap me digitally hahahaha. I handled the empties with an outer join, This case is closed and as MR Tony always reminds everyone "boys and girls if you are riding your bike tonight, do wear white". — user2799983, Jun 05 '14 at 20:56

Frank Tudor · Answer 1 · 2014-06-04T23:48:12.663

1

That query/code is rough (I rendered it to something I could work with).

Your query: (if yours works just leave it as is (I'm making two assumptions below)).

<cfquery name="test" datasource="#sdatasource#">
select count(a.*) as totalcount, a.qualitydesc
  from quality a, qualitycheck b
 where a.qualityid = b.qualityid
   and datediff(d,a.duedate,getdate()) >= 90
 group by a.qualitydesc               
</cfquery>

Then do your check if and sets like this: (instead of an array I did a struct (making more assumptions as well)).

<cfset totals = structnew()>

<cfif test.recordcount> 
<cfoutput query="test">
    <cfif test.totalcount neq ""> 
    <cfset StructInsert( totals, test.QualityDesc, test.totalcount )>
    <cfelse>
    <cfset xxtotal = 0>
    </cfif>
</cfoutput>
</cfif>

In fact, you can skip that nested if statement and for the xxtotal and do something else out of the loop leaving you with tighter code that looks like this:

<cfset totals = structnew()>

<cfif test.recordcount> 
<cfoutput query="test">
<cfset StructInsert( totals, test.QualityDesc, test.totalcount )>
</cfoutput>
</cfif>

So if these are your targets:

1) totalcount 21 QualityDesc IO 
2) totalcount 1  QualityDesc Max 
3) totalcount 1  QualityDesc Min 
4) totalcount 1  QualityDesc Other 
5) totalcount 3  QualityDesc Reg

Then your looped values will look like this and any missing values or whatever will be bypassed (again you will need to do some checking down stream)...

totals.IO = 21
totals.Max = 1
totals.Min = 1
totals.Other = 1
totals.Reg = 3

Let me know if this helps and makes sense.

edited Jun 04 '14 at 23:48

answered Jun 04 '14 at 23:28

Frank Tudor

4,226
2
23
43

Can we let you know if it doesn't make any sense? While the question may be vague, this answer makes me say, "Uh?". What were these two assumptions you mentioned? – Dan Bracuk Jun 05 '14 at 00:37
Hi Dan, that query gave me pause. For example, the where clause: `datediff(d,duedate,getdate()) >= 90` what table is that coming from? (i'm used to being explicit but lazy code might work (probably does)) next was `count()` personally, I would have pointed to a primary key column and target a specific table. In fact, I would refactor that entire query. Next in the actual code, I moved from an array to a struct. I think structs are easier to deal with. Not that it matters. Also that `xxTotal = 0` 'nested if' does he really need it nested? Probably not. Hence the second optional code block. – Frank Tudor Jun 05 '14 at 01:47
I thought the query was ok. It was the rest of it that was unclear. While it is unclear what the questioner wants to accomplish, it is less clear what you are attempting to accomplish. – Dan Bracuk Jun 05 '14 at 01:58
@DanBracuk :) Maybe. BUT these are the kind of answers one gets when the questions are not super-awesome. The guy is a new coder, I would also assume this is not his first attempt, I can also assume he is tired. You can tell all this by the variables, the structure, the ## in the cfset that he doesn't need...the attempt to solve something for one single row of a recordset. So if you must set a variable value pair (when you already have one in the query) then I think it was a good approach to setting ancillary variable/value pairs. My answer is in my organization. – Frank Tudor Jun 05 '14 at 03:14

score 0 · Answer 2 · edited May 23 '17 at 12:09

(Summary from comments...)

If you mean some of the descriptions, like "IO", are not included in query results at all, then that is what I suspected earlier. Since you are using an INNER join, the query will only return counts for descriptions that exist in both tables. If you want to include all descriptions, even if there is no match in "QualityCheck", you need to use an OUTER join instead. For example:

SELECT  Quality.Qualitydesc
        , COUNT(QualityCheck.QualityID) AS TotalCount
FROM   Quality LEFT JOIN QualityCheck
           ON  QualityCheck.QualityID = Quality.qualityID
           AND DATEDIFF(d,DueDate,GETDATE()) >= 90
GROUP BY quality.qualityDesc

Though as I mentioned on another thread, how you construct the filter can negatively impact the query's performance. See What makes a SQL statement sargable? for details and alternatives.

Side note, while you provided a lot of detail here, you omitted the most important part: .. the actual goal ;-) You will get faster and more accurate responses if you clearly summarize what you are trying to do first, then include the code.

Parsing a query in coldfusion

2 Answers2