Two queries in the same class

(0) Share

Report

Posted on by mimi51340

Hello,

I would like create in the same interface, two queries.

Because I need to extract some information with different filters from two tables (CustTrans and VendTrans) in the sames csv file. But there is no link between these two tables for my extraction.

Is it possible to do this ? Do I need to create two queries in the same class ?

Thanks for your help.

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Martin Dráb 239,392 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Sure, where do you see a problem?

Maybe you could also use an union query, if you want to extract the same fields from both tables. But I guess that using two separate queries will be easier and more flexible.

Was this reply helpful? Yes No
mimi51340 50 on at

Like (0)

Report
Copy link

Link copied!
For the moment I created one query only. But I get this error:

Below the method initParamDefault:

public void initParmDefault() { super(); queryCustTrans = new Query(); qbdsCustTrans = queryCustTrans.addDataSource(tableNum(CustTrans)); qbdsCustTrans.addSelectionField(fieldNum(CustTrans,createdDateTime),SelectionField::Count); qbdsCustTrans.addSelectionField(fieldNum(CustTrans,Voucher),SelectionField::Count); qbds2 = qbdsCustTrans.addDataSource(tableNum(tri_NettingCorrelationAR)); qbds2.joinMode(JoinMode::InnerJoin); qbds2.relations(false); qbds2.addLink(fieldNum(CustTrans, AccountNum),fieldNum(tri_NettingCorrelationAR, TRI_CustAccount)); queryRun = new QueryRun(queryCustTrans); }

And the content of my method to extract data :

void ExportProcess(str _fileName) { SysOperationProgress progression = new SysOperationProgress(); str InvoiceId; ; CodeAccessPermission::revertAssert(); permission= new FileIoPermission(_fileName,#io_write); permission.assert(); commaIO = new AsciiIO(_fileName, #io_write); if (!commaIO) { throw error("@SYS104602"); } if(commaIo) { switch(delimiter) { case FileDelimiter::Comma : commaIo.outFieldDelimiter(#delimiterComma); break; case FileDelimiter::SemiColumn : commaIo.outFieldDelimiter(#delimiterSemiColumn); break; case FileDelimiter::Space : commaIo.outFieldDelimiter(#delimiterSpace); break; case FileDelimiter::Tabulation : commaIo.outFieldDelimiter(#delimiterTab); break; case Filedelimiter::Pipe : commaIo.outFieldDelimiter(#delimiterPipe); break; } } if(checkWriteHeader) { this.WriteHeader(); } while (queryRun.next()) { custTrans = queryRun.get(tablenum(CustTrans)); tri_NettingCorrelationAR = queryRun.get(tablenum(TRI_NettingCorrelationAR)); InvoiceId = ""; if(custTrans.Invoice == "") InvoiceId = custTrans.Voucher; else InvoiceId = custTrans.Invoice; line = [ strLfix("INV_AR",10), strLfix("",1), strLfix(InvoiceId,32), strLfix(tri_NettingCorrelationAR.TRI_HFMCode,25), strLfix("FR026",25), strLfix(custTrans.CurrencyCode,3), strLfix(num2str(custTrans.AmountCur,14,2,DecimalSeparator::Dot,ThousandSeparator::None),14), strLfix(date2str(custTrans.TransDate,321,dateday::Digits2,dateseparator::None,datemonth::Digits2,dateseparator::None,dateyear::Digits4,dateflags::None),8), strLfix(date2str(custTrans.DueDate,321,dateday::Digits2,dateseparator::None,datemonth::Digits2,dateseparator::None,dateyear::Digits4,dateflags::None),8), strLfix("",255), strLfix("",30), strLfix("",255), strLfix("",100), strLfix("",100), strLfix("",100), strLfix("",100), strLfix("",100), strLfix("",100), strLfix("",100), strLfix("",8) ]; //On écrit la ligne dans le fichier commaIO.writeExp(line); //On incrément le nombre de lignes écrites i2 ; progression.setCaption(this.caption()); progression.setAnimation(#AviUpdate); progression.setText(strfmt("@SYS73233" " : %1", i2)); } }

Below the main method :

static void main(args _args)
{
TRI_ExportCsvTxtNettingQuery TRI_ExportCsvTxtNettingQuery = new TRI_ExportCsvTxtNettingQuery();

if (TRI_ExportCsvTxtNettingQuery.prompt())
TRI_ExportCsvTxtNettingQuery.run();
}

I do not understand why I get this error.

Thanks for your help.

Was this reply helpful? Yes No
Martin Dráb 239,392 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
If I understand it correctlym, we've just changed the topic. You're seem to be saying that this code:

Query query = new Query(); QueryBuildDataSource qbdsCustTrans; QueryBuildDataSource qbdsCorrelation; QueryRun queryRun; QueryBuildDataSource qbdsCustTrans = query.addDataSource(tableNum(CustTrans)); qbdsCustTrans.addSelectionField(fieldNum(CustTrans, createdDateTime), SelectionField::Count); qbdsCustTrans.addSelectionField(fieldNum(CustTrans, Voucher), SelectionField::Count); qbdsCorrelation = qbdsCustTrans.addDataSource(tableNum(tri_NettingCorrelationAR)); qbdsCorrelation.joinMode(JoinMode::InnerJoin); qbdsCorrelation.relations(false); qbdsCorrelation.addLink(fieldNum(CustTrans, AccountNum), fieldNum(tri_NettingCorrelationAR, TRI_CustAccount)); queryRun = new QueryRun(queryCustTrans); queryRun.next();

throws the error. But that can't completely be true, because the error message talks about a different query. For example, it contains filtes for Invoice field, but there is nothing like that in your code. Thererefore you didn't give us all information.

Anyway, I think that the problem is this line:

qbdsCustTrans.addSelectionField(fieldNum(CustTrans, createdDateTime), SelectionField::Count);

Result of Count function is a number, but the data type of this field is utcDateTime - it's not compatible.

Also, you didn't mention anything about extending another class, but clearly it's the case. What is the parent class?

Was this reply helpful? Yes No
mimi51340 50 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your help. I removed completly the "selectionField::count" because I think it is not necessary in this case.

Sorry I need to solve a last problem to can show you my issue with a second query.

Because now I get nothing in the "ExportProcess" method for these fields:

Whereas for the tri_NettingCorrelationAR table I get the data.

Thanks

Was this reply helpful? Yes No
Martin Dráb 239,392 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Please check if you have data in those fields in custTrans buffer. If not, we don't need to care about the code, because the cause is somewhere else.

If you aren't getting any data, make sure that your actually actually fetches these fields. It wasn't the case with the query you showed us before and it's not clear what query you're using at the moment.

Was this reply helpful? Yes No
mimi51340 50 on at

Like (0)

Report
Copy link

Link copied!
Now it works correctly. I added these fields in the query.

So my first was, is it possible to do something like that :

public void initParmDefault() { super(); /*query = new Query(); qbds1 = query.addDataSource(tablenum(LedgerTrans)); qbds1.addSelectionField(fieldNum(LedgerTrans,AccountNum),SelectionField::Count); qbds1.addSelectionField(fieldNum(LedgerTrans,Dimension),SelectionField::Count); //qbds1.addSelectionField(fieldNum(LedgerTrans,Dimension[2]),SelectionField::Count); //qbds1.addSelectionField(fieldNum(LedgerTrans,Dimension[3]),SelectionField::Count); qbds1.addSelectionField(fieldNum(LedgerTrans,AmountMst),SelectionField::Sum); qbds1.orderMode(OrderMode::GroupBy); qbds1.addGroupByField(fieldnum(LedgerTrans,AccountNum)); qbds1.addGroupByField(fieldnum(LedgerTrans,Dimension)); //qbds1.addGroupByField(fieldnum(LedgerTrans,Dimension[2])); //qbds1.addGroupByField(fieldnum(LedgerTrans,Dimension[3])); queryRun = new QueryRun(query);*/ queryCustTrans = new Query(); qbdsCustTrans = queryCustTrans.addDataSource(tableNum(CustTrans)); qbdsCustTrans.addSelectionField(fieldNum(CustTrans,createdDateTime)); qbdsCustTrans.addSelectionField(fieldNum(CustTrans,CurrencyCode)); qbdsCustTrans.addSelectionField(fieldNum(CustTrans,TransDate)); qbdsCustTrans.addSelectionField(fieldNum(CustTrans,DueDate)); qbdsCustTrans.addSelectionField(fieldNum(CustTrans,AmountCur)); qbdsCustTrans.addSelectionField(fieldNum(CustTrans,Voucher)); qbds2 = qbdsCustTrans.addDataSource(tableNum(tri_NettingCorrelationAR)); qbds2.addSelectionField(fieldNum(tri_NettingCorrelationAR,TRI_HFMCode)); qbds2.joinMode(JoinMode::InnerJoin); qbds2.relations(false); qbds2.addLink(fieldNum(CustTrans, AccountNum),fieldNum(tri_NettingCorrelationAR, TRI_CustAccount)); queryRun = new QueryRun(queryCustTrans); queryVendTrans = new Query(); qbdsVendTrans = queryVendTrans.addDataSource(tableNum(VendTrans)); qbdsVendTrans.addSelectionField(fieldNum(VendTrans,createdDateTime)); qbdsVendTrans.addSelectionField(fieldNum(VendTrans,CurrencyCode)); qbdsVendTrans.addSelectionField(fieldNum(VendTrans,DocumentDate)); qbdsVendTrans.addSelectionField(fieldNum(VendTrans,DueDate)); qbdsVendTrans.addSelectionField(fieldNum(VendTrans,AmountCur)); qbdsVendTrans.addSelectionField(fieldNum(VendTrans,Voucher)); qbds3 = qbdsCustTrans.addDataSource(tableNum(tri_NettingCorrelationAP)); qbds3.addSelectionField(fieldNum(tri_NettingCorrelationAP,TRI_HFMCode)); qbds3.joinMode(JoinMode::InnerJoin); qbds3.relations(false); qbds3.addLink(fieldNum(VendTrans, AccountNum),fieldNum(tri_NettingCorrelationAP, TRI_PurchAccount)); queryRunVendTrans = new QueryRun(queryVendTrans); }

In fact the aim is to add a new selection on the VendTrans table as below (with CustTrans table).

Thanks

Was this reply helpful? Yes No
Verified answer

Martin Dráb 239,392 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

As far as I can say, you aren't creating a new indepedent class. You're implementing a specialization of an existing class and overriding a few methods. How you can modify behavior of the parent class depends on how the parent class is implemented.

It looks that the parent class extends RunBase and inherits this filter-related logic from there.

The answer to a question whether multiple queries can be used in a single class is "yes". But if you ask whether the RunBase framework can generate dialog fields for query ranges and the "Select" button mutliple times (for multiple queries), then the asswer is "no".

Was this reply helpful? Yes No