7 Replies Latest reply on Oct 1, 2016 3:50 AM by BKBK

    Crosstab - array error

    BACFL Level 1

      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[j]#</td>
         <CFSET rowtotal=rowtotal+course_idarray[j]>
      </CFLOOP>
      <td>#rowtotal#</td>
      </tr>
      </CFOUTPUT>
      </table>

        • 1. Re: Crosstab - array error
          BACFL Level 1

          An update.

           

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

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

          • 2. Re: Crosstab - array error
            BKBK Adobe Community Professional & MVP

            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[i] = crosstabquery.dsckcrse)>
            • 3. Re: Crosstab - array error
              BKBK Adobe Community Professional & MVP

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

              • 4. Re: Crosstab - array error
                BACFL Level 1

                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[i]#</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.

                • 5. Re: Crosstab - array error
                  BKBK Adobe Community Professional & MVP

                  Please see my first post.

                  • 6. Re: Crosstab - array error
                    kylec46637293

                    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[i]#</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[i] + summedresultsNewOld.headcount))>

                       </cfoutput>

                       <cfset rowtotal = 0>

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

                         <cfset rowtotal = #rowtotal# + #bucketarray[j]#>

                         <td width="125">#bucketarray[j]#</td>

                       </cfloop>

                       <td>#rowtotal#</td>

                          </tr>

                      </cfoutput>

                       <tr>

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

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

                           <td bgcolor="##999999">#BucketTotal[j]#</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

                    • 7. Re: Crosstab - array error
                      BKBK Adobe Community Professional & MVP

                      @kylec46637293

                      Start your own thread.