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     trouble with alpha return values - space padding

Read-Only Read-Only Topic
Go
Search
Notify
Tools
trouble with alpha return values - space padding
 Login/Join
 
<bigpgo>
posted
Hi all. I'm have some trouble saving a return value from SQL passthrough if it's an alpha value - the variable that gets saved is padded with spaces to whatever length that field is defined as:

SQL
SELECT DISTINCT PROJ_ID AS PROJID
FROM PROJECTS
WHERE PROJ_ID='Test';
TABLE
ON TABLE HOLD AS rep1 FORMAT ALPHA
END
-RUN

SET HOLDLIST=PRINTONLY
TABLE FILE rep1
PRINT
COMPUTE PROJ_ID/A16 = PROJID;
ON TABLE HOLD AS rep1 FORMAT ALPHA
END
-RUN
-READ rep1 &PROJ_ID.A16
SET HOLDLIST=ALL
-TYPE project name is &PROJ_ID :

That prints: "project name is Test <12 spaces>:"

As you can see, the project id is padded with spaces to be 16 characters. I cannot define it to be A4 because it's not always A4 - it can be up to 16 characters..

Any suggestions? Thank you.
 
Report This Post
<Jeff Sneden>
posted
The absolute easiest solution is to have a define where you append the empty string:

<SQL SELECT>

DEFINE FILE REP1
PROJ_ID2/A16 = PROJ_ID||'';
END

<WEBFOCUS PIRNT>

The || appends wile removing trailing white space. To append with out removing trailing WS, use |.

-Jeff Sneden
 
Report This Post
Noreen Redden
Gold member
posted Hide Post
Well, you can use TRIM to get rid of trailing blanks on a variable, after using a -READ.

However, in a DEFINE, we will always pad to the size of the field with blanks. Generally, in a report for instance, with SQUEEZE = ON and HTML, if everything is only 4 bytes, that column width will be 4. Can you give an example of why the trailing blanks are a problem?
 
Posts: 60 | Location: 2 penn | Registered: May 22, 2003Report This Post
<bigpgo>
posted
Thanks for the suggestions. I tried both TRIM and ||, but it does not get rid of the trailing spaces - maybe it's because I'm on an older version of webFOCUS - 4.36.

Here is my syntax:

COMPUTE PROJ_ID/A16 = TRIM('T', PROJID, 16, ' ', 1, 'A16');

Also, the reason why I need the variable to contain the exact value is because I later on use it in a sql passthrough command:

SELECT ... WHERE proj_id = '&PROJ_ID';...

which never matches because of the spaces.
 
Report This Post
Francis Mariani
Expert
posted Hide Post
Here's what to do:

-SET &PROJ_IDX = '''' || &PROJ_ID || '''';

SELECT ... WHERE proj_id = &PROJ_IDX;...

(That's four single quotes!)
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<bigpgo>
posted
Francis Mariani, I tried your suggestion:
it still pads it, but now prints it with quotes:
"project name is 'Test' :"

Also, the SELECT fails to grab any data (it grabs data if I hard code 'Test' as the project id, so that's not the issue).

It seems that because I defined the field to be A16, it will always pad it to 16, so I'll probably end up using Oracle's TRIM function right in the SQL passthrough.

Thanks for the suggestions.
 
Report This Post
Francis Mariani
Expert
posted Hide Post
The line
SELECT ... WHERE proj_id = &PROJ_IDX;...

will translate to
SELECT ... WHERE proj_id = 'test' ;...

which should not matter to the syntax of the SQL statement - the trailing blanks are outside the quoted selection criteria. That is why I suggested this method. This SHOULD work.
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
If your DB is Oracle,

look into the ORACHAR setting:

SQL SQLORA SET ORACHAR FIX/VAR

Setting it to FIX may make the SELECT statement work WITH the embedded blanks. The default is VAR.
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
N.Selph
Guru
posted Hide Post
Another solution might be to actually shorten the length of the variable in DM on the fly--
Here my input was &GEO:

-SET &LEN= ARGLEN(&GEO.LENGTH, &GEO, 'I2');
-SET &GEOS=TRIM('B',&GEO, &LEN, ' ', 1, 'A&LEN.EVAL');

This works on 5.21 -
I use it when the variable next has to go through a CTRAN function. That function really returns garbage in DM when there are trailing spaces in its input variable.
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
<bigpgo>
posted
N.Selph - I tried your approach and it worked well. Thank you and all others who contributed.
 
Report This Post
k.lane
Platinum Member
posted Hide Post
I'm now experiencing trouble similar to this. In my situation, I have an oracle table that has the column REPORTING_GROUP defined as VARCHAR2(4).

In the FOCUS master, it is defined as A4.

I am reporting from the table and using the following WHERE:

WHERE REPORTING_GROUP EQ 'ERR'

This however is not returning any results. Even if I use 'ERR ', it still doesn't work. If I use :

WHERE REPORTING_GROUP LIKE 'ERR%', it does work.

From this thread, I've made a few of the changes as I deemed as having potential and still nothing has worked.

Any ideas on this one?

Sure, I could make the logic change to use LIKE and append the % to the end of the string, but this seems rather unreasonable for such an easy WHERE statement.

Ken


Prod - WF 7.6.4 Unix/Solaris - Self-Service, BI Dashboard, MRE
Dev - WF 7.6.4 Unix/Solaris - Self-Service, BI Dashboard, MRE
Databases: Oracle 10g, SQL Server 2000, DB2.
 
Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005Report This Post
jimster06
Guru
posted Hide Post
How about TRUNCATE?

Example: Using the TRUNCATE Function as an Argument for a Function
In the following example, TRUNCATE is an argument for the EDIT function.

-SET &LONG = 'ABC ' ;
-SET &RESULT = EDIT(TRUNCATE(&LONG)'Z','9999');
-SET &LL = &LONG.LENGTH;
-SET &RL = &RESULT.LENGTH;
-HTMLFORM BEGIN

LONG = &LONG LENGTH = &LL

RESULT = &RESULT LENGTH = &RL

-HTMLFORM END

This is taken from the TECHNICAL DOCUMENTATION LIBRARY above - search on TRUNCATE.
 
Posts: 252 | Location: USA | Registered: April 15, 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     trouble with alpha return values - space padding

Copyright © 1996-2020 Information Builders