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'

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?

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,

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 .........

 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.

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

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.

Yep I agree make the Focus File with the indexed field should do the trick.

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

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.

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.

You sure the User_id is the same size on each file?