How to get count of records without holding the data in webFOCUS

[Sivakumar Gopi 2]

Is there a way to get the count of the record directly from the database without holding the data inside webFOCUS. We are not allowed to use SQLPASSTHRU so please let me know if there some other way to do this.

Thank you

SG

WF 8207.28.13

[Patrick Huebgen]

Hi what exactly are you trying to build - where do you want this number to be displayed?

[Sivakumar Gopi 2]

I want to know how may records I am getting from the database. Based on that I will have to create the next table requests.

I am not trying to display the number of records on the report.

I am just trying if there is a way from webFOCUS to pass a SQL with SELECT COUNT(*) to the database.

[Brent Mason 2]

TABLE FILE <synonym>

COUNT *

END

[Patrick Huebgen]

You can use &RECORDS or &LINES after you created a report

TABLE FILE CARSUM SALES BY COUNTRY ON TABLE HOLD AS TESTEND-TYPE LINE &LINES RECORDS &RECORDS

or if you do only need the number not the data

-SET &HOW_MANY = 0;TABLE FILE CARSUM COMPUTE HOW_MANY/I11=CNT.SALES ;ON TABLE HOLD AS TEST FORMAT ALPHAEND-RUN-READFILE TEST-TYPE HOWMANY &HOW_MANY

[Sivakumar Gopi 2]

Hey Brent

This worked. Thanks for sharing it.

However I am trying to hold this in a file and then read it as a variable but it just give me NULL.

TABLE FILE <synonym>

COUNT * AS 'TOT'

ON TABLE SET ASNAMES ON

ON TABLE HOLD AS ENT_SS_TOT 

END

-RUN

-READFILE ENT_SS_TOT

-TYPE &TOT

[Brent Mason 2]

My suggestion was based on your statement that you wanted a count "...without holding the data inside webFOCUS." If that's not the case, you can verify that the car file has 18 records with Patrick's &RECORDS or &HOW_MANY example. This is the same answer given by COUNT *.

[Sivakumar Gopi 2]

I didn't want to hold all the data in a hold file and then count the lines. This is because sometimes my query could get over a million rows. That's why I want to check the number of rows first and then put a record limit on the data pull.

The count * was able to print the number of lines but when I try to hold it and read that it's is not recognizing the alias name I have given. That's my issue now.

Thank you

SG

[David Beagan]

I tried your code on a SQL Server table and it worked. I did change a couple of things:

TABLE FILE fexlogCOUNT * AS 'TOT'ON TABLE SET ASNAMES ONON TABLE HOLD AS ENT_SS_TOT END-RUN-SET &TOT=0;-READFILE ENT_SS_TOT-TYPE TOT=&TOT

output looks like:

Not sure why it doesn't work for you.

[Mat Barnard]

Can you post your current code here if you're still having an issue?

[Patrick Huebgen]

@Sivakumar Gopi​ 

did you tried my sample ? I prefer to use a COMPUTE instead of AS

-SET &HOW_MANY = 0;TABLE FILE CARSUM COMPUTE HOW_MANY/I11=CNT.SALES ;ON TABLE HOLD AS TEST FORMAT ALPHAEND-RUN-READFILE TEST-TYPE HOWMANY &HOW_MANY

[Sivakumar Gopi 2]

What I noticed is that when the count is equal or more than 100000 it started printing NULL. I had to make a CNT. on a column and the read it in a variable.

[Sivakumar Gopi 2]

Using COMPUTE worked. Thanks Patrick!

[Patrick Huebgen]

@Sivakumar Gopi​  - You are welcome