Hi,
Can you help me in calculating the no. of records in query for two different tables and then need to export it in excel ?
DEFINE TEMP-TABLE ttSubTotals FIELD poltype AS CHARACTER FIELD subtotal AS DECIMAL FIELD recuwm AS INTEGER FIELD recclm AS INTEGER Index ttix IS UNIQUE PRIMARY poltype. DEF VAR subtotal AS DECIMAL. DEF VAR subrecuwm AS INTEGER. DEF VAR subrecclm AS INTEGER. DEF STREAM data-export. OUTPUT STREAM data-export TO VALUE("c:\temp\output.csv"). PUT STREAM data-export UNFORMATTED "Class, Amount, No. of records in uwm100, No.of records in clm100" SKIP. FOR EACH uwm100 WHERE uwm100.comdat > 12/01/14 AND uwm100.comdat < 12/01/2015 NO-LOCK BREAK BY poltype: FOR EACH clm100 WHERE clm100.policy = uwm100.policy AND clm100.rencnt = uwm100.rencnt AND clm100.endcnt = uwm100.endcnt NO-LOCK: ACCUMULATE clm100.policy (COUNT). END. ACCUMULATE uwm100.amount (COUNT SUM BY uwm100.poltype). subtotal = (ACCUM SUM BY uwm100.poltype uwm100.amount). subrecuwm = (ACCUM COUNT BY uwm100.poltype uwm100.amount). subrecclm = subrecclm + (ACCUM COUNT clm100.policy). IF LAST-OF(poltype) THEN DO: CREATE ttSubTotals. ASSIGN ttSubTotals.poltype = uwm100.poltype ttSubTotals.subtotal = subtotal ttSubTotals.recuwm = subrecuwm ttSubTotals.recclm = subrecclm . subrecclm = 0. EXPORT STREAM data-export DELIMITER "," ttSubtotals. END. END.
not exactly sure what you want to do but...
if the table is millions or even billions of records long you can use the slib/utils/fast-rec-cnt.p in the standard libraries project at the oehive.org
the utility uses proutil idxblockreport to calc the records. note that this is not an exact number but it's pretty close.
i timed it at roughly 1 second per 500,000,000 records.
<code>
define var i as int no-undo.
run slib/utils/fast-rec-cnt.p( "<physical dbname>", "table", output i).
message i.
</code>
if it's a small number of records and you're already running over them and exporting them to excel then there's no problem counting them.
to create an excel file you can either use a plain csv file or you might want to look at the free docxfactory project at docxfactory.com
hth
> i timed it at roughly 1 second per 500,000,000 records.
If I recall correctly Excel can't open a file with more than a million rows. FOR EACH query can stop counting the records after the specified limit.
you're right. you couldn't export it if it was more than roughly a million.
btw i wrote the fast-rec-cnt.p after reading your post about proutil idxblockreport and howto use it for fast record counts a few years ago :) thank you! :)
You may have more options if you are willing to use SQL92 instead of ABL. The SQL technology in general (and OE-SQL92 in particular) is great for most reporting-related purposes.
For example, if you have an index on the table than then the "SELECT count(*)" should be resolved without reading the base table data. In contrast, I don't think normal ABL has any mechanism at all for reading only index data without getting down into the base table's data.
> I am assuming Windows platform since Excel doesn’t run on others...
you may want or need to generate the file on the server running unix/linux and there's openoffice/libreoffice and others...
Hi All,
Thanks for your responses.
Am running in the windows only and each table has around 600 records only.
My query looks like this,
output to "C:\Vignesh_NTT\Dfsa.csv".
export delimiter "," "Policy no" "Claim" "Renewal cnt" "End cnt" "Gross prem" "class" "comdat".
for each uwm100 where comdat >= 12/29/14 and comdat <= 12/28/15 and releas = yes
no-lock by cntry:
For each clm100 where clm100.policy = uwm100.policy and
clm100.rencnt = uwm100.rencnt and
clm100.endcnt = uwm100.endcnt no-lock:
Now i want to calculate the no.of records in uwm100 and clm100 table and need to export it.
Something like this?
DEF VAR i AS INTEGER. DEF VAR ii AS INTEGER. DEF STREAM data-export. OUTPUT STREAM data-export TO VALUE("c:\temp\output.csv"). PUT STREAM data-export UNFORMATTED "Policy no, Claim, Renewal cnt, End cnt, Gross prem, class, comdat" SKIP. FOR EACH uwm100 WHERE uwm100.comdat > 12/01/14 AND uwm100.comdat < 12/01/2015 NO-LOCK BY cntry: ACCUMULATE uwm100.policy (COUNT). i = (ACCUM COUNT uwm100.policy). EXPORT STREAM data-export DELIMITER "," uwm100. FOR EACH clm100 WHERE clm100.policy = uwm100.policy AND clm100.rencnt = uwm100.rencnt AND clm100.endcnt = uwm100.endcnt NO-LOCK: ACCUMULATE clm100.policy (COUNT). EXPORT STREAM data-export DELIMITER "," clm100. END. ii = ii + (ACCUM COUNT clm100.policy). END. OUTPUT STREAM data-export CLOSE. MESSAGE i SKIP(1) ii VIEW-AS ALERT-BOX.
Thank you its very helpful.
In the same query if i need to calculate for different types,suppose a table has different policy types i need to find the sum of amounts of that type and also the no. of records in that type,
something like this,
for each uwm100 where trndat >= 12/29/14 and trndat <= 12/28/15 and releas = yes
no-lock break by poltyp:
if first-of(poltyp) then do:
assign
a = 0
i = 0
.
end.
if poltyp = "FIA" then
assign
a = a + uwm100.prem_t
i = i + 1.
else if poltyp = "FFF" then
assign
a = a + uwm100.prem_t
i = i + 1.
else if poltyp = "ECA" then
assign
a = a + uwm100.prem_t
i = i + 1.
else if poltyp = "EMB" then
assign
a = a + uwm100.prem_t
i = i + 1.
else if poltyp = "CSV" then
assign
a = a + uwm100.prem_t
i = i + 1.
I need to do the same for two tables and export the variable a & i.
can you please help on this.
DEFINE TEMP-TABLE ttSubTotals FIELD poltype AS CHARACTER FIELD subtotal AS DECIMAL Index ttix IS UNIQUE PRIMARY poltype. DEF VAR subtotal AS DECIMAL. FOR EACH uwm100 WHERE uwm100.comdat > 12/01/14 AND uwm100.comdat < 12/01/2015 NO-LOCK BREAK BY poltype: FOR EACH clm100 WHERE clm100.policy = uwm100.policy AND clm100.rencnt = uwm100.rencnt AND clm100.endcnt = uwm100.endcnt NO-LOCK: ACCUMULATE clm100.amount (COUNT SUM). END. ACCUMULATE uwm100.amount (COUNT SUM BY uwm100.poltype). subtotal = (ACCUM SUM BY uwm100.poltype uwm100.amount). IF LAST-OF(poltype) THEN DO: CREATE ttSubTotals. ASSIGN ttSubTotals.poltype = uwm100.poltype ttSubTotals.subtotal = subtotal . DISPLAY ttSubTotals. END. END. MESSAGE "Records in uwm100 = " + STRING((ACCUM COUNT uwm100.amount)) SKIP(1) "Records in clm100 = " + STRING((ACCUM COUNT clm100.amount)) SKIP(1) VIEW-AS ALERT-BOX.
DEFINE VARIABLE iTotalRec AS INTEGER NO-UNDO. DEFINE VARIABLE iTotalRecFiltered AS INTEGER NO-UNDO. FOR EACH uwm100 NO-LOCK WHERE uwm100.trndat >= 12/29/14 AND uwm100.trndat <= 12/28/15 AND uwm100.releas = yes BREAK BY uwm100.poltyp: ACCUMULATE uwm100.prem_t (COUNT BY uwm100.poltyp). /* it will only get the accumulated count from records that match the criteria */ IF LAST-OF(uwm100.poltyp) AND LOOKUP(uwm100.poltyp, 'FIA,FFF,ECA,EMB,CSV') > 0 THEN iTotalRecFiltered = iTotalRecFiltered + (ACCUM COUNT BY uwm100.poltyp uwm100.prem_t). END. /* gets the total number of records from the whole table */ iTotalRec = (ACCUM COUNT uwm100.prem_t).
Thank you...! The query looks good and able to get my results.
But one concern is in clm100 table you are accumulating by amount but i dont need amount wise in that table i just need to export the no.of records based on clm100.class as below last column.
And also in uwm100 table i also need to export the no.of records by poltype whereas we are exporting only the total no.of records in table.
The poltype and class are same in two fields.
Please export the fields dont need to display it.
My excel should be like this,
Class Amount (uwm100) No.of record in uwm100 No.of records in clm100
FIA 11033.12 100 50
FFF 52080.6 200 50
ECA 326.26 100 100
EMB 4166.45 50 100
CSV 1423.33 100 100
DEFINE TEMP-TABLE ttSubTotals FIELD poltype AS CHARACTER FIELD subtotal AS DECIMAL FIELD recuwm AS INTEGER FIELD recclm AS INTEGER Index ttix IS UNIQUE PRIMARY poltype. DEF VAR subtotal AS DECIMAL. DEF VAR subrecuwm AS INTEGER. DEF VAR subrecclm AS INTEGER. DEF STREAM data-export. OUTPUT STREAM data-export TO VALUE("c:\temp\output.csv"). PUT STREAM data-export UNFORMATTED "Class, Amount, No. of records in uwm100, No.of records in clm100" SKIP. FOR EACH uwm100 WHERE uwm100.comdat > 12/01/14 AND uwm100.comdat < 12/01/2015 NO-LOCK BREAK BY poltype: FOR EACH clm100 WHERE clm100.policy = uwm100.policy AND clm100.rencnt = uwm100.rencnt AND clm100.endcnt = uwm100.endcnt NO-LOCK: ACCUMULATE clm100.policy (COUNT). END. ACCUMULATE uwm100.amount (COUNT SUM BY uwm100.poltype). subtotal = (ACCUM SUM BY uwm100.poltype uwm100.amount). subrecuwm = (ACCUM COUNT BY uwm100.poltype uwm100.amount). subrecclm = subrecclm + (ACCUM COUNT clm100.policy). IF LAST-OF(poltype) THEN DO: CREATE ttSubTotals. ASSIGN ttSubTotals.poltype = uwm100.poltype ttSubTotals.subtotal = subtotal ttSubTotals.recuwm = subrecuwm ttSubTotals.recclm = subrecclm . subrecclm = 0. EXPORT STREAM data-export DELIMITER "," ttSubtotals. END. END.
Many Thanks!! [:)]
It fetches exactly what i need.
And also thanks for your quick responses.