Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]How to read count(*) in the read command....

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]How to read count(*) in the read command....
 Login/Join
 
Guru
posted
Hi,

Iam doing an Sql passthrough which goes something like this :
select COUNT(*) from (select distinct y,z from table1
where x in (&var)and y is not null );
END

Later Iam tring to do this :
TABLE FILE SQLOUT
PRINT *
ON TABLE SET ASNAMES ON
ON TABLE SAVE AS s1
END
-RUN
-*-EXIT

Now Iam trying to read the count variable which is getting printed in the above table file request and I tried reading it using its Alias name(E01): but that doesnt seem to work...

-READ S1 &E01.D20.2.
-TYPE &E01
-EXIT

Could anyone please let me know the column name which needs to be used in the read and in the type commands as I have already done a count(*) in Sql passthrough...


Thanks a lot in advance!

This message has been edited. Last edited by: info4pal,
 
Posts: 270 | Registered: October 30, 2014Report This Post
Platinum Member
posted Hide Post
Hi,
-READ doesn't have any knowledge about fields or aliases or field formats.
-READ simply reads strings of text from a text file. It puts these into dialogue manager variables.

If you run your "SAVE" section with the -EXIT i.e.
TABLE FILE SQLOUT
PRINT *
ON TABLE SET ASNAMES ON
ON TABLE SAVE AS s1
END
-RUN
-EXIT

You should see the results of the SAVE e.g. number of records and lines, as well as what was written to the TEXT file that the SAVE format created. There you will see the sequence and the length of the data.
You would use that to construct your -READ
e.g.
if it shows a single column with a length of 12 (NB: length, not format), then your -READ would be something like this:
-READ S1 &whatever.12.

See the "WebFOCUS Developing Reporting Applications Release 8.0" manual for more info about -READ.
It also has info about -READFILE, which could be another option.


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Expert
posted Hide Post
Be very careful of PRINT *, as all fields will be returned.

If you add more fields to the SQL command, they will be saved in the s1 file.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
quote:
TABLE FILE SQLOUT
PRINT *
ON TABLE SET ASNAMES ON
ON TABLE SAVE AS s1
END
-RUN

Did you try:
  
TABLE FILE SQLOUT
PRINT *
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS s1
END
-RUN
-READFILE s1


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Guru
posted Hide Post
Hi,

Thanks a lot everyone!
I will try these and will let you know if it works...

Regards!
 
Posts: 270 | Registered: October 30, 2014Report This Post
Guru
posted Hide Post
Hi,

Iam trying to do a count on 2 fields but am not able to it and also not able to read the 2 variables value as well :

TABLE FILE SQLOUT
SUM
CNT.DST.field1
CNT.DST.field2
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS SAVE1 FORMAT ALPHA
END
-RUN

-READ SAVE1 &field1.I5 &field2.I5
-TYPE &field1 &field2

Any idea on how to do this would be greatly helpful.

Thanks a lot in advance!
 
Posts: 270 | Registered: October 30, 2014Report This Post
Guru
posted Hide Post
quote:
TABLE FILE SQLOUT
SUM
CNT.DST.field1
CNT.DST.field2
ON TABLE SET ASNAMES ON
-*ON TABLE HOLD AS SAVE1 FORMAT ALPHA
END
-RUN


Are you getting the datas for field1 & 2 in your output? Seems, there is nothing wrong in your code.
BTW, update your signature.


-Rifaz

WebFOCUS 7.7.x and 8.x
 
Posts: 406 | Location: India | Registered: June 13, 2013Report This Post
Master
posted Hide Post
Have you tried breaking this down and testing it bit by bit?

i.e.

TABLE FILE SQLOUT
SUM
CNT.field1
CNT.field2
END

-* If that works then try

TABLE FILE SQLOUT
SUM
CNT.DST.field1
CNT.DST.field2
END

-* If that works then try your HOLD FORMAT ALPHA and do

TABLE FILE SAVE1
PRINT *
END

-* If that works then check the -READ ...

-READ SAVE1 &field1.I5 &field2.I5
-TYPE &field1 &field2



Actualy, now that I look at it: Is it correct to specify the data type in a -READ statement? You are saving in ALPHA and your read suggests that the values are integers. I don't have time right now to sus this out, so at least check on that. In the back of my head is a notion that you can only specify the length of your fields with -READ, not the type.

This message has been edited. Last edited by: George Patton,


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Platinum Member
posted Hide Post
Hi,

Here's an example of counting the number of countries and cars from the CAR file and using -READFILE to get the values into AMP variables.

Here's the FEX:

-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE +++ COUNT COUNTRIES AND CARS IN CAR FILE +++
-TYPE +++ HOLD FILE AS HLD_VARS +++
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
TABLE FILE CAR
SUM
CNT.DST.COUNTRY AS 'CNT_CNTRY'
CNT.DST.CAR AS 'CNT_CAR'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HLD_VARS
END
-RUN
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE +++ DISPLAY LAYOUT OF HLD_VARS HOLD FILE +++
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
? HOLD HLD_VARS
-RUN
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE +++ SET AMP VARS BY USING -READFILE OF HLD_VARS +++
-TYPE +++ DISPLAY AMP VARS USING TYPE COMMANDS +++
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-READFILE HLD_VARS
-TYPE --------------------------------
-TYPE CNT_CNTRY ----- &CNT_CNTRY
-TYPE CNT_CAR ------- &CNT_CAR
-TYPE --------------------------------
-RUN

This is what I get when I run this:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++ COUNT COUNTRIES AND CARS IN CAR FILE +++
+++ HOLD FILE AS HLD_VARS +++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1
0 NUMBER OF RECORDS IN TABLE= 10 LINES= 1
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++ DISPLAY LAYOUT OF HLD_VARS HOLD FILE +++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
0DEFINITION OF HOLD FILE: HLD_VARS
0FIELDNAME ALIAS FORMAT
CNT_CNTRY E01 I5
CNT_CAR E02 I5
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++ SET AMP VARS BY USING -READFILE OF HLD_VARS +++
+++ DISPLAY AMP VARS USING TYPE COMMANDS +++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--------------------------------
CNT_CNTRY ----- 5
CNT_CAR ------- 10
--------------------------------

Maybe this will help.

~Jim


WebFocus 8.201M, Windows, App Studio
 
Posts: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008Report This Post
Guru
posted Hide Post
Hi,

Thanks a lot everyone!

Regards!
 
Posts: 270 | Registered: October 30, 2014Report This Post
Master
posted Hide Post
Please post the solution you used ...


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Guru
posted Hide Post
Hi,

jfr99 solution worked!Thanks a lot again...


Regards!


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]How to read count(*) in the read command....

Copyright © 1996-2020 Information Builders