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.

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

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?

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.

Here's what to do:

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

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

(That's four single quotes!)

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.

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.

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.

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.

N.Selph - I tried your approach and it worked well. Thank you and all others who contributed.

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

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.