Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to find the usage type of a field?
Go
New
Search
Notify
Tools
Reply
  
How to find the usage type of a field?
 Login/Join
 
Gold member
posted
Hi,
Could any one of you please tell me how can find the Usage type of a field in the table.
Because in my reports all the parameters will be passed as string.
Like as follows
-set &parm1 = 'where country_id eq ''12''';
Actually in my table the country id is a numeric field.so it is throwing error if I include the above parameter in the WHERE section.
Following is the error i am getting
"COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED"
My table request looks as follows.

TABLE FILE COUNTRY
PRINT *
&PARM1
END

And the &Parm will be dynamic and it can be of any data type, sometime i may use country_name as parm1.
Can anyone of you help me how to figure out columns usage type before comparing it.
 
Posts: 71 | Registered: November 20, 2003Reply With QuoteReport This Post
<Kyle>
posted
Here is a routine that I use for this purpose as well as getting the TITLE attribute value for a field. Here is the code, you should put it in a fex called FLDINFO. Hope this helps.

-* FLDINFO
-* =======
-* This routine is used to extract the TITLE and USAGE/FORMAT attributes
-* concerning specific fields. It is useful when you have parameterized report
-* requests and need to access these attributes for placement in headings,
-* footings, column titles, defines/computes, etc.
-*
-* The information is stored in a temporary file that may be referenced by a
-* DECODE statement. The temporary files are called FLDTITLE and FLDUSAGE.
-*
-* This procedure only requires 1 parameter/variables to be passed, FILENAME.
-*
-* FILENAME - This is the name of the MASTER or top level JOIN file you wish to
-* get information on.
-*
-* For Dynamic or Joined tables (requests using the JOIN command), the routine
-* needs to be called after the JOIN command is processed. For example:
-* JOIN . . .
-* EX FLDINFO . . .
-* -RUN
-* TABLE FILE . . .
-*
-* NOTE: After a -INCLUDE or EX of this routine you need to place a -RUN
-* command to force the HOLD files to be created.
-*
-* EXAMPLE - The example shows using field titles to dynamically generate
-* headings and column titles for computed columns. Also, I am dynamically
-* assigning the format for a computed field depending on the field that is
-* being summed. NOTE: &MFLD_NAME/&DFLD_NAME would normally be passed as
-* parameters from a web page; I simply coded them for simplicity.
-* =============================== />-* -SET &MFLD_NAME=DOLLARS;
-* -SET &DFLD_NAME=PRODUCT;
-* EX FLDINFO FILENAME=GGSALES
-* -RUN
-* -SET &MFLD_TITLE=DECODE &MFLD_NAME (FLDTITLE ELSE 'Unknown');
-* -SET &MFLD_USAGE=DECODE &MFLD_NAME (FLDUSAGE ELSE 'A30');
-* -SET &DFLD_TITLE=DECODE &DFLD_NAME (FLDTITLE ELSE 'Unknown');
-* TABLE FILE GGSALES
-* SUM &MFLD_NAME
-* COMPUTE NEWFLD/&MFLD_USAGE=&MFLD_NAME * 3; AS '&MFLD_TITLE x 3'
-* BY &DFLD_NAME
-* HEADING
-* "&MFLD_TITLE Summary Report by &DFLD_TITLE"
-* END
-*
-SET &FILELEN='A' | &FILENAME.LENGTH;
-SET &UCFILENAME=UPCASE(&FILENAME.LENGTH,&FILENAME,'&FILELEN.EVAL');
CHECK FILE &FILENAME HOLD AS FLDINFO
DEFINE FILE FLDINFO
NEWTITLE/A80=IF TITLE EQ '' THEN FIELDNAME ELSE TITLE;
TRUNC_TITLE/A30=EDIT(NEWTITLE,'999999999999999999999999999999$');
TRANS_TITLE/A30=CTRAN(30,TRUNC_TITLE,44,32,TRANS_TITLE);
TRANS_TITLE2/A32='''' | TRANS_TITLE || '''';
END
-*
TABLE FILE FLDINFO
PRINT FIELDNAME TRANS_TITLE2
ON TABLE HOLD AS FLDTITLE
END
-*
TABLE FILE FLDINFO
PRINT FIELDNAME FORMAT
ON TABLE HOLD AS FLDUSAGE
END

This message has been edited. Last edited by: <Mabel>,
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
If you want to test the value of an &er variable obtained from a FORM you can use the Dialogue Manager in this way.,.,
-IF &TEST.TYPE NE I GOTO BAD ;
TABLE
WHERE VALUE EQ &TEST ;

...
-BAD

You can test for Alpha data with &VALUE.TYPE EQ A
and numeric with &VALUE.GTYPE EQ I
 
Posts: 226 | Registered: June 08, 2003Reply With QuoteReport This Post
Guru
posted Hide Post
You could call the table 'SYSCOLUM' and it will tell you all the stuff you want to know.

SET LINES=10000
TABLE FILE SYSCOLUM
HEADING CENTER
"WEBFOCUS COLUMN NAMES FOR TABLE &TABLENAME"
"&DATE"
" "
PRINT
-* ALIAS
COLTYPE
LENGTH
ACTUAL
USAGE
SCALE
NULLS
RESTRICT
TITLE
REMARKS
HELPMESSAGE
DIMEN_NAME
DIMEN_ID
DIMEN_LEVEL

BY COLUMN_NAME
WHERE TABLE_NAME EQ '&TABLENAME';
FOOTING
" "
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT EXL2K
END
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks to all for the help.
Kyle's example worked out.Tried on Curtis's but it throws an error says usage is not the field name or it gives zero record in it (No HTML output)

TABLE FILE SYSCOLUMN
PRINT *
BY COLUMN_NAME
WHERE TABLE NAME EQ 'MASTER FILE NAME'
 
Posts: 71 | Registered: November 20, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks to all for the help.
Kyle's example worked out.Tried on Curtis's but it throws an error says usage is not the field name or it gives zero record in it (No HTML output)

TABLE FILE SYSCOLUMN
PRINT *
BY COLUMN_NAME
WHERE TABLE NAME EQ 'MASTER FILE NAME'
 
Posts: 71 | Registered: November 20, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
quote:
Originally posted by krishkasi:
[qb]TABLE FILE SYSCOLUMN
PRINT *
BY COLUMN_NAME
WHERE TABLE NAME EQ 'MASTER FILE NAME' [/qb]
If you are on Unix, make sure the name used in 'MASTER FILE NAME' is lower case !
 
Posts: 54 | Location: Switzerland | Registered: May 13, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to find the usage type of a field?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.