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     [SOLVED] help with 'listed in file' command - alpha hold file adding extra spaces?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] help with 'listed in file' command - alpha hold file adding extra spaces?
 Login/Join
 
Gold member
posted
Unltimately, my goal here is to join two databases together by the ENCNTR.ALIAS and ICU_LIST.VST_EXT_ID fields. Here are the master file definitions of the two fields:

FIELDNAME=VST_EXT_ID, ALIAS=vst_ext_id, USAGE=A20, ACTUAL=A20, $
FIELDNAME=ALIAS, ALIAS=ALIAS, USAGE=A200V, ACTUAL=A200V, $
(Although the fields are longer than 7 chars, for my purposes it is a 7 digit number stored as an alphanumeric field.)

I am first creating a hold file on the first database with the following:
DEFINE FILE ICU_LIST
FIN_NUM/A9 = ''''||SUBSTR(20,VST_EXT_ID,1,7,7,'A7')||'''';
END
TABLE FILE ICU_LIST
PRINT
FIN_NUM
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS FIN_LIST FORMAT ALPHA
END

The records on this file look as I would expect when I table file it, ie, '1234567' '2345678' etc.

I'm then querying the second database with this file:
TABLE FILE ENCNTR_ALIAS
PRINT ENCNTR_ID
WHERE ENCNTR_ALIAS.ALIAS IN FILE FIN_LIST;
ON TABLE
END

I would expect this to parse to something like "where alias in ('1234567','2345678')" - which will work when I run this manually- but instead the trace shows the following and nothing is returned:
13.20.19 AE SELECT T1."ENCNTR_ID",T1."ALIAS" FROM V500.ENCNTR_ALIAS T1
13.20.19 AE WHERE (T1."ALIAS" IN('1796197
13.20.19 AE
13.20.19 AE
13.20.19 AE ', '1809173
13.20.19 AE
13.20.19 AE
13.20.19 AE ', '1849962
13.20.19 AE
13.20.19 AE
13.20.19 AE '

Where are all these extra spaces coming from?! Is there a better way of doing this?
Thanks in advance for any advice, my head hurts from banging it up against this wall all day...

This message has been edited. Last edited by: jjoyce,


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Report This Post
Virtuoso
posted Hide Post
Since that field is a Define..it must be adding extra spaces. You might need to use the TRIM function and get it down to what you need...just a thought


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
Tried a trim in my define and the result was the same.
DEFINE FILE ICU_LIST
FIN_NUM/A9 = ''''||TRIM('B',VST_EXT_ID,20,' ',1,'A7')||'''';


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Report This Post
Expert
posted Hide Post
Execute this command after holding the file to see the format of the fields in the hold file:

? FF FIN_LIST


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
You only require those single quotes around your filter values if they contain blanks.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
You could trying using a conditional JOIN: Using a Conditional Join


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
I replaced my define with
FIN_NUM/A7 = TRIM('B',VST_EXT_ID,20,' ',1,'A7');

but the trace remains the same and nothing is returned.


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Report This Post
Expert
posted Hide Post
Did you run this
quote:
? FF FIN_LIST
what did it tell you?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Example program:
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN
TABLE FILE BASEL_TIME_D
SUM
COMPUTE PERIOD_TYPE2/A5 = SUBSTR(10, PERIOD_TYPE, 1, 5, 5, 'A5');
BY PERIOD_TYPE NOPRINT
WHERE PERIOD_TYPE IN ('MONTH', 'DAY');
ON TABLE HOLD AS H001
END
?FF H001
-RUN
TABLE FILE BASEL_TIME_D
SUM
DAY_OF_WEEK
BY PERIOD_TYPE
BY DAY_OF_WEEK NOPRINT
WHERE PERIOD_TYPE IN FILE H001;
WHERE READLIMIT EQ 1000;
END

Result of ?FF H001:
FILENAME=  H001
PERIOD_TYPE2  E01           A5

Generated SQL:
AGGREGATION DONE ...
SELECT T1."PERIOD_TYPE",T1."DAY_OF_WEEK",
MAX(T1."DAY_OF_WEEK") FROM BASEL.TIME_D T1 WHERE
(T1."PERIOD_TYPE" IN('DAY', 'MONTH')) GROUP BY T1."PERIOD_TYPE",
T1."DAY_OF_WEEK" ORDER BY T1."PERIOD_TYPE",T1."DAY_OF_WEEK"
FETCH FIRST 1000 ROWS ONLY FOR FETCH ONLY;


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
At first it wouldn't work as I added a space "? FF" but now I got it...

This was the result using the trim function in my define.
FILENAME= FIN_LIST
FIN_NUM E01 A7
15.31.19 AE SELECT T1."ENCNTR_ID",T1."ALIAS" FROM V500.ENCNTR_ALIAS T1
15.31.19 AE WHERE (T1."ALIAS" IN('1796197
15.31.19 AE
15.31.19 AE
15.31.19 AE ', '1809173
15.31.19 AE
15.31.19 AE
15.31.19 AE ',


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Report This Post
Gold member
posted Hide Post
Also, I did attempt the conditional join and got only syntax errors.. I have never used one before so I don't fully understand how its supposed to work.

My concern with that it appears to me that the join will return all rows from the database and then apply a filter. This table is very large and I want only a very small subset so returning all the rows in the table would be a very inefficient way of getting at the data vs passing the values that I want in a where clause...


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Report This Post
Expert
posted Hide Post
What kind of database is this? Temporarily change the Master to
FIELDNAME=ALIAS, ALIAS=ALIAS, USAGE=A200, ACTUAL=A200, $
and test again. Also, in my DB2 environment, I added this to EDASPROF to eliminate the annoying A200V:

SQL DB2 SET VARCHAR OFF


(Sorry about the blank between ? and FF - my mistake)


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
One database is sql server... I am querying it to create the ICU_LIST hold file containing VST_EXT_ID in the earlier code- I left that part out for simplicity- and the hold file is a FOCUS format.

The other database containing encntr_alias.alias is an oracle 10g.


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Report This Post
Gold member
posted Hide Post
YES!!!!!!!!!!!
modifying the masterfile to remove the V did it. Thank you so much! These are our two major databases and I've had a growing need for integrated reports between them and this field is the only common link. Solving this helps me oodles and oodles!


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Report This Post
Expert
posted Hide Post
If you regularly regenerate the masters (we do once a month), you may wish to add the varchar command to EDASPROF and then regenerate the masters:

ENGINE [SQLMSS] SET VARCHAR {ON|OFF}


quote:
where:
SQLMSS - Indicates the Adapter for Microsoft SQL Server. You can omit this value if you
previously issued the SET SQLENGINE command.
ON - Maps the Microsoft SQL Server data type VARCHAR as variable-length alphanumeric
(AnV). This is required for Unicode environments. ON is the default value.
OFF - Maps the Microsoft SQL Server data type VARCHAR as alphanumeric (A).
(See "Managing Microsoft SQL Server Metadata" in the "Adapter Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS" doc).

I am afraid of the "This is required for Unicode environments" statement - we're moving to a Unicode environment and I hope this statement is not true!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report 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     [SOLVED] help with 'listed in file' command - alpha hold file adding extra spaces?

Copyright © 1996-2020 Information Builders