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,

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.

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.

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

Hi,

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

Regards!

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!

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.

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,

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

Hi,

Thanks a lot everyone!

Regards!

Please post the solution you used ...

Hi,

jfr99 solution worked!Thanks a lot again...


Regards!