Hi,
I’ve 2 focus files where I’ve written SQL as below and I want to create Master File and Access File using the below SQL dataset. Please suggest

Please let me know what is the suffix and connection details I’ve to provide for respective synonyms(highlighted in yellow) for a FOCUS tables.

CAR1.SQL FILE

 SELECT CAR FROM CAR WHERE COUNTRY = 'ENGLAND' 

CAR1.MAS FILE

 FILENAME=CAR1, SUFFIX=[B][COLOR:YELLOW]??[/COLOR][/B]  , $
 VARIABLE NAME=&&DATASECTOKEN,  $
  SEGMENT=CAR1, SEGTYPE=S0, $
    FIELDNAME=CAR, ALIAS=CAR, USAGE=A20, ACTUAL=A4, FIELDTYPE=I, $
 

CAR1.acx

 SEGNAME=IBD_DET_ENT,
   CONNECTION=[COLOR:YELLOW]??[/COLOR][B]??[/B],
   DATASET=baseapp/ibd_det_ent.sql,
   SUBQUERY=Y, $
 

This message has been edited. Last edited by: <Kathryn Henning>,

Create New Synonym from the associated app folder which has access to the view or SQL based table and go from there.

Doug,
I'm using this Synonym as input for DBA Security master file.
Please let me know how to create Synonym for SQL Dataset

Hi,
Please suggest HOW TO CREATE MASTER FILE USING SQL DATASET OF CAR FILE(ideally using FOCUS file SQL).

I've never seen it accessed it that way.. I've usually made the synonym connect directly to the table or stored proc and use webfocus to do the query or execution with parameters into the proc.

Gavin,
Usually we create Master File for external data set like TEXT, COMT, FIX files. Similarly there is option to create Master File for SQL query. I know to create MasterFile for RDBMS sql file but I'm not sure how to create Synonym for a FOCUS SQL file.

My example on top will work for RDBMS SQL.

I think you mean something like this? (this example uses Oracle):

-* Prepare the SQL statement
SQL SQLORA PREPARE MYSTATEMENT FOR
-INCLUDE CAR1.SQL
;
END

-* Execute the prepared SQL statement
TABLE FILE MYSTATEMENT
PRINT *
END
-RUN

-* To see where the master file(s) were generated:
WHENCE MYSTATEMENT MASTER
WHENCE MYSTATEMENT ACCESS

The master gets generated automatically.

Wep5622,
I know how to create using Oracle or Teradata but I need to create master file using Focus files.