How to add a new line in the middle of cfquery result?

Question

I have a query result set from cfquery. I just want to add a new value after a specific line number. But when each time it is checked whether the line is at the end.

Is there a way to insert a row in the middle of a query?

 <cfquery datasource="cse" name="abc">
    select * from grade 
 </cfquery>

 <cfset i = 0>
 <cfloop query="abc">
   <cfset i = i+1>
   <cfif i eq 2>
      <cfset queryAddRow(abc)>
   </cfif>  
 </cfloop>

Answer 1

You cannot, easy. You have coupla options.

<cfquery name="resultSet" dbtype="query">
    SELECT col1, col2, etc
    FROM yourQuery
    WHERE [somecondition matching the "top" rows]

    UNION

    SELECT 'value' AS col1, 'value' AS col2, etc

    UNION

    SELECT col1, col2, etc
    FROM yourQuery
    WHERE [somecondition matching the "bottom" rows]
</cfquery>

Or you can simply iterate over the original query by building a new query using queryNew(), queryAddRow()and querySetCell(). At the appropriate point in the loop ... add the row you want to insert, and then add the rest.

There is no elegant way that I can think of.

Answer 2

, , .

QueryNew() .

!

, , - , 10 . . , 15, 10 20.

, !

Answer 3

, , ORDER BY'd CFQUERY.

, ( ), ORDER BY, , , , () , .

Answer 4

Not knowing the goal you are trying to accomplish, my first tip will be in line with the other answers. Add rows that can later be sorted using Order By. However, if you really want to insert a row at a specific position in an existing query, this should do it for you. Note that you need to define the columns in QueryNew (), so I provided an example.

 <cfquery datasource="cse" name="abc">
    select student, teacher from grade 
 </cfquery>

 <cfset abc_new = QueryNew("student,teacher","varchar,varchar")>

 <cfloop query="abc">
   <!--- ADD NEW DATA TO QUERY AT ROW 2 --->
   <cfif CURRENTROW eq 2>
     <cfset QueryAddRow(abc_new) />
     <cfset QuerySetCell(abc_new,"STUDENT","Tommy Oliver") />
     <cfset QuerySetCell(abc_new,"TEACHER","Ms. Appleby") />
   </cfif>

   <!--- COPY ORIGINAL DATA TO QUERY, ROW NUMBERS NOT PRESERVED --->
   <cfset QueryAddRow(abc_new) />
   <cfset QuerySetCell(abc_new,"STUDENT",abc.STUDENT) />
   <cfset QuerySetCell(abc_new,"TEACHER",abc.TEACHER) />
 </cfloop>

 <cfdump var="#abc_new#">