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> Cannot join master files
Page 1 2 

Read-Only Read-Only Topic
Go
Search
Notify
Tools
<SOLVED> Cannot join master files
 Login/Join
 
Gold member
posted
I have two fields in two master files. One field is D20.2 (real) and the other is P11 (real). When I drap the field from one master file to the other, they will not connect. The data within these two fields is the same. Any ideas? I believe they need to be the same type. If so, what is a P11, and how can I change it to a D12.2. I cann't change the master file as that may affect others. Thanks

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


WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF
 
Posts: 54 | Registered: May 07, 2008Report This Post
Expert
posted Hide Post
Tracie,

Look up define-based join. You can use a define to set the format of the join key in one file to match the format of the join key in the other file.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Master
posted Hide Post
Tracie,

P and D are both numeric formats. Keep in mind that not only do they need to be in the same format, they need to be the same size. All you have to do to convert the P11 to a D20.2 is:
DEFINE yourfile
NEWFIELD/D20.2=OLDFIELD;
END


As Ginny suggested, you may be able to use a define-based join.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
And be aware of the fact that joining D-fiels may not yield the desired results.
D-fields are binary fields, and the chance that the values in the fields are indeed 100% the same in two instances is quite slim. On screen they may look the same, but it is quite possible that the values may differ somewhere in the 20th decimal position. If that's the case, the join will not be successful, and you'll be wondering why.
My advice would be to convert both fields in both data files using a define to a A20.2 field and then MATCH on these calculated values.

Hope this helps...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
quote:
a A20.2 field

???
hmmm.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Virtuoso
posted Hide Post
quote:
a A20.2 field



yes, I hmmmm too...




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
quote:
a A20.2 field


Yeah, Wow


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
Well now, c'mon, y'all know what I mean.
Or don't you?


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
No...




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
GamP,

Well... if I quote you:
quote:

And be aware of the fact that joining D-fiels may not yield the desired results.
D-fields are binary fields, and the chance that the values in the fields are indeed 100% the same in two instances is quite slim. On screen they may look the same, but it is quite possible that the values may differ somewhere in the 20th decimal position. If that's the case, the join will not be successful, and you'll be wondering why.

then I wouldn't use anything of type 'D' for JOIN or for MATCH. Since the other field is a P, the probably the content of the D20.2 is also a whole number. I would try to convert the 'D' to a 'P', or if possible, both to a 'I'.
What do you say?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
Are these FOCUS files? Bear in mind two caveats:

a. On platforms lacking packed-decimal arithmetic instructions, P format was (back in PC Focus days) stored internally as D, so the defined-P-to-real-P join would still be D-to-D. On Windows P is nowadays stored as packed (I just verified), so this may not be an issue.

b. In principle, P format has separate representations for +0 and -0. Not sure if -0 can actually occur, and if it would be treated as "equal" regardless. (Anyone know for sure?)


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
You can only obtain -0 if you program in assembler and if you are a bit crooked...

There was also the problem of distinguishing between +0 and 0 (signless 0) - same as above.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
Brings back vague memories of Univac 1100 architecture (36 bit word, 6 and 9 bit character representations), whose fixed-point integer format had equal-size ranges of positive and negative integers (unlike the 360), but hence had two zeros: all binary 0's, and all 1's.

And Wang, based on 360, which allowd low-values in a packed field as a valid representation of zero (usefull for file compression): so 000c, 000d, and 0000 were all valid in a signed packed field.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Master
posted Hide Post
What is type of files are the input files? If they are Oracle or DB2 create a view that does the join for you.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
These are Oracle master files. We are trying to define a new variable on both master files and will see if those new elements will join. I will let you know. Thanks


WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF
 
Posts: 54 | Registered: May 07, 2008Report This Post
Expert
posted Hide Post
remember, JOINs clear DEFINEs...
you might want to do a SET KEEPDEFINES ON
before your defines if you're going to try to set up a defined field on your guest file.
otherwise,
a defined-based join lets you transform the field on your host file to the same format as the field on the guest file.
That's how we do it here.
i'm sure i'm repeating something someone else has already told you...




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Master
posted Hide Post
Consider creating a view in Oracle that does the join for you. Oracle can join 2 numeric fields even when they are not the same length. Then create a master for the view. If you are using only a few of the fields from each table the data extraction is also faster with a view.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
This one hasn't been solved quite yet. We did create a view that does the join and I still cannot display the field from the view.
 PO Order 
PO Note ID 
Document Text Note ID Document Text 
Note Text Field 
426308 22,630,118.00 22,630,118.00 . 
426308 22,630,119.00 22,630,119.00 . 
 
It does match the two note fields but the note_text pulled from the new view will not display.
 TABLE FILE IFS_PURCHASE_ORDER_LINE_ALL
PRINT 
     ORDER_NO AS 'PO Order'
     NOTE_ID AS 'PO Note ID'
     NOTE_ID AS 'Document Text Note ID'
     NOTE_TEXT AS 'Document Text,Note Text Field'
HEADING
""
FOOTING
""
WHERE ( ORDER_NO EQ '426308' ); 
The two are joined on Order number


WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF
 
Posts: 54 | Registered: May 07, 2008Report This Post
Gold member
posted Hide Post
This one has not been solved and I still need some assistance. Can someone removed the SOLVED from the text? thanks


WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF
 
Posts: 54 | Registered: May 07, 2008Report This Post
Expert
posted Hide Post
tracie, you are the only one who can do that, as the thread owner




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Master
posted Hide Post
Can you post your MFD


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
Tracie,

Now Oracle has the join, and it still does not show.
Are you sure that the master file for IFS_PURCHASE_ORDER_LINE_ALL is the master file that has been created for the new Oracle view? For if it is and if the data is indeed in the separate physical tables, then this all of a sudden becomes an Oracle problem.
Please check again. And, as Pat suggests, please post your master file(s) and exact fex-code that you're using.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
Here is the MFD.
FILENAME=IFS_WI_NOTE_ID_TEXT, SUFFIX=SQLORA  , $
  SEGMENT=IFS_WI_NOTE_ID_TEXT, SEGTYPE=S0, $
    FIELDNAME=ORDER_NO, ALIAS=ORDER_NO, USAGE=A12V, ACTUAL=A12V, $
    FIELDNAME=NOTE_ID, ALIAS=NOTE_ID, USAGE=D20.2, ACTUAL=D8, $
    FIELDNAME=NOTE_TEXT, ALIAS=NOTE_TEXT, USAGE=A2000V, ACTUAL=A2000V, $
  


WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF
 
Posts: 54 | Registered: May 07, 2008Report This Post
Virtuoso
posted Hide Post
Tracie,

This is a different master file as the one you mentioned earlier.
Are you able to just use this master file and get the text for the order?
If not, are you able to get the text by using the oracle software (sqlplus for instance)?
If you do see the text in this way, then what exactly is the join and table command where it fails to get the text?


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
The DBA made a type in the file name and recreated it. It has the same elements as the first one. I can just use the IFS_WI_NOTE_ID_TEXT master file and display the note text for the same order. When I join it to the purchase order line all master file I loose the note text.


WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF
 
Posts: 54 | Registered: May 07, 2008Report This Post
Virtuoso
posted Hide Post
In that case, the problem lies either with the join or with the other master file.
Does this other master file by accident also have a field called NOTE_TEXT?
If so, you need to qualify your field name to instruct webfocus that is has to come from the joined file (ie. print the IFS_WI_NOTE_ID_TEXT.NOTE_TEXT field).
If this is not the case or if it still does not print, then we'll defenitely have to see your master file(s) and code to try and find the cause of the problem.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
Yes, both files have a NOTE_TEXT and the one on the purchase order line all is empty. How to I change the field in Webfocus to look at the note_text from the WI_IFS_NOTE_ID_TEXT master instead of the one? I changed the source, but it reverted back to the original
  ORDER_NO AS 'PO No'
     NOTE_ID AS 'PO Note ID'
     NOTE_ID AS 'Document Text,Note ID'
     NOTE_TEXT 
Thanks


WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF
 
Posts: 54 | Registered: May 07, 2008Report This Post
Virtuoso
posted Hide Post
Using DevStudio, are you?
In the object inspecter, right click and check Qualified FIeldnames (or some text to that effect). I think it's the first option in the menu.
The select your note_text field from the object inspector to show on the report.
That should do it.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
That worked. Thank you so very much. This one is SOLVED.


WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF
 
Posts: 54 | Registered: May 07, 2008Report This Post
Gold member
posted Hide Post
One last question. How do I change the heading for this post adding , or does someone else do that? This forum has been extremely helpful to me and I want to ensure I know how to properly update by posts. Thanks


WebFocus 7703
Windows 7
Output format: HTML, Excel, PDF
 
Posts: 54 | Registered: May 07, 2008Report This Post
  Powered by Social Strata Page 1 2  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     <SOLVED> Cannot join master files

Copyright © 1996-2020 Information Builders