| Subject | Re: SQL or Array Rowset ? |
| From | Bruce Beacham <bbeacham@no-plm-lowman.co.uk> |
| Date | Mon, 5 Apr 2021 17:08:31 +0100 |
| Newsgroups | dbase.getting-started |
Hi This sounds like a case for a JOIN using an sql file to state the SELECT statement, itself quite possibly using an sql file to generate its source table - you can have quite a cascade but this sort of thing is amazingly quick in execution. What I have done for myself is create a short program that creates a text file (*.sql) on the fly, and the text file contains the sql statement that generates the required rowset, whether JOIN or source table, at each level. Thus the parameters of the particular run (like the database, or an account number, or a pair of dates) can be sent into the process for creating the sql file and be expressed as literals in the sql statement. Each temporary text file is made by one of a handful of permanent programs that produce the code for that place in the data structure I am building. Below is a pair of functions that contribute sql statements for an enquiry on a transactions history table. The second feeds the first - look for sqlsource04_ytd. (Watch the wrap) References to horses and stables in the comments are to another of my apps I pinched this from, to warn me of risks in such code if I try to get too clever. alias is a database alias in the BDE. DM is a datamodule on that database TotalByHeader and CurrentYearOnly are logicals. All I am aiming to do here is answer a few questions on how you build a cascading sql statement. Bruce ********************************** FUNCTION SQLList_Group_Accounts * This uses Groups as the collector of the accounts * in each group into sql statement @sqlsource01. * As set at the moment, it gathers into this sql statement both subordinate * sql statements as outer joins herein, not daisychained. * It adds in the AccName field from a subset of DM.Codes. * Parameter [1] alias is a string of the BDE alias that the tables are * to come from. * Parameters [3] Startdate and [4] EndDate are individually optional, * place-sensitive date types, * fed through to calls to SQLTotalLedgerTransactionsByUniqueAccount(). * They must be set as query.params before the query is activated. * EndDate must have been applied to the totalling of the LastYear_YTD field * in this year's Codes table in UpdateCodesComparatives() in prepdata.prg. parameter alias, DM, StartDate, EndDate, TotalByHeader, CurrentYearOnly local sqlsource03, sqlsource04_ytd, sqlsource04_month, sqlsource10 * This carries this year's figures as well as the Last Year and Previous Year figures. sqlsource03 = SQLCodesListForGroupTotals_IncludingThisYearValues(alias, (DM), StartDate, EndDate, TotalByHeader, CurrentYearOnly) /// Suppress these if done via SQLCodesListForGroupTotals_IncludingThisYearValues(). ///sqlsource04_month = SQLTotalLedgerTransactionsByUniqueAccount(alias, (DM), StartDate, EndDate, TotalByHeader) // below, itself generating an sql file on Ledger. ///sqlsource04_ytd = SQLTotalLedgerTransactionsByUniqueAccount(alias, (DM), false, EndDate, TotalByHeader) // below, itself generating an sql file on Ledger. * This provides the account names (or the nnn account header names). sqlsource10 = SQLCodeNames(alias, (DM), StartDate, EndDate, TotalByHeader) * This is the name of the file holding the sql statement. * It is either used as the @ filename in the dBL sql statement * or is a Local SQL View used in a SELECT FROM. local sqlfile, fl sqlfile = funique(_app.tempdirectory + "SQLList_Group_Accounts_?????.sql") local fl fl = new file() fl.create(sqlfile, "W") _app.tempfiles.add(sqlfile) // for deletion upon an orderly quit - see AppForm.onClose() fl.puts('/' + '*' + sqlfile + ' on ' + datetime() + '*' + '/') fl.puts('/' + '*generated by ' + program() + '() in ' + program(1) + '*' + '/') fl.puts('SELECT ') fl.puts('Groups.GrpLetter, ') // 1 fl.puts('Groups.GrpName, ') // 2 fl.puts('Groups.Profit, ') // 3 fl.puts('Groups.NotGroup, ') // 4 fl.puts('Codes.Accno, ') // 5 fl.puts('CodeNames.Accname, ') // 6 fl.puts('YTDLedger.TransValues as ThisYear_YTD_Value, ') // 7 fl.puts('YTDLedger.TransQuans as ThisYear_YTD_Quantity, ') // 8 fl.puts('MonthLedger.TransValues as ThisYear_Month_Value, ') // 7 fl.puts('MonthLedger.TransQuans as ThisYear_Month_Quantity, ') // 8 fl.puts('Codes.ThisYear_Month_Value, ') // 7 fl.puts('Codes.ThisYear_Month_Quantity, ') // 8 fl.puts('Codes.ThisYear_YTD_Value, ') // 7 fl.puts('Codes.ThisYear_YTD_Quantity, ') // 8 if CurrentYearOnly else fl.puts('Codes.LastYear_Year as LastYear_Year_Value, ') // 9 fl.puts('Codes.LastYear_YTD as LastYear_YTD_Value, ') // 10 fl.puts('Codes.Previous_Year as PreviousYear_Year_Value, ') // 11 endif fl.puts('CAST ("END" AS CHAR(3)) ') // for my own readability. * Extra info: * The following arithmetic works where there is a row coming in on each join, * as the source sqls cast their output as numeric in case there is a null * in the source or there is no data in a subordinate sql. * But it does not work where there is no incoming row (eg a rider * responsible for stabling is not on the Entries file as responsible for the fee, * or on CashList. //fl.puts('(Debtor.Entry_Fees + Debtor.Stabling_Fees - Debtor.Net_Received ) as Debtor, ') * This is the lead, the Groups table. fl.puts('FROM ":' + alias + ':' + DM.GetTablenameFromQueryname("Groups") + '.dbf" Groups ') * This carries the Last Year and Previous Year figure * from SQLCodesListForGroupTotals_IncludingThisYearValues(). fl.puts('FULL OUTER JOIN "' + sqlsource03 + '" Codes on Codes.GrpLetter = Groups.GrpLetter ') * These carry this year figures from SQLTotalLedgerTransactionsByUniqueAccount() * if they are not brought in through SQLCodesListForGroupTotals_IncludingThisYearValues(). //fl.puts('FULL OUTER JOIN "' + sqlsource04_ytd + '" YTDLedger on Codes.Accno = YTDLedger.Accno ') //fl.puts('FULL OUTER JOIN "' + sqlsource04_month + '" MonthLedger on Codes.Accno = MonthLedger.Accno ') * This brings in the AccName field from SQLCodeNames. * Uses the .0000 account to name a nnn TotalByHeader Accno. fl.puts('INNER JOIN "' + sqlsource10 + '" CodeNames on Codes.Accno = CodeNames.Accno ') fl.puts('ORDER BY Groups.GrpLetter, Codes.Accno ') fl.close() return (sqlfile) ********************************** FUNCTION SQLTotalLedgerTransactionsByUniqueAccount * This produces an sql file that generates a rowset that groups * the Transactions values on each account Code from Ledger. * That Code may be each account number, or * (if TotalByHeader = true) each header (a string nnn). * * It may be called by the Codes collector * SQLCodesListForGroupTotals_IncludingThisYearValues(), * or distinctly by the top-level Groups collector (SQLList_Group_Accounts()). * This sql produces one total per Code. * Generally it is run twice, once for the YTD (only EndDate * is given) and one for the Month (StartDate and EndDate * are both given). * Note that it purely uses the table generated by the sql file * from SQLLedgerTransactionsOnAccount(), which is transactions * on all accounts individually. The account number may be * the full number or (if TotalByHeader = true) each account's * header (a string nnn) in which case there are multiple rows * for any code. * That sql file is not used as a join to another sql instance of the Ledger. * The reason is that a group by cannot be done on an expression field * like a field which is CAST(), which is necessary when we total by * account header (nnn) rather than by a plain account (nnn.nnnn). * So SQLTotalLedgerTransactionsByUniqueAccount() does the grouping since it is fed * by a temp table that already has the CAST() abbreviation done * (if we are doing TotalByHeader). * Parameter [1] alias is a string of the BDE alias that the tables are * to come from. * Parameter [3], if given and a date, has to be set as * a query.params["StartDate"] value before activating the query. * Parameter [4], if given and a date, has to be set as * a query.params["EndDate"] value before activating the query. parameter alias, DM, StartDate, EndDate, TotalByHeader local sqlsource09 * This is a flat table of the transactions in each account * for the current year, either Month or YTD (depending on StartDate). sqlsource09 = SQLLedgerTransactionsOnAccount(alias, (DM), StartDate, EndDate, TotalByHeader) // below, itself generating an sql file on Ledger. * This is the name of the file holding the sql statement. * It is either used as the @ filename in the dBL sql statement * or is a Local SQL View used in a SELECT FROM. local sqlfile, fl sqlfile = funique(_app.tempdirectory + "SQLTotalLedgerTransactionsByUniqueAccount_?????.sql") fl = new file() fl.create(sqlfile, "W") _app.tempfiles.add(sqlfile) // for deletion upon an orderly quit - see AppForm.onClose() fl.puts('/' + '*' + sqlfile + ' on ' + datetime() + '*' + '/') fl.puts('/' + '*generated by ' + program() + '() in ' + program(1) + '*' + '/') fl.puts('SELECT ') fl.puts('Ledger.GrpLetter, ') * Accno is an Account Header (a string nnn) if TotalByHeader, * or a full numeric(8, 4) AccNo. 20.2.20 * If TotalByHeader, we group here by Account Header. 19.2.20 fl.puts('Ledger.Accno, ') fl.puts('CAST(sum(Ledger.n_value) as NUMERIC(11, 2)) as TransValues, ') // so not null, so allows arithmetic in the function calling this. fl.puts('CAST(sum(Ledger.quantity) as INTEGER) as TransQuans, ') // so not null, so allows arithmetic in the function calling this. fl.puts('CAST ("END" AS CHAR(3)) ') fl.puts('FROM "' + sqlsource09 + '" Ledger ') * The file coming in needs to be single-row per grouped account. * Have to use literal dates (so cannot requery() with different dates) * because parameters in an sql sub-statement give error: * "Parameter not set in query string". fl.puts('GROUP BY Ledger.GrpLetter, Ledger.Accno ') fl.puts('ORDER BY Ledger.GrpLetter, Ledger.Accno ') fl.puts('') fl.close() return (sqlfile) **************************************** On 05/04/2021 14:10, Robbie Nott wrote: > Hi Akshat > > Many thanks for the reply > > Attached sample tables > When the quote is completed the program goes through the QuoteItems and > builds an array of the unique part numbers and sums their quantities. > > Program then looks up the Accounts file with each PartNumber to get the > NoOnHand ( DB's and CR's ) - this resides in a procedure CalcNoOnHand() > > Balance is calculated - NoOnHand - NoRequired > > Results are sent out to the PackageInvTotals table which displays on the > form. > > PackageInvTotals table columns 1 to 3 will remain constant and I'd like > to leave them alone. > It's the last 2 columns in the output table - NoOnHand and Balance - > that I'm needing help with... > > Again, many thanks > Robbie > > | |