Is there a way to join a SQLMSS table inside a Focus master file

Like, inside focus database master file FILE1.mas join a SQL Server table EMPTAB as below:

FILE=FILE1, SUFFIX=FOC, SEGMENT=SEG01, SEGTYPE=S1, $
FIELDNAME =EMP_ID, EMPID ,I9 ,FIELDTYPE=I, $

SEGMENT=SEG02,SEGTYPE=DKU,PARENT=SEG01,
CRFILE=EMPTAB,CRSEGNAME=EMPTAB,CRKEY=EMP_ID,$


Currently, this is producing an error as below:
Error code is: 32027Could not get error message

Something had to do with the way i was joining the SQL Server table.

Thank you in advance

This message has been edited. Last edited by: Kathleen Butler,

You do it the same way.

Its in the documentation.

Waz,

I could not find it in the documentation. Could you please point me to it?

? 32027
(FOC32027) CONSOLE CONNECT TO %1 FAILED

Yes, it's vague.

You may have to qualify the table name EMPTAB with the schema name.

Or, perhaps WF metadata (.mas, .acx) is required when you join with this method. Did you generate metadata? Take a look at the acx file - perhaps it has a clue how the cross-reference should be coded.

The documentation Waz refers to is: Describing Data With WebFOCUS Language > Defining a Join in a Master File

Though you may not need to do this, see also:

Adapter Administration for UNIX, Windows, OpenVMS, IBM i, and z/OS > Using the Adapter for Microsoft SQL Server > Optimizing Non-Equality WHERE-Based Left Outer Joins

Adapter Administration for UNIX, Windows, OpenVMS, IBM i, and z/OS > Cluster Join

The SQLMSS EMPTAB.acx file contains
SEGNAME=EMPTAB, TABLENAME=MSBI.dbo.Emptab, CONNECTION=MSBI_PROD, $

So, I added a new line with EMP_ID as a key field inside the .acx file
SEGNAME=EMPTAB, KEYFLD=EMP_ID, IXFLD=EMP_ID, $

Now, inside foc master file FILE1.mas, i tried to join the SQLMSS table in the following way.
FILE=FILE1, SUFFIX=FOC, SEGMENT=SEG01, SEGTYPE=S1, $
FIELDNAME =EMP_ID, EMPID ,I9 ,FIELDTYPE=I, $

SEGMENT=SEG02, SEGTYPE=DKU, SEGSUF=SQLMSS, PARENT=SEG01, CRFILE=EMPTAB, CRKEY=EMP_ID, CRSEGMENT=EMPTAB, CRJOINTYPE=INNER, $

Still no luck. It still keeps crashing with Error code 32027

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

Can you try using Data Management Console?

Try using the Synonym Editor in Dev Studio (I imagine it's there for App Studio as well). This is equivalent to the Data Management Console suggested by Ricardo.

The doc: Describing Data With Graphical Tools > Using the Synonym Editor > Creating Business Views > How to Create a Business View Using the Synonym Editor

It is possible you missed something in the acx file, so it might be better to use the GUI.

We don't have Data Management console. I tried my case with synonym editor in dev studio.

I am able to see the columns list when i do ?FF FILE1. But, I could not get the data when i use the query.

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

I attempted this with a FOCUS DB (HTESTDATES) and an Oracle table (TEST_CALENDAR). I don't get the 32027 error, but I do get this error:

(ACC20201) BOTH DATASET AND ACCESS FILE NOT PERMITTED

I used only the GUI, so I guess it's not fool-proof (as all other GUI tools).

The mas:

FILENAME=newmas, $
  SEGMENT=SEG01, CRFILE=TEST/HTESTDATES, CRINCLUDE=ALL, $
  SEGMENT=ENTRPT_DIM_CALENDAR, SEGTYPE=KM, PARENT=SEG01, CRFILE=COMMON/ENTRPT_DIM_CALENDAR, CRINCLUDE=ALL,
    JOIN_WHERE=SEG01.BUS_DTE EQ ENTRPT_DIM_CALENDAR.BUS_DTE;, $

The acx:

SEGNAME=SEG01, GUI_PARAMS='1,311,52,411,172,100,120,1', $
 SEGNAME=TEST_CALENDAR, GUI_PARAMS='1,690,267,896,387,206,120,1', $

It appears that the schema name is not required as the WebFOCUS METADATA name (TEST_CALENDAR) is being used...

I have no clue why I get the BOTH DATASET AND ACCESS FILE NOT PERMITTED error and I've never seen a "ACC" error message before.

WF1326, I'm not sure if I read your original post correctly - I wouldn't add anything inside an existing master - I would create a new master with the Synonym Editor and reference the FOCUS DB and MSSQL table. You may end up with the error I'm getting. I will attempt to fix this error by removing the dataset tag in the master for the FOCUS DB.

I created this using DMC.
master:

FILENAME=DW_ADV_VENEZUELA, SUFFIX=SQLMSS  , $
  SEGMENT=MSRSD83_ADV_REPORTS_VENEZUELA, SEGTYPE=S0, $
    FIELDNAME=SRSD83_IDENTITY_K, ALIAS=SRSD83_IDENTITY_K, USAGE=P23, ACTUAL=P12, FIELDTYPE=R, $
    FIELDNAME=SRSD83_SRSD01_DIVISION_C, ALIAS=SRSD83_SRSD01_DIVISION_C, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=SRSD83_SRSD10_MANUFACTURER_C, ALIAS=SRSD83_SRSD10_MANUFACTURER_C, USAGE=A3, ACTUAL=A3, $
    FIELDNAME=SRSD83_SRSD37_SUBSEGMENT_K, ALIAS=SRSD83_SRSD37_SUBSEGMENT_K, USAGE=P23, ACTUAL=P12, $
    FIELDNAME=SRSD83_CATALOG_X, ALIAS=SRSD83_CATALOG_X, USAGE=A15V, ACTUAL=A15V, $
    FIELDNAME=SRSD83_VEHICLE_X, ALIAS=SRSD83_VEHICLE_X, USAGE=A100V, ACTUAL=A100V, $
    FIELDNAME=SRSD83_RETAIL_SALES_Q, ALIAS=SRSD83_RETAIL_SALES_Q, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=SRSD83_EXPORTATION_Q, ALIAS=SRSD83_EXPORTATION_Q, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=SRSD83_SRSD39_GROUP_K, ALIAS=SRSD83_SRSD39_GROUP_K, USAGE=P23, ACTUAL=P12, $
    FIELDNAME=SRSD83_ORIGIN_X, ALIAS=SRSD83_ORIGIN_X, USAGE=A5V, ACTUAL=A5V, $
    FIELDNAME=SRSD83_WHOSALE_Q, ALIAS=SRSD83_WHOSALE_Q, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=SRSD83_INI_INV_BRANCH_Q, ALIAS=SRSD83_INI_INV_BRANCH_Q, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=SRSD83_FIN_INV_DEALER_Q, ALIAS=SRSD83_FIN_INV_DEALER_Q, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=SRSD83_SRSD05_CITY_K, ALIAS=SRSD83_SRSD05_CITY_K, USAGE=P23, ACTUAL=P12, $
    FIELDNAME=SRSD83_SRSD03_DEALER_K, ALIAS=SRSD83_SRSD03_DEALER_K, USAGE=P23, ACTUAL=P12, $
    FIELDNAME=SRSD83_CREATED_Y, ALIAS=SRSD83_CREATED_Y, USAGE=HYYMDs, ACTUAL=HYYMDs, $
    FIELDNAME=SRSD83_CREATED_CDSID_C, ALIAS=SRSD83_CREATED_CDSID_C, USAGE=A8V, ACTUAL=A8V, $
    FIELDNAME=SRSD83_PERIOD_R, ALIAS=SRSD83_PERIOD_R, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=SRSD83_REP_TYPE_C, ALIAS=SRSD83_REP_TYPE_C, USAGE=A1, ACTUAL=A1,
      MISSING=ON, $
  SEGMENT=MSRSD01_DIVISON, SEGTYPE=KU, PARENT=MSRSD83_ADV_REPORTS_VENEZUELA, CRFILE=VESIR21815/MSRSD01_DIVISON, CRINCLUDE=ALL,
    JOIN_WHERE=SRSD83_SRSD01_DIVISION_C EQ SRSD01_DIVISION_C;, $
  SEGMENT=MSRSD03_DEALER, SEGTYPE=KU, PARENT=MSRSD83_ADV_REPORTS_VENEZUELA, CRFILE=VESIR21815/MSRSD03_DEALER, CRINCLUDE=ALL, CRJOINTYPE=INNER,
    JOIN_WHERE=SRSD83_SRSD03_DEALER_K EQ SRSD03_IDENTITY_K;, $
  SEGMENT=MSRSD05_CITY, SEGTYPE=KU, PARENT=MSRSD83_ADV_REPORTS_VENEZUELA, CRFILE=VESIR21815/MSRSD05_CITY, CRINCLUDE=ALL, CRJOINTYPE=INNER,
    JOIN_WHERE=SRSD83_SRSD05_CITY_K EQ SRSD05_IDENTITY_K;, $
  SEGMENT=MSRSD10_MANUFACTURER, SEGTYPE=KU, PARENT=MSRSD83_ADV_REPORTS_VENEZUELA, CRFILE=VESIR21815/MSRSD10_MANUFACTURER, CRINCLUDE=ALL, CRJOINTYPE=INNER,
    JOIN_WHERE=SRSD83_SRSD10_MANUFACTURER_C EQ SRSD10_MANUFACTURER_C AND SRSD83_SRSD01_DIVISION_C EQ SRSD10_MSRSD01_DIVISION_C;, $
  SEGMENT=MSRSD39_VEHICHLE_GROUP, SEGTYPE=KU, PARENT=MSRSD83_ADV_REPORTS_VENEZUELA, CRFILE=VESIR21815/MSRSD39_VEHICHLE_GROUP, CRINCLUDE=ALL, CRJOINTYPE=INNER,
    JOIN_WHERE=SRSD83_SRSD39_GROUP_K EQ SRSD39_IDENTITY_K;, $
  SEGMENT=MSRSD37_SUBSEGMENT, SEGTYPE=KU, PARENT=MSRSD83_ADV_REPORTS_VENEZUELA, CRFILE=VESIR21815/MSRSD37_SUBSEGMENT, CRINCLUDE=ALL, CRJOINTYPE=INNER,
    JOIN_WHERE=SRSD83_SRSD37_SUBSEGMENT_K EQ SRSD37_IDENTITY_K;, $

  

acx:

SEGNAME=MSRSD83_ADV_REPORTS_VENEZUELA, 
   TABLENAME=VESIR.dbo.MSRSD83_ADV_REPORTS_VENEZUELA, 
   CONNECTION=VESIR21815, 
   GUI_PARAMS='1,712,0,1092,366,380,366,1', $
  FIELD=SRSD83_IDENTITY_K, 
    AUTOINCREMENT=YES, 
    START=1, 
    INCREMENT=1, $
  FOREIGN_KEY=MSRSD83_SRSD83_SRSD01_FK, 
     PRIMARY_KEY_TABLE=VESIR.dbo.MSRSD01_DIVISON, 
     FOREIGN_KEY_COLUMN=SRSD83_SRSD01_DIVISION_C, 
     PRIMARY_KEY_COLUMN=SRSD01_DIVISION_C, $
  FOREIGN_KEY=MSRSD83_SRSD03_FK, 
     PRIMARY_KEY_TABLE=VESIR.dbo.MSRSD03_DEALER, 
     FOREIGN_KEY_COLUMN=SRSD83_SRSD03_IDENTITY_K, 
     PRIMARY_KEY_COLUMN=SRSD03_IDENTITY_K, $
  FOREIGN_KEY=MSRSD83_SRSD05_FK, 
     PRIMARY_KEY_TABLE=VESIR.dbo.MSRSD05_CITY, 
     FOREIGN_KEY_COLUMN=SRSD83_SRSD05_IDENTITY_K, 
     PRIMARY_KEY_COLUMN=SRSD05_IDENTITY_K, $
  FOREIGN_KEY=MSRSD83_SRSD83_SRSD10_FK, 
     PRIMARY_KEY_TABLE=VESIR.dbo.MSRSD10_MANUFACTURER, 
     FOREIGN_KEY_COLUMN=SRSD83_SRSD10_MANUFACTURER_C, 
     PRIMARY_KEY_COLUMN=SRSD10_MANUFACTURER_C, $
  FOREIGN_KEY=MSRSD83_SRSD83_SRSD37_FK, 
     PRIMARY_KEY_TABLE=VESIR.dbo.MSRSD37_SUBSEGMENT, 
     FOREIGN_KEY_COLUMN=SRSD83_SRSD37_IDENTITY_K, 
     PRIMARY_KEY_COLUMN=SRSD37_IDENTITY_K, $
  FOREIGN_KEY=MSRSD83_SRSD83_SRSD39_FK, 
     PRIMARY_KEY_TABLE=VESIR.dbo.MSRSD39_VEHICHLE_GROUP, 
     FOREIGN_KEY_COLUMN=SRSD83_SRSD39_IDENTITY_K, 
     PRIMARY_KEY_COLUMN=SRSD39_IDENTITY_K, $
 SEGNAME=MSRSD01_DIVISON, 
   GUI_PARAMS='1,67,399,223,519,156,120,1', $
 SEGNAME=MSRSD03_DEALER, 
   GUI_PARAMS='1,330,431,526,721,196,290,1', $
 SEGNAME=MSRSD05_CITY, 
   GUI_PARAMS='1,575,418,773,722,198,305,1', $
 SEGNAME=MSRSD10_MANUFACTURER, 
   GUI_PARAMS='1,809,476,1055,872,246,396,1', $
 SEGNAME=MSRSD39_VEHICHLE_GROUP, 
   GUI_PARAMS='1,1091,414,1297,716,206,302,1', $
 SEGNAME=MSRSD37_SUBSEGMENT, 
   GUI_PARAMS='1,1322,399,1520,723,198,324,1', $
  

Ricardo, which of these segments are FOCUS databases?

Ricardo, thank you for your attempt.It would help me in some way.

Francis, i would follow your idea of creating an entirely new master file and see how it goes.

Agreed with the separate master, its just safer

Using Synonym editor, i created new master that reference's the FOCUS master and SQLMSS master file

mas:
FILENAME=NEWMASTER, $
SEGMENT=SEG01, CRFILE=DATA/FILE1, CRINCLUDE=ALL, $
SEGMENT=EMPDATA, SEGTYPE=KM, PARENT=SEG01, CRFILE=DATA/EMPDATA, CRINCLUDE=ALL,
JOIN_WHERE=SEG01.EMP_ID EQ EMPDATA.EMP_ID;, $

When i try to get sample data, i end up getting a popup saying
"Session lost, agent crashed or killed"

Could not find any details about why it crashed on the server

In my attempt to replicate what you're trying to do, I deleted the "DATASET" attribute from the FOCUS DB master (because of the error: (ACC20201) BOTH DATASET AND ACCESS FILE NOT PERMITTED).

Now the program runs without error, but there are no results.

 TABLE FILE TEST/NEWMAS
 PRINT
 TEST_CALENDAR.BUS_DTE
 BY TEST_CALENDAR.BUS_DTE
 WHERE RECORDLIMIT EQ 100
 END
 BYPASSING TO END OF COMMAND