Multiple Queries populating QSqlQueryModel

[hollyberry]

I realize my problem would be solved by creating a view in the database that I could set to my QSqlQueryModel. Unfortunately, I do not have the permissions to create a view. I also realize my problem could be solved by simply creating a longer SELECT query; unfortunately, the database (disgustingly inefficient design) cannot handle the length of query I need to execute.

My solution is to create multiple queries that will run one at a time. Then I would like to use QSqlQueryModel to put all the information together and in a QTableView. Is this possible? Can I use more than one query in the QSqlQueryModel?

Here is the code for one query that I know works. I just have 8 more queries like it that need to be in the same table:

QSqlQueryModel *model = new QSqlQueryModel;
   model->setData ("SELECT props.number, props.sample,  props.top, props.bottom, AVG(ds.magn), MEDIAN(TO_NUMBER(ds.magn)) FROM ds JOIN props ON props.number  = ds.number WHERE ds.cd BETWEEN props.top AND props.bottom GROUP BY props.number, props.cnumber, props.sample, props.top, props.bottom HAVING props.number = 11 ORDER BY TO_NUMBER(props.cnumber) desc, TO_NUMBER(props.sample) desc");
   if (model->lastError().isValid())
       qDebug() << model->lastError();
   else qDebug() << "success!";
   model->setHeaderData(0, Qt::Horizontal, QObject::tr("CNumber"));
   model->setHeaderData(1, Qt::Horizontal, QObject::tr("Sample"));
   model->setHeaderData(2, Qt::Horizontal, QObject::tr("Top"));
   model->setHeaderData(3, Qt::Horizontal, QObject::tr("Bottom"));
   model->setHeaderData(4, Qt::Horizontal, QObject::tr("Mean Magnectic"));
   model->setHeaderData(5, Qt::Horizontal, QObject::tr("Median Magnectic"));
 
QTableView *view = new QTableView;
   view->setModel(model);
   view->show();

QSqlQueryModel *model = new QSqlQueryModel;
   model->setData ("SELECT props.number, props.sample,  props.top, props.bottom, AVG(ds.magn), MEDIAN(TO_NUMBER(ds.magn)) FROM ds JOIN props ON props.number  = ds.number WHERE ds.cd BETWEEN props.top AND props.bottom GROUP BY props.number, props.cnumber, props.sample, props.top, props.bottom HAVING props.number = 11 ORDER BY TO_NUMBER(props.cnumber) desc, TO_NUMBER(props.sample) desc");
   if (model->lastError().isValid())
       qDebug() << model->lastError();
   else qDebug() << "success!";
   model->setHeaderData(0, Qt::Horizontal, QObject::tr("CNumber"));
   model->setHeaderData(1, Qt::Horizontal, QObject::tr("Sample"));
   model->setHeaderData(2, Qt::Horizontal, QObject::tr("Top"));
   model->setHeaderData(3, Qt::Horizontal, QObject::tr("Bottom"));
   model->setHeaderData(4, Qt::Horizontal, QObject::tr("Mean Magnectic"));
   model->setHeaderData(5, Qt::Horizontal, QObject::tr("Median Magnectic"));

QTableView *view = new QTableView;
   view->setModel(model);
   view->show();

To copy to clipboard, switch view to plain text mode 

Thank you for any assistance!

[ChrisW67]

Can I use more than one query in the QSqlQueryModel?

No. One SELECT query returning one result set. At this point your query becomes one of pure SQL.

the "HAVING props.number = 11" clause should be in the "WHERE". "Having" is for placing conditions on the results of aggregate functions after grouping.

When you say you have "8 more queries like it that need to be in the same table" do you mean that these other queries output the same columns and their results just need to be appended? If so, a UNION or UNION ALL query is another approach.

Are all the queries varying only the props.number of interest? If so then "WHERE props.number in (11, 23, 45, 89 ...)" might help.