help need to find number of records on a query.

[Hobby]

Hai,

I need to find the number of records in a buffer which satisfies a given where condition.

for example :

i have a query
h_query:query-prepare("for each compinv").

i need to find the number of records.. There is a function called h_query:num-results.but this needs the query to be opened. and if there are some 10K records ,then its taking time..
Is there any faster way?

 

[MaximMonin]

h_query:num-results doesnt work the way you think. It returns number of records in current buffer returned by DB server, but it is not all records if table is big.

select count (*) from compinv - i think it is fastest way to get records number. But it still works kinda slowly.

 

[RealHeavyDude]

The number of records in a table or a query is an information Progress does NOT provide. Never had - and I doubt that they ever will.

The only chance to get that information is to implement your own logic that would provide such information. In almost all cases this means to run some sort of query to count the records.

SELECT COUNT(*) FROM table is SQL89 syntax which is supported by the 4GL compiler. But there is no easy way to process whatever result a SQL89 statement would give with 4GL means. I once had a working example to fetch the result from a SELECT COUNT(*) into an INTEGER variable in using something like DECLARE CURSOR - but I can't seem to find it. The question is whether you really want to mix 4GL and SQL89 syntax in your 4GL code.

Regards, RealHeavyDude.

 

[UncleNel]

One way to find the number of records in a query is to change your query-prepare from "for each" to "preselect each" and after you open the query use the method h_query:num-results to obtain the number of rows. This is not a fast method of obtaining the count, but it works. "Preselect" does what in implies, fetches all the records. If you only want a rows returned count, I would suggest that you use the FIELDS() options in the query-prepare statement with no fields listed.

 

[RealHeavyDude]

Just to let you know - for anyone who is interrested

This

DEFINE VARIABLE iCount AS INTEGER NO-UNDO.

SELECT COUNT(*) INTO iCount FROM myTable.

MESSAGE iCount
VIEW-AS ALERT-BOX INFO BUTTONS OK.

will compile and work in OpenEdge 10 although it's SQL89 syntax ...


HTH, RealHeavyDude.

 

[UncleNel]

/*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.

 

[tamhas]

dbanalysis is a whole lot simpler.