Table 1 initial table fields: the salesman, the customer, in which, beginning balance, beginning balance not yet make out an invoice
Table 2 sales table fields: the salesman, customers, sales by month,
Table 3 table fields of make out an invoice: the salesman, the customer, by month, make out an invoice amount
Table 4 collection table fields: the salesman, the customer, in which, payment amount
Ask for subordinate in check, according to the specific results are as follows
Salesman sum (opening balance) sum (sales) sum (payment amount) ending balance of the sum (the beginning balance not yet make out an invoice) sum (c.) final balance not yet make out an invoice


The ending balance and ending balance of the final balance not yet make out an invoice is calculated field=beginning balance + sales - payment amount
The final balance not yet make out an invoice=beginning balance not yet make out an invoice + sales - make out an invoice amount

Table 4 each record all cannot little

How do you write SQL statement, thank you!
Late at night for help!

CodePudding user response:

 
In the select salesman, belongs to, the sum (opening balance) as the beginning balance, the sum (sales) as sales, the sum (payment amount) as payment amount, 
The sum (opening balance) + sum (sales) + sum (payment amount) as the final balance, 
The sum (the beginning balance not yet make out an invoice) as the beginning balance of make out an invoice, the sum (c.) as make out an invoice amount, 
The sum (the beginning balance not yet make out an invoice) + sum (sales) - the sum (c.) as the final balance not yet make out an invoice 
The from 
(
In the select salesman, belong to the beginning balance, 0 as sales, 0 as payment amount, initial balance not yet make out an invoice, 0 as make out an invoice amount, 0 as the final balance not yet make out an invoice 
From the beginning table 
Union all 
Month, select the salesman, belong to zero as the beginning balance, sales, 0 as payment amount, 0 as the beginning balance not yet make out an invoice, 0 as make out an invoice amount, 0 as the final balance not yet make out an invoice 
The from sales table 
Union all 
Month, select the salesman, belong to zero as the beginning balance, 0 as sales, 0 as payment amount, 0 as the beginning balance not yet make out an invoice, make out an invoice amount, 0 as the final balance not yet make out an invoice 
From table of make out an invoice 
Union all 
Month, select the salesman, belong to zero as the beginning balance, 0 as sales, payment amount, 0 as the beginning balance of make out an invoice, 0 as make out an invoice amount, 0 as the final balance not yet make out an invoice 
The from of table 
Org) 
Group by a solicitor, belongs in 

CodePudding user response:

The sum (opening balance) + sum (sales) + sum (payment amount) as the final balance,

Should be

The sum (opening balance) + sum (sales) - the sum (payment amount) as the final balance,

I write the wrong

CodePudding user response:

SQL. Add (' select ywy, sum (qcye) as qc, the sum (ysje) as xsje, sum (je) as skje, '+
'the sum (qcye) + sum (ysje) - the sum (je) as qmye,' +
'the sum (qcfpje) as QCFP, sum (JSHJ) as fpje,' +
'the sum (qcfpje) + sum (ysje) - the sum (JSHJ) as qmfpye' +
'from' +
'(' +
'the select ywy, qc, 0 as xsje, 0 as skje, 0 as qmye, QCFP, 0 as fpje, 0 as qmfpye' +
'the from yskqcye' +
'union all +
'the select ywy, 0 as qc, xsje, 0 as skje, 0 as qmye, QCFP, 0 as fpje, 0 as qmfpye' +
'from an XSB' +
'union all +
'the select ywy, 0 as qc, 0 as xsje, skje, 0 as qmye, QCFP, 0 as fpje, 0 as qmfpye' +
'the from SKB' +
') org +
'where CPLX="' + comcplx. Text + ' ' 'and ssyf=' "+ comssyf. Text + +
' ' ' ''group by ywy');

Compile error after
Invalid use of keyword
Token: select
The Line Numbe: 1

CodePudding user response:

Invalid use keywords, should be SQL statements to write wrong, look not to come out, you test the

SQL. Add in front of what? . Check the SQL text in the 'select ywy, sum (qcye) as qc, the sum (ysje) as xsje, sum (je) as skje,' what's in front of the no, you don't have clear,

SQL statement is a good idea to run successfully in SQL Studio and then transferred into the Delphi
You can first test in the middle that
The select ywy, qc, 0 as xsje, 0 as skje, 0 as qmye, QCFP, 0 as fpje, 0 as qmfpye
The from yskqcye
Union all
The select ywy, 0 as qc, xsje, 0 as skje, 0 as qmye, QCFP, 0 as fpje, 0 as qmfpye
From an XSB
Union all
The select ywy, 0 as qc, 0 as xsje, skje, 0 as qmye, QCFP, 0 as fpje, 0 as qmfpye
The from SKB


Also do not recommend 'where CPLX="' + comcplx. Text + ' ' 'and ssyf=' "+ comssyf. Text +" '"' this kind of writing, with the Parameters

CodePudding user response:

Comprehensive summary data, often through SQL cannot problem, need to loop through the data set, data statistics,

CodePudding user response:

Forgot to say, what I use is early padradox database
Cannot find a reason

Procedure TForm1.com CplxChange (Sender: TObject);
The begin
If comHzfs. Text='by solicitor collect then
The begin
With the data. XSHZ do
The begin
The close;
Sql. The Clear;
SQL. Add (' select ywy, sum (qcye) as qc, the sum (ysje) as xsje, sum (je) as skje, '+
'the sum (qcye) + sum (ysje) - the sum (je) as qmye,' +
'the sum (qcfpje) as QCFP, sum (JSHJ) as fpje,' +
'the sum (qcfpje) + sum (ysje) - the sum (JSHJ) as qmfpye' +
'the from (' +
'the select ywy, qc, 0 as xsje, 0 as skje, 0 as qmye, QCFP, 0 as fpje, 0 as qmfpye' +
'the from yskqcye' +
'union all +
'the select ywy, 0 as qc, xsje, 0 as skje, 0 as qmye, 0 as QCFP, 0 as fpje, 0 as qmfpye' +
'from an XSB' +
'union all +
'the select ywy, 0 as qc, 0 as xsje, skje, 0 as qmye, 0 as QCFP, 0 as fpje, 0 as qmfpye' +
'the from SKB' +
'union all +
'the select ywy, 0 as qc, 0 as xsje, 0 as skje, 0 as qmye, 0 as QCFP, fpje, 0 as qmfpye' +
'the from XXB' +
') org +
'where CPLX=: CPLX and ssyf=: ssyf' +
'group by ywy');
ParamByName (' CPLX). AsString:=comcplx. Text;
ParamByName (' ssyf). AsString:=comssyf. Text;
The open;
end;
end;

end.

CodePudding user response:

I tried to use the SQL syntax error is

Select the from (select from union all select the from) T
Don't paradox database the same?

CodePudding user response:

If the Paradox does not support the select the from (select from union all select the from) T
That you put in the middle that create a temporary table