Sivakumar Gopi 2 Posted December 8, 2023 Share Posted December 8, 2023 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 youSGWF 8207.28.13 Link to comment Share on other sites More sharing options...
Patrick Huebgen Posted December 8, 2023 Share Posted December 8, 2023 Hi what exactly are you trying to build - where do you want this number to be displayed? Link to comment Share on other sites More sharing options...
Sivakumar Gopi 2 Posted December 8, 2023 Author Share Posted December 8, 2023 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. Link to comment Share on other sites More sharing options...
Brent Mason 2 Posted December 8, 2023 Share Posted December 8, 2023 TABLE FILE <synonym>COUNT *END Link to comment Share on other sites More sharing options...
Patrick Huebgen Posted December 8, 2023 Share Posted December 8, 2023 You can use &RECORDS or &LINES after you created a reportTABLE 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 Link to comment Share on other sites More sharing options...
Sivakumar Gopi 2 Posted December 8, 2023 Author Share Posted December 8, 2023 Hey BrentThis 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 ONON TABLE HOLD AS ENT_SS_TOT END-RUN-READFILE ENT_SS_TOT-TYPE &TOT Link to comment Share on other sites More sharing options...
Brent Mason 2 Posted December 8, 2023 Share Posted December 8, 2023 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 *. Link to comment Share on other sites More sharing options...
Sivakumar Gopi 2 Posted December 11, 2023 Author Share Posted December 11, 2023 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 youSG Link to comment Share on other sites More sharing options...
David Beagan Posted December 11, 2023 Share Posted December 11, 2023 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=&TOToutput looks like:Not sure why it doesn't work for you. Link to comment Share on other sites More sharing options...
Mat Barnard Posted December 12, 2023 Share Posted December 12, 2023 Can you post your current code here if you're still having an issue? Link to comment Share on other sites More sharing options...
Patrick Huebgen Posted December 12, 2023 Share Posted December 12, 2023 @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 Link to comment Share on other sites More sharing options...
Sivakumar Gopi 2 Posted December 13, 2023 Author Share Posted December 13, 2023 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. Link to comment Share on other sites More sharing options...
Sivakumar Gopi 2 Posted December 13, 2023 Author Share Posted December 13, 2023 Using COMPUTE worked. Thanks Patrick! Link to comment Share on other sites More sharing options...
Patrick Huebgen Posted December 14, 2023 Share Posted December 14, 2023 @Sivakumar Gopi - You are welcome Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now