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] Global join error with custom table

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Global join error with custom table
 Login/Join
 
MO Admin
Gold member
posted
I am trying to join a custom user table to the SMQUERY table so I can display the full user name instead of the user ID when running my custom usage report.

I can get the join to happen without errors, but when I run the report, I get FOC1101--Interface does not support global join.

Can anyone shed any light on this error, as I have not been able to find any information at all about it.

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


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
Prarie
Virtuoso
posted Hide Post
were you able to see this entire message
FOC1101) THIS INTERFACE DOES NOT SUPPORT THE GLOBAL JOIN
The JOIN command does not support the
external files you are attempting to join
together.

What is your Custom user table? Can you get back data if you run a report against it?


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
MO Admin
Gold member
posted Hide Post
No, I only get the following:
(FOC1101) THIS INTERFACE DOES NOT SUPPORT THE GLOBAL JOIN


When I run a report based on only my custom table, I get all the infomration. The table only includes user id, user name, role, group, and FTI Y/N indicator.

This information was created by running the mrextract.bat on the server, and pulling the user text file into Excel. I then added my FTI Y/N indicator or each row. Then I went into my Master File directory, and did 'New'-->'Upload Data File'


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
GinnyJakes
Expert
posted Hide Post
quote:
What is your Custom user table? Can you get back data if you run a report against it?


Can you answer these questions or post the master and access files for this custom table.

What data source is SMQUERY? DB2, SQL Server?


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
MO Admin
Gold member
posted Hide Post
Here is the master file for my user table:
FILENAME=WF_USER_TABLE, SUFFIX=COM ,
DATASET=ANALYZER/WF_USER_TABLE.TXT, $
SEGMENT=WF_USER_TABLE, SEGTYPE=S1, $
FIELDNAME=USER_ID, ALIAS=USER_ID, USAGE=A48, $
FIELDNAME=ROLE, ALIAS=ROLE, USAGE=A50, $
FIELDNAME=USER_NAME, ALIAS=USER_NAME, USAGE=A100, $
FIELDNAME=GROUP, ALIAS=GROUP, USAGE=A50, $
FIELDNAME=FTI, ALIAS=FTI, USAGE=A1, $


When I run a report based on only it, I get all the data.

My analyzer is a Focus/FDS repository, so I am assuming SMQUERY is a focus data source.

Thanks,


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
GinnyJakes
Expert
posted Hide Post
I am going to try what you are doing on my dev box. My repository is DB2 and my platform is AIX but that shouldn't make a difference.

Stay tuned .........


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
GinnyJakes
Expert
posted Hide Post
 TABLE FILE IBIMRUSR
PRINT DST.USER_NAME
BY USER_ID
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS USERS FORMAT ALPHA
END
JOIN SMUSER IN SMQUERY TO USER_ID IN USERS AS J1
TABLE FILE SMQUERY
PRINT USER_NAME SMRPCNAME
BY SMUSER
END


Here is my master for ibimrusr.txt.

FILENAME=IBIMRUSR, SUFFIX=COM
DATASET='C758760/IBIMRUSR.TXT'
SEGNAME=IBIMRUSR, SEGTYPE=S1, $
  FIELDNAME=USR_HTM, ALIAS=USR_HTM, FORMAT=A50, $
  FIELDNAME=USER_ID, ALIAS=USER_ID, FORMAT=A48, $
  FIELDNAME=ROLE, ALIAS=ROLE, FORMAT=A50, $
  FIELDNAME=FIELD_4, ALIAS=FIELD_4, FORMAT=A200, $
  FIELDNAME=USER_NAME, ALIAS=USER_NAME, FORMAT=A100, $
  FIELDNAME=FIELD_6, ALIAS=FIELD_6, FORMAT=A50, $


My extract has lots more columns than yours does for one thing so make sure that the master is correct.

The second this is that if you are going to join to a sequential file, the data must be sorted by the join key.

The request above works at my site. Make sure you are following all of the rules.


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
MO Admin
Gold member
posted Hide Post
I recreated my table and report exactly as yours is. When I run it, I get:

No HTML Output!

--------------------------------------------------------------------------------


0 NUMBER OF RECORDS IN TABLE= 331 LINES= 289
(FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
GinnyJakes
Expert
posted Hide Post
quote:
FOC1070: The FROM field used in a JOIN command must be sorted in ascending sequence within the file if it is joined to a FIX, COM or VSAM/ESDS file.


Well the good news is that you are getting a different message. The bad news is that it is still not working. Your source must also be sorted by USER_ID.

Another thing you could do on the 'USERS' file is create it as

ON TABLE HOLD AS USERS FORMAT FOCUS INDEX USER_ID


That would eliminate the sort requirement.

You may have to paste some of your code in order for us to diagnosis this further.


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
Prarie
Virtuoso
posted Hide Post
Yep I agree make the Focus File with the indexed field should do the trick.
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
MO Admin
Gold member
posted Hide Post
Using the following code, I am able to run it. However, I am only getting user_name data for my personal MRE id. All the others are blank.

TABLE FILE IBIMRUSR
PRINT USER_NAME
BY USER_ID
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS USERS FORMAT FOCUS INDEX USER_ID
END
JOIN SMUSER IN SMQUERY TO USER_ID IN USERS AS J1
TABLE FILE SMQUERY
PRINT USER_NAME SMRPCNAME
BY SMUSER
END


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
GinnyJakes
Expert
posted Hide Post
Please look at the contents of your uploaded file to make sure that it contains everything that you need.

Also make sure that the SMQUERY file has everything as well.

You may need a SET ALL=ON statement before your code to get the short paths.


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
MO Admin
Gold member
posted Hide Post
When I run reports on each table individually, they both contain all the data I need. SMQUERY has records for several differnt users, and IBIMRUSR has all of my users.

The SET ALL=ON command did not make a difference.


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
Prarie
Virtuoso
posted Hide Post
You sure the User_id is the same size on each file?


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
MO Admin
Gold member
posted Hide Post
Yes, they are both A48


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
Prarie
Virtuoso
posted Hide Post
Try this

JOIN SMUSER IN SMQUERY TO ALL USER_ID IN USERS AS J1
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
MO Admin
Gold member
posted Hide Post
Same result.

Even when I put this in MR and log in as a different user, I still only see my name being matched; not the logged in user's name.


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
Prarie
Virtuoso
posted Hide Post
Well than maybe you should try a Match...I'm not sure why it would not join...I must be missing something.
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
GinnyJakes
Expert
posted Hide Post
A MATCH won't work. He can have multiple rpc's for the same user id and he needs the name for all of them.

The example I posted works in my environment. But my ibimrusr.txt file master does not match his and that concerns me.

MO,

Can you cut and paste some of your data from the user file into a post so that I can see it?


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
MO Admin
Gold member
posted Hide Post
Ginny,

I made my ibimrusr file match yours. The reason it didn't match at first was because I cleaned out some of the fields that I did not need and added one of my own.

However, after your post, I recreated the table exactly like yours, and that is what I have been working with.

I will upload some data for you tomorrow. Time to call it quits for today.

Thanks for everyone's help.


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
MO Admin
Gold member
posted Hide Post
Got a reply from IBI support that finally got it working.

The problem was that on the SMQUERY table, the user id's are in all UPPER case. On my user table, we use all LOWER case id's.

They had me convert my user table to all UPPER case and then do the match.

That worked! Thanks for everyone's help. I really do appreciate it.


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report 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] Global join error with custom table

Copyright © 1996-2020 Information Builders