Coldfusion Structures Null Values

[apocalipsis19]

Hi community!

I am writing a report that breaks school down to Grade-Level - Ethnicity - Gender counts for students with more than 3 unlawful absences. I write a query, then I turned that query into a structure in the form:

<cfset StudentListStruct=session.DBAccess.QueryToStruct(QuerySet=getTruancyCalculations, Grouping="GRADE_LEVEL,ETHNICITY,GENDER")/>

QueryToStruct is a custom function that we created that turns any resultset in a structure. I am grouping my counts by Grade Level, Ethnicity then Gender. When I dump my structure I can see that for some grade levels one of the elements in my ethnicities is empty, null or whatever you want to call it.

This is what I do to print:

<cfif StructKeyExists(StudentListStruct[getUniqueGradeLevels.GRADE_LEVEL],getUniqueEthnicities.ETHNICITY)AND StructKeyExists(StudentListStruct,getUniqueGradeLevels.GRADE_LEVEL AND getUniqueEthnicities.ETHNICITY neq '')>
#StudentListStruct[getUniqueGradeLevels.GRADE_LEVEL][getUniqueEthnicities.ETHNICITY][getUniqueGenders.GENDER[looper]]#
<cfelse>
0
</cfif>

That code is enclosed 3 loops, one loop that loops throught he grade levels, one loop that loops through the ethnicities and one loop that loops through the genders. I am getting all zeros because my first condition is never being accomplished. I don't want that. I attached the code at the end of this message.

Any thoughts?

Thanks!

[Dan_Bracuk]

I only read part of your code but my intitial thought is that you are overengineering something simple. If you want information about students with more than 3 absences, why not write a query that selects that data and then present it?

[apocalipsis19]

Well, the school wants to show that data in a report broken down by grade level, ethnicity and gender.

Any thoughts?

[-__cfSearching__-]

apocalipsis19 wrote:
> Any thoughts?

I agree. This seems like something that could be achieved much more easily done inside a sql query. I think you are over-complicating the code. Perhaps you are unfamiliar with sql, or are uncertain how to write a query that would produce those results. If you could post the structure of the pertinent tables, column names and relationships, others would be better able to assist you.

[Dan_Bracuk]

quote:

---

Originally posted by: apocalipsis19
Well, the school wants to show that data in a report broken down by grade level, ethnicity and gender.

Any thoughts?

---

start with

select studentid, sex, race, grade, count(race) absents
from some tables
where your conditions are met
group by studentid, sex, race, grade
having count(race) >= 3

[apocalipsis19 · Accepted solution]

Thanks Dan! I worked my problem around. The problem is that I was dumping my structure. I forgot that the dump function automatically sorts the structure in a way that makes sense to the human eye to see on the screen. So I thought that I could manipulate my elements just the way I was looking at them. I was wrong. I sorted my structure and sub-structures using:

ListSort(StructKeyList(StudentListStruct)

This way I got a sorted list of elements. I was getting an exception because obviously I was referencing the wrong position of my data because my data was not organized as I thought it was. Once I sorted my data my problems disappeared. I just looped through my lists and verified the existence of the element then printed on screen otherwise prints zero.

My problem was not writing a query that group my counts in the form of GRADE LEVEL - ETHNICITY - GENDER, the problem was to layout those elements in an HTML table for printing. I thought it was more intelligent to use the power of structures here. So to print my elements I wrote:

<cfif StructKeyExists(StudentListStruct, x) AND StructKeyExists(StudentListStruct, y) AND StructKeyExists(StudentListStruct, z)>
#StudentListStruct.TOTALSTUDENTS#
<cfelse>
0
</cfif>

Where X represent an array of grade levels, Y represents an array of ethnicities and Z represents a list with two elements F and M for female and male.
Thanks to those who replied and helped! I really appreciate it!

Apocalipsis!

[Newsgroup_User]

apocalipsis19 wrote:
> ListSort(StructKeyList(StudentListStruct)

If you care to, you can replace that with the single structSort(...)
function. Which returns a list of keys sorted by the desired structure
element.