[SOLVED]How to read count(*) in the read command....
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,
November 30, 2014, 10:27 AM
Twanette
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
November 30, 2014, 03:47 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 30, 2014, 09:41 PM
Danny-SRL
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
December 01, 2014, 04:50 AM
info4pal
Hi,
Thanks a lot everyone! I will try these and will let you know if it works...
Regards!
December 02, 2014, 07:31 AM
info4pal
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
Any idea on how to do this would be greatly helpful.
Thanks a lot in advance!
December 02, 2014, 08:08 AM
Rifaz
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
December 02, 2014, 08:09 AM
George Patton
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,
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 --------------------------------