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     WebFocus against SQL TX fields

Read-Only Read-Only Topic
Go
Search
Notify
Tools
WebFocus against SQL TX fields
 Login/Join
 
Carol Dobson
Guru
posted
Reporting against a SQL table, I have a CompanyDescription field that is in TX format;

I need to see if there are any blank Company Descriptions. Documentation says you can use CONTAINS or OMITS to generate LIKE and NOT LIKE in SQL

However, I can't say
IF COMPANYDESCRIPTION CONTAINS ' ' as I would get every description with a blank anywhere in the text.

any way to do this without writing the SQL?
which is my last choice.

Thanks!
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
andrew w
Member
posted Hide Post
Carol,

I've never tried using this but from the description of POSIT subroutine, this might be what you're after. POSIT finds the starting position of a substring within a larger string and returns zero if the substring is not found.

Cheers

Andrew W
 
Posts: 27 | Location: Sydney, Australia | Registered: May 27, 2003Report This Post
susannah
Expert
posted Hide Post
Carol, here's a very simple way you might like: if companydesc is, say, a 30 character field,then
COMPANYDESC/A30=LJUST(30,COMPANYDESC,'A30');
just to make sure you've left adjusted your field
then in your main table request:

IF COMPANYDESC NE 'put 20 or so blanks here'

and the blank field you're creating here to compare to need not be even a full 30 chars in order to work just fine.
Note: when i write in this message board and i have 20 blanks in between the two ' marks, this board sandwiches the line to:
IF COMPANYDESC NE ' ' which is NOT what i mean to be saying here. I mean to put 20 or so blanks in between those 2 ' marks. You get that, right?
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Carol Dobson
Guru
posted Hide Post
Susannah and Andrew,
Thanks for the feedback. The problem is TEXT fields in SQL can not be used in User Written Subroutines or Functions (as they are now called).

Returns error;
(FOC702) THE OPTION SPECIFIED IS NOT AVAILABLE WITH TEXT FIELDS:

I'm limited to CONTAINS or OMITS!!

Thanks again!
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
EricH
Platinum Member
posted Hide Post
Carol,

A solution will depend on your underlying RDBMS (I assume you're using an RDBMS since you said you're eporting against a SQL table). In some RDBMSs, such as Oracle, you cannot even issue a WHERE statement against a LONG data field. Others are more flexible. Is there an upper limit on the # of characters you will see in your text field? I'm not sure what the upper limit for regular character fields is these days (4096?) but if there is an upper limit, perhaps you can change or re-DEFINE the field in your MFD from text to alpha. This would allow you to use a standard WHERE text-field EQ ' '. Good luck!

EricH
 
Posts: 164 | Registered: March 26, 2003Report This Post
Carol Dobson
Guru
posted Hide Post
Hi Eric,
We are using a RDBMS. When I create the synonym I can select SET CONVERSION LONGCHAR = TEXT or ALPHA. When I set it to Alpha, the master gets generated with an A500 field. I'm guessing that's what you mean by upper limit?

However, it gets treated the same as the TX with screening statements. I can only use CONTAINS or OMITS.

I get the same error
FOC702) THE OPTION SPECIFIED IS NOT AVAILABLE WITH TEXT FIELDS

Thanks!
Carol
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
GCohen
Platinum Member
posted Hide Post
If you change the USAGE format from TX to
AnnV, eg A80V, then you can submit a
WHERE field NE ' ' ;

You may have to also change the ACTUAL format to an AnnV.
This will only work in release 5.2.3 and up, as
Focus now directly supports the VarChar data type.
 
Posts: 226 | Registered: June 08, 2003Report 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     WebFocus against SQL TX fields

Copyright © 1996-2020 Information Builders