Crosstab - array error

Participant ,
May 07, 2014 May 07, 2014

Copy link to clipboard

Copied

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>

TOPICS
Advanced techniques

Views

661

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 07, 2014 May 07, 2014

Copy link to clipboard

Copied

An update.

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

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 10, 2014 May 10, 2014

Copy link to clipboard

Copied

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."

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 10, 2014 May 10, 2014

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 12, 2014 May 12, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 12, 2014 May 12, 2014

Copy link to clipboard

Copied

Please see my first post.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 20, 2016 Sep 20, 2016

Copy link to clipboard

Copied

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!

arrayset.png

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Sep 20, 2016 Sep 20, 2016

Copy link to clipboard

Copied

LATEST

@kylec46637293

Start your own thread.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines