Query of Queries - returntype = "array"

This works properly if I don’t try to return an array of structs, but it would be convenient if that worked. At the moment, however, I get the following error. Is this a bug, improper syntax, or just not possible?

temp = queryExecute("
  SELECT
    consultantID,
    date,
    model,
    SUM( accounts ) AS accounts,
    SUM( aua ) AS aua
  FROM temp
  GROUP BY consultantID, date, model",
  {},
  { dbtype : "query", returntype : "array" }
)

Before we go any further, shouldn’t there be a semicolon at the end of a line?

I have a portable repro here (TryCF.com):

week = queryNew("id,en,mi", "integer,varchar,varchar", [
	[1,"Monday","Rāhina"],
	[2,"Tuesday","Rātū"],
	[3,"Wednesday","Rāapa"],
	[4,"Thursday","Rāpare"],
	[5,"Friday","Rāmere"],
	[6,"Saturday","Rāhoroi"],
	[7,"Sunday","Rātapu"]
])

weekAsArray = queryExecute("SELECT * FROM week ORDER BY id", {}, {dbtype="query",returntype : "array"})

writeDump(weekAsArray)

And that runs fine on both versions of Lucee that trycf has (5.4.3.16 and 6.0.0.445-SNAPSHOT).

I can’t find a bug in Jira, but poss something fixed since 5.3.10.120?

Hrm. Also runs on a 5.3.9.173 container I have. So poss something more afoot here than it seems. or my repro is rubbish…

I was 15 hours in when I encountered this…

Thanks for the repro - sanity checks are helpful! It turns out that I introduced a bug further up, which led to QoQ processing an empty query. The error message threw me - I would expect an empty query to return an empty array, which would have led to the solution.

Oddly, I can’t reproduce this on tryCF using your repro as a base. The complete (broken) method is below - the problem was the queryExecute inside the each() loop. Locally, with this code, it returns the error I posted above. If I move the queryExecute outside the loop where it belongs, it works properly.

This is parsing an ugly quarterly spreadsheet that puts repeating data in columns instead of rows :confused:

  public any function parse( any sheet, string fileName ) {
    var dates       = [];
    var dataColumns = [ "column11", "column16", "column21" ]
    var out         = queryNew( "consultantID,date,model,accounts,aua", "integer,date,varchar,integer,float" );

    sheet.each( ( row, i ) => {
      if ( i == 1 ) {
        dataColumns.each( ( column ) => {
          dates.append( row[ column ] );
        } )
      } else if ( i >= 3 ) {
        dataColumns.each( ( column, j ) => {
          out.addRow( {
            consultantID : variables.consultantID,
            date         : dates[ j ],
            model        : row[ "column3" ],
            accounts     : 1,
            aua          : row[ column ]
          } );
        } )
      }

      out = queryExecute("
        SELECT
          consultantID,
          date,
          model,
          SUM( accounts ) AS accounts,
          SUM( aua ) AS aua
        FROM out
        GROUP BY consultantID, date, model",
        {},
        { dbtype : "query", returntype: "array" }
      )
    } )

    return out
  }

Did you try my suggestion? What happens when you add semicolons (;), for example, as I have done below:

public any function parse( any sheet, string fileName ) {
	var dates       = [];
	var dataColumns = [ "column11", "column16", "column21" ];
	var out         = queryNew( "consultantID,date,model,accounts,aua", "integer,date,varchar,integer,float" );

	sheet.each( ( row, i ) => {
		  if ( i == 1 ) {
			dataColumns.each( ( column ) => {
			  dates.append( row[ column ] );
			} );
		  } else if ( i >= 3 ) {
			dataColumns.each( ( column, j ) => {
			  out.addRow( {
				consultantID : variables.consultantID,
				date         : dates[ j ],
				model        : row[ "column3" ],
				accounts     : 1,
				aua          : row[ column ]
			  } );
			} );
		  }

		  out = queryExecute("
			SELECT
			  consultantID,
			  date,
			  model,
			  SUM( accounts ) AS accounts,
			  SUM( aua ) AS aua
			FROM out
			GROUP BY consultantID, date, model",
			{},
			{ dbtype : "query", returntype: "array" }
		  );
		} 
	);

	return out;
}