/*Here is a small utility that allows you count records in multiple tables using SQL or ABL . Change the scoped-define for your table or leave the scoped-define empty. Hope this helps*/
&SCOPED-DEFINE TBL1 customer
&SCOPED-DEFINE TBL2 employee
&SCOPED-DEFINE TBL3 billto
&SCOPED-DEFINE TBL4 order
&SCOPED-DEFINE TBL5 warehouse
&SCOPED-DEFINE TBL6 state
&SCOPED-DEFINE TBL7 supplier
&SCOPED-DEFINE TBL8 shipto
&SCOPED-DEFINE TBL9 orderline
&SCOPED-DEFINE TBL10
&SCOPED-DEFINE TBL11
DEFINE VARIABLE hq AS HANDLE NO-UNDO.
DEFINE VARIABLE hb AS HANDLE NO-UNDO.
DEFINE VARIABLE ix AS INTEGER NO-UNDO.
DEFINE VARIABLE cTmp AS CHARACTER NO-UNDO.
DEFINE VARIABLE iCnt AS INTEGER NO-UNDO.
DEFINE VARIABLE iTotIdx AS INTEGER NO-UNDO.
DEFINE VARIABLE iTime AS INTEGER NO-UNDO.
DEFINE VARIABLE iTmp AS INTEGER NO-UNDO.
DEFINE VARIABLE cc AS CHARACTER NO-UNDO.
DEFINE FRAME fx.
DEFINE VARIABLE cTblList AS CHARACTER NO-UNDO.
DEFINE VARIABLE iCntArray AS INTEGER EXTENT 256 NO-UNDO.
DEFINE VARIABLE iTimeArray AS INTEGER EXTENT 256 NO-UNDO.
DEFINE VARIABLE lSQL AS LOGICAL NO-UNDO.
FUNCTION cLine RETURNS CHARACTER PRIVATE
(INPUT i AS INTEGER):
RETURN SUBSTITUTE("&1 &2 &3",
IF i NE iTotIdx THEN STRING(ENTRY(i,cTblList),"x(15)")
ELSE " TOTALS",
IF i NE iTotIdx THEN STRING(iCntArray
,"zz,zzz,zz9")
ELSE STRING(iCntArray,"zzz,zzz,zz9"),
STRING(iTimeArray,"hh:mm:ss")).
END FUNCTION. /*cLine*/
FUNCTION getEtime RETURN INTEGER PRIVATE
(INPUT iStartTm AS INTEGER):
RETURN INTEGER((ETIME - iStartTm) / 1000).
END FUNCTION. /*getEtime*/
CREATE WIDGET-POOL "XXX".
ASSIGN cTblList = SUBSTITUTE("TOTALS&1&2&3&4&5&6&7&8&9",
&IF "{&TBL1}" <> ""
&THEN ",{&TBL1}" &ELSE "" &ENDIF,
&IF "{&TBL2}" <> ""
&THEN ",{&TBL2}" &ELSE "" &ENDIF,
&IF "{&TBL3}" <> ""
&THEN ",{&TBL3}" &ELSE "" &ENDIF,
&IF "{&TBL4}" <> ""
&THEN ",{&TBL4}" &ELSE "" &ENDIF,
&IF "{&TBL5}" <> ""
&THEN ",{&TBL5}" &ELSE "" &ENDIF,
&IF "{&TBL6}" <> ""
&THEN ",{&TBL6}" &ELSE "" &ENDIF,
&IF "{&TBL7}" <> ""
&THEN ",{&TBL7}" &ELSE "" &ENDIF,
&IF "{&TBL8}" <> ""
&THEN ",{&TBL8}" &ELSE "" &ENDIF,
&IF "{&TBL9}" <> ""
&THEN ",{&TBL9}" &ELSE "" &ENDIF
)
cTblList = cTblList + SUBSTITUTE("&1&2",
&IF "{&TBL10}" <> ""
&THEN ",{&TBL10}" &ELSE "" &ENDIF,
&IF "{&TBL11}" <> ""
&THEN ",{&TBL11}" &ELSE "" &ENDIF
)
iTotIdx = LOOKUP("TOTALS",cTblList) /*set totals index*/
.
MAIN:
DO ON ERROR UNDO,RETRY
ON QUIT UNDO,LEAVE
ON STOP UNDO,LEAVE:
IF RETRY THEN DO:
MESSAGE SUBSTITUTE("ERROR has occurred:&1",
RETURN-VALUE)
VIEW-AS ALERT-BOX.
LEAVE.
END. /*retry*/
UPDATE lSQL FORMAT "SQL/ABL" LABEL "Use SQL or ABL?"
WITH FRAME fupd1 SIDE-LABELS CENTERED ROW 10 WIDTH 40
TITLE " COUNT RECORDS IN TABLES ".
DO ix = 1 TO NUM-ENTRIES(cTblList):
IF ix EQ iTotIdx THEN NEXT. /*Skip totals*/
ASSIGN
iCnt = 0
iTmp = ETIME
.
PAUSE 0.
MESSAGE SUBSTITUTE(" Counting records in table&1)....",
ENTRY(ix,cTblList)).
PAUSE 1.
IF NOT lSQL THEN DO: /*ABL*/
CREATE QUERY hq.
CREATE BUFFER hb FOR TABLE ENTRY(ix,cTblList).
cTmp = SUBSTITUTE("preselect each &1 fields() no-lock",
ENTRY(ix,cTblList)).
IF hq:SET-BUFFERS(hb)
AND hq:QUERY-PREPARE(cTmp)
AND hq:QUERY-OPEN()
AND hq:NUM-RESULTS GT 0
THEN DO:
iCnt = hq:NUM-RESULTS .
END.
IF VALID-HANDLE(hq) THEN DO:
hq:QUERY-CLOSE().
DELETE OBJECT hb.
hb = ?.
END.
END. /*not sql*/
ELSE DO: /*SQL*/
CASE ENTRY(ix,cTblList):
&IF "{&TBL1}" <> "" &THEN
WHEN "{&TBL1}" THEN
SELECT COUNT (*) INTO iCnt FROM {&TBL1}.
&ENDIF
&IF "{&TBL2}" <> "" &THEN
WHEN "{&TBL2}" THEN
SELECT COUNT (*) INTO iCnt FROM {&TBL2}.
&ENDIF
&IF "{&TBL3}" <> "" &THEN
WHEN "{&TBL3}" THEN
SELECT COUNT (*) INTO iCnt FROM {&TBL3}.
&ENDIF
&IF "{&TBL4}" <> "" &THEN
WHEN "{&TBL4}" THEN
SELECT COUNT (*) INTO iCnt FROM {&TBL4}.
&ENDIF
&IF "{&TBL5}" <> "" &THEN
WHEN "{&TBL5}" THEN
SELECT COUNT (*) INTO iCnt FROM {&TBL5}.
&ENDIF
&IF "{&TBL6}" <> "" &THEN
WHEN "{&TBL6}" THEN
SELECT COUNT (*) INTO iCnt FROM {&TBL6}.
&ENDIF
&IF "{&TBL7}" <> "" &THEN
WHEN "{&TBL7}" THEN
SELECT COUNT (*) INTO iCnt FROM {&TBL7}.
&ENDIF
&IF "{&TBL8}" <> "" &THEN
WHEN "{&TBL8}" THEN
SELECT COUNT (*) INTO iCnt FROM {&TBL8}.
&ENDIF
&IF "{&TBL9}" <> "" &THEN
WHEN "{&TBL9}" THEN
SELECT COUNT (*) INTO iCnt FROM {&TBL9}.
&ENDIF
&IF "{&TBL10}" <> "" &THEN
WHEN "{&TBL10}" THEN
SELECT COUNT (*) INTO iCnt FROM {&TBL10}.
&ENDIF
&IF "{&TBL11}" <> "" &THEN
WHEN "{&TBL11}" THEN
SELECT COUNT (*) INTO iCnt FROM {&TBL11}.
&ENDIF
END CASE.
END. /*SQL*/
ASSIGN iCntArray[ix] = iCntArray[ix] + iCnt
iCntArray[iTotIdx] = iCntArray[iTotIdx] + iCnt
iTime = getEtime(iTmp)
iTimeArray[ix] = iTimeArray[ix] + iTime
iTimeArray[iTotIdx] = iTimeArray[iTotIdx] + iTime
.
PAUSE 0.
MESSAGE SUBSTITUTE("&1:&2 Time(secs):&3", ENTRY(ix,cTblList),
STRING(iCnt,">>,>>>,>>9"),
STRING(iTime, ">>,>>9.99")).
/*pause.*/
END. /*do ix*/
HIDE ALL NO-PAUSE.
FORM
cc FORMAT "X(45)" LABEL " TABLE COUNT TIME"
WITH 15 DOWN FRAME fx
TITLE SUBSTITUTE(" COUNT USING &1 ", STRING(lSQL,"SQL/ABL"))
.
DO ix = 1 TO NUM-ENTRIES(cTblList):
IF ix EQ iTotIdx THEN NEXT. /*display totals line last*/
PAUSE 0.
DISPLAY cLine(ix) @ cc
WITH FRAME fx.
DOWN 1 WITH DOWN FRAME fx.
END.
PAUSE 0.
DISPLAY cLine(iTotIdx) @ cc
WITH FRAME fx.
/*pause.*/
END. /*main*/
DELETE WIDGET-POOL "XXX".
RETURN.