Focal Point
How to find out the no. of columns retrieved by the FOCEXEC Procedure

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6701077331

January 23, 2004, 07:09 AM
<Viswa>
How to find out the no. of columns retrieved by the FOCEXEC Procedure
Hai

I want to know how many columns have been retrieved by the FOCEXEC Procedure.
Please help me in finding that.

SQL SQLORA
SELECT * FROM TAB;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD
END

Now i want to know how many columns are present in HOLD file.
Is there a way to do that?
Thanks in advance.

Viswakanth
January 23, 2004, 01:44 PM
<Grzegorz>
Viswakanth,

You can use the following focexec to check the number of columns in the HOLD file:

TABLE FILE SYSTABLE<br />PRINT COLCOUNT<br />WHERE NAME EQ 'hold'<br />END
It works with WF 5.2.3. I suppose, it can be slightly different with the earlier releases.
Note: 'hold' is lowercase.

Hope this helps
Grzegorz
January 23, 2004, 05:11 PM
Carol Dobson
Another handy trick is after the SQLOUT talbe is created add these two lines to your program.

?FF SQLOUT
-EXIT

This will show you the fields and formats in the SQLOUT file, but doesn't actually count the columns.
January 24, 2004, 04:45 AM
susannah
those are cool tricks; i think the standard answer 'focwizard' answer might be this:
TABLE FILE CAR
do stuff
ON TABLE HOLD AS somefile
END
CHECK FILE somefile HOLD
then have a look at the file called HOLD produced by the CHECK FILE statement. Not only does it tell you how many fields there are, but all kinds of cool info . The CHECK command reads the master file description of the filename specified and then you can TABLE FILE HOLD PRINT * and see all the cool info. I think you'll like this technique. It has lots of uses. Its a FOCWIZARD classic.
January 26, 2004, 11:57 AM
Carol Dobson
Susannah,
The "Check File Filename HOLD" is very handy, but didn't work for us using Servlet, one of the reasons we switched back to cgi-bin. Does it work for you? We're on WF 5.2.1

Thanks!
Carol
January 28, 2004, 12:47 AM
susannah
Yes Carol. i'm in 522 and i tested this from my MRE environment where i'm stuck with Servlet as well, and it works fine. I'm upgrading to 524. but staying with ISAPI for my selfserv app. Can you use ISAPI instead of cgi?

I'm in 524 now, all works fine. Just as Mikel says, below.
February 09, 2004, 10:46 AM
<Viswa>
Hai Grzegorz

Thank you for giving the reply.
I need to catch the no. of columns in a variable so that, i can use for some calculations.

Ex:
Let's say, if no of columns are 5 i will do some process else i will do some other process.
So i need to capture the no. of Columns present in SQLOUT table into one variable.
Please help me how can i do that.

Thanks
Viswakanth
February 10, 2004, 05:28 PM
<mhuber>
Hey Viswa,
Try this:

SET HOLDLIST=PRINTONLY
SET ASNAMES=ON
TABLE FILE CAR
PRINT CAR
BY COUNTRY
ACROSS BODYTYPE
ON TABLE HOLD AS CARHOLD
END
? HOLD CARHOLD
TABLE FILE SYSTABLE
PRINT COMPUTE DUMMY/A4 = TRIM('L', EDIT(COLCOUNT), 4, '0', 1, 'A4');
WHERE NAME EQ 'carhold'
ON TABLE HOLD AS MYCOUNT
END
? HOLD MYCOUNT
-RUN
-SET &MyCols = '';
-READ MYCOUNT &MyCols.4.
-TYPE -------------------------
-TYPE CARHOLD has &MyCols columns.
COLCOUNT is an I4, so it's a fairly natural conversion to an A4. The TRIM is only there to get rid of the leading zeros produced by EDIT. At the top, SET HOLDLIST=PRINTONLY is necessary so that DUMMY is the only field in MYCOUNT. Otherwise, COLCOUNT will be the first field in the HOLD file, and we won't get the correct value in &MyCols. In the -READ statement, you can either have .4. or .A4. - both will do the same thing.

If you're using Dialogue Manager to do branching logic, you might have to do a .EVAL on &MyCols ( -IF &MyCols.EVAL GT 5 THEN GOTO SOMEPLACE ).

Cool trick, Grzegorz... I never knew about SYSTABLE.

Hope this helps,
Michael

This message has been edited. Last edited by: <Mabel>,
March 09, 2004, 06:47 AM
Mikel
CHECK FILE method

Carol, we have tested this example with servlet and cgi in 4.3.6 and 5.2.1. All works fine.


-* _____________________________________________________________________
-* The report

TABLE FILE CAR
PRINT
COUNTRY CAR MODEL BODY SALES RCOST DCOST
ON TABLE HOLD
END
-RUN

-* _____________________________________________________________________
-* Columns number retrieval - CHECK FILE method.
-* Input: &HOLD - hold filename.
-* Output: &COLUMNS - (A5) - HOLD columns number.

-DEFAULT &HOLD = 'hold' ;

CHECK FILE &HOLD HOLD

TABLE FILE HOLD
SUM CNT.FIELDNAME
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS COLUMNS FORMAT ALPHA
END
-RUN

-SET &COLUMNS = '?' ;
-READ COLUMNS &COLUMNS.A5.

-TYPE &HOLD file has &COLUMNS column(s).
Regards,
Mikel

This message has been edited. Last edited by: <Mabel>,