Skip to main content
BACFL
Inspiring
May 7, 2014 2:24 PM UTC
Question

Crosstab - array error

get the following error with my code below. Please Help.

Table will be rows = dscgrd, columns = dsckcrse, rows x cols = count of dsckextid.  All grouped by teacher_anumber.

  

0 is not greater than zero or less than or equal to 0.

The range passed to ArraySet must begin with a number greater than zero and less than or equal to the second number.
The error occurred in C:/ColdFusion10/cfusion/wwwroot/CFIDE/groups/crosstabs.cfm: line 41
39 :           GROUP="dscgrd">  40 :  <tr><th>#dscgrd#</th>  41 : <CFSET temp = ArraySet(course_idarray, 0, b, "N/A")>  

<CFQUERY NAME="crosstabquery" DATASOURCE="LIISthinkgate">
  SELECT     teacher_anumber, dsckcrse, dsckextid, dscgrd
    FROM     evaluationstudentcourse
  ORDER BY     dscgrd, dsckcrse
</CFQUERY>

<CFQUERY NAME="crosstabcolumns" DBTYPE="query">
  SELECT distinct     dsckcrse
    FROM             crosstabquery
  ORDER BY             dsckcrse
</CFQUERY>

<CFSET course_idlist = ValueList(crosstabcolumns.dsckcrse)>
<CFSET course_idheaders = ListToArray(course_idlist)>
<CFSET b = ArrayLen(course_idheaders)>

<CFSET course_idarray = ArrayNew(1)>

------------------------------------------------------------
<table>
<CFOUTPUT QUERY="crosstabquery"
          GROUP="dscgrd">
<tr><th>#dscgrd#</th>
<CFSET temp = ArraySet(course_idarray, 0, b, "N/A")>
     
     
     
<CFOUTPUT>
   <CFSET i = ListFind(course_idlist
                  , crosstabquery.dsckcrse)>
   <CFSET temp = ArraySet(course_idarray, i, i, crosstabquery.dsckcrse)>
</CFOUTPUT>
<CFSET rowtotal=0>
<CFLOOP index="j" FROM="1" TO="#b#">
   <td>#course_idarray#</td>
   <CFSET rowtotal=rowtotal+course_idarray>
</CFLOOP>
<td>#rowtotal#</td>
</tr>
</CFOUTPUT>
</table>

This topic is closed to new replies. Start a new post to keep the conversation going.

7 replies

kylec46637293
Participant
Sep 20, 2016 7:02 PM UTC

Hello,

I am having a similar issue right now as well. On CF9, I am having no issues. Currently, I am testing on a new web server with CF11 update 10. The error I am receiving is similar:

0 is not greater than zero or less than or equal to 0.The range passed to ArraySet must begin with a number greater than zero and less than or equal to the second number.

Here is a snippet of my code (not sure if you will need more than that). The bolded code is where it is saying it is erroring out on and I believe is where I need to make a change. I am more or less looking for more ideas on what to check or if something has changed since CF9 that I have not read on.

---

<cfset bucketlist = ValueList(crosstabcolumns2.ts_desc)>

<cfset bucketheaders = ListToArray(bucketlist)>

<cfset b = ArrayLen(bucketheaders)>

<cfset bucketarray = ArrayNew(1)>

<cfset bucketTotal = ArrayNew(1)>

<cfset temp = arrayset(bucketTotal,1,b,"0")>

<cfoutput query="summedresultsNewOld" group="classcode">

  <table border="1" cellpadding="5">

  <tr><th width="225">MAIN CAMPUS</th>

     <cfloop index="i" From="1" to="#b#">

       <th>#bucketheaders#</th>

     </cfloop>

  <th width="125">TOTALS</th>

  </tr>

  <cfoutput group="ts_type" >

    <tr><th>#ts_type#</th>

   <cfset temp = arrayset(bucketarray,1,b,"0")>

   <cfoutput>

        <cfset i = listfind(bucketlist, trim(summedresultsNewOld.ts_desc))>

              <cfset temp = ArraySet(bucketarray, i, i, summedresultsNewOld.headcount)>

              <cfset temp = ArraySet(BucketTotal,i, i, (BucketTotal + summedresultsNewOld.headcount))>

   </cfoutput>

   <cfset rowtotal = 0>

   <cfloop index="j" from="1" to="#b#">

     <cfset rowtotal = #rowtotal# + #bucketarray#>

     <td width="125">#bucketarray#</td>

   </cfloop>

   <td>#rowtotal#</td>

      </tr>

  </cfoutput>

   <tr>

      <th bgcolor="##999999">TOTALS</th>

        <cfloop index="j" from="1" to="#b#">

       <td bgcolor="##999999">#BucketTotal#</td>

        </cfloop>

     <td bgcolor="##999999">#ArraySum(BucketTotal)#</td>

    </tr> 

  </table>

</cfoutput>

---

This is the result of what it should look like (currently in CF9). Any help is appreciated!

Translate
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
Sep 21, 2016 5:04 AM UTC

@kylec46637293

Start your own thread.

Translate
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
May 10, 2014 7:19 PM UTC

3 remarks:

  • The line <CFSET temp = ArraySet(course_idarray, 1, b, "N/A")> should be put outside the cfoutput loop. There is no need for it to run at every pass of the loop.
  • The cfoutput tag within <cfoutput query="crosstabquery"> is unnecessary.
  • The line <CFSET temp = ArraySet(course_idarray, i, i, crosstabquery.dsckcrse)> is perhaps equivalent to <CFSET course_idarray = crosstabquery.dsckcrse)>
Translate
BACFLAuthor
Inspiring
May 12, 2014 2:35 PM UTC

Hi BKBK!

I appreciate your assistance.  What appears to have been the problem here from some research that I did is that a Cold Fusion array does not start with zero, it starts with 1.  So I changed the array to: "

<CFSET temp = ArraySet(bucketarray, 1, 2, crosstabquery.amount)>" so that the count starts with 1 and the next count is 2.

Here is my updated code, for which as you can see below the COUNT that I added is not working properly.

<CFQUERY NAME="crosstabquery" DATASOURCE="LIISthinkgate">
  SELECT distinct teacher_anumber,dsckcrse,dscgrd,  count(dsckextid) as amount
    FROM evaluationstudentcourse
    WHERE teacher_anumber = 'A054863'
    group by teacher_anumber,dsckcrse,dscgrd
  ORDER BY teacher_anumber,dscgrd,dsckcrse
</CFQUERY>

<CFQUERY NAME="crosstabcolumns" DBTYPE="query">
  SELECT distinct dsckcrse
    FROM crosstabquery
    WHERE dsckcrse = crosstabquery.dsckcrse
  ORDER BY dsckcrse
</CFQUERY>


<CFSET bucketlist = ValueList(crosstabcolumns.dsckcrse)>

<CFSET bucketheaders = ListToArray(bucketlist)>

<CFSET b = ArrayLen(bucketheaders)>

<CFSET bucketarray = ArrayNew(1)>

<CFSET temp = ArraySet(bucketarray
                  , 1, b, "N/A")>

<CFOUTPUT QUERY="crosstabquery" GROUP="teacher_anumber">
  <h4>Anumber=#teacher_anumber#</h4>
  <table border="1" cellpadding="5">
  <tr><th></th>
  <CFLOOP INDEX="i" FROM="1" TO="#b#">
    <th>#bucketheaders#</th>
  </CFLOOP>
  </tr>
  <CFOUTPUT GROUP="dsckcrse">
    <tr><th>#dscgrd#</th>
  
  
       <CFSET i = ListFind(bucketlist
                  , crosstabquery.dscgrd)>
        <CFSET temp = ArraySet(bucketarray
                     , 1, 2, crosstabquery.amount)>
 
    <CFLOOP index="j" FROM="1" TO="#b#">
      <td>#crosstabquery.amount#</td>
    </CFLOOP>
    </tr>
    </CFOUTPUT>
  </table>
</CFOUTPUT>

I got rid of the original error, however I am now not getting "0" where there should be no count. For instance 5001020 (dsckcrse), 02 (dscgrd) should have 136.  The others (01,03,04,05,KG) should be zero.  I get the following:

Anumber=A054863

500101050010205001030500104050010505001060
01120120120120120120
02136136136136136136
03145145145145145145
04163163163163163163
05165165165165165165
KG117117117117117117


Appreciate any insight you have.

Translate
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
May 12, 2014 7:37 PM UTC

Please see my first post.

Translate
BACFLAuthor
Inspiring
May 7, 2014 2:27 PM UTC

An update.

this line: <CFSET temp = ArraySet(course_idarray, 0, b, "N/A")>

Should be: <CFSET temp = ArraySet(course_idarray, 1, b, "N/A")>

Translate
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
May 10, 2014 7:25 PM UTC

0 is not greater than zero or less than or equal to 0.

I shall report that error message as a bug. It is false. It should read: "0 is not greater than zero and less than or equal to 0."

Translate
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices