Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Join SQLMSS Table inside a Focus Master file
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Join SQLMSS Table inside a Focus Master file
 Login/Join
 
Platinum Member
posted
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,


WebFOCUS
7703/7705/8105m/8201m/8202m

 
Posts: 100 | Registered: September 10, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
You do it the same way.

Its in the documentation.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6253 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Waz,

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


WebFOCUS
7703/7705/8105m/8201m/8202m

 
Posts: 100 | Registered: September 10, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
? 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


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, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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


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, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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,


WebFOCUS
7703/7705/8105m/8201m/8202m

 
Posts: 100 | Registered: September 10, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
Can you try using Data Management Console?


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
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.


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, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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,


WebFOCUS
7703/7705/8105m/8201m/8202m

 
Posts: 100 | Registered: September 10, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
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.


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, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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.


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, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
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', $
  


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Ricardo, which of these segments are FOCUS databases?


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, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WebFOCUS
7703/7705/8105m/8201m/8202m

 
Posts: 100 | Registered: September 10, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
Agreed with the separate master, its just safer


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6253 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


WebFOCUS
7703/7705/8105m/8201m/8202m

 
Posts: 100 | Registered: September 10, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
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


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, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Join SQLMSS Table inside a Focus Master file

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.