I know it's possible to capture the SQL generated by WebFOCUS when using a TABLE request directly against a RDBMS, but is it possible to do the reverse. In other words, see how SQL Translator translates SQL commands into WebFOCUS TABLE commands?

I don't see where SQL Translator translates SQL commands into WebFOCUS TABLE command. Aren't we bypassing WebFOCUS when using SQL?

It's my understanding that WebFOCUS translates SQL entered thru the SQL Translator (hence the name) into TABLE commands because the data source is actually a FOCUS table.

SQL
SELECT *
FROM ANY_FOC_TABLE
WHERE ...
END

Yep, you're right.

SQL
SELECT * 
FROM NEW_PROJECT 
WHERE PRJ_PROJECT_ID = 'N0110';
FILE XYZ
END
-RUN
DOS TYPE XYZ.FEX
-EXIT

Here is the table request created

SET COUNTWIDTH=ON                                                               
-SET SQLERRNUM = 0;                                                             
DEFINE FILE                                                                     
NEW_PROJECT TEMP                                                                
SQLDEF01/YYMD MISSING ON NEEDS ALL = PRJ_PLAN_START;                            
SQLDEF02/YYMD MISSING ON NEEDS ALL = PRJ_PLAN_FINISH;                           
SQLDEF03/YYMD MISSING ON NEEDS ALL = PRJ_ACTUAL_START;                          
SQLDEF04/YYMD MISSING ON NEEDS ALL = PRJ_ACTUAL_FINISH;                         
SQLDEF05/YYMD MISSING ON NEEDS ALL = PRJ_FROZEN_DATE;                           
SQLDEF06/YYMD MISSING ON NEEDS ALL = PRJ_CLOSE_DATE;                            
SQLDEF07/YYMD MISSING ON NEEDS ALL = PRJ_CANCEL_DATE;                           
SQLDEF08/YYMD MISSING ON NEEDS ALL = PRJ_APPROVED_DATE;                         
END                                                                             
TABLEF FILE NEW_PROJECT                                                         
PRINT FOCLIST PRJ_PROJECT_ID PRJ_NAME PRJ_STATE PRJ_OBJVERSION PRJ_ACCESS_ON_OFF
   PRJ_DESCRIPTION SQLDEF01 AS 'PRJ_PLAN_START' SQLDEF02 AS 'PRJ_PLAN_FINISH'   
  SQLDEF03 AS 'PRJ_ACTUAL_START' SQLDEF04 AS 'PRJ_ACTUAL_FINISH' SQLDEF05 AS    
  'PRJ_FROZEN_DATE' SQLDEF06 AS 'PRJ_CLOSE_DATE' SQLDEF07 AS 'PRJ_CANCEL_DATE'  
  SQLDEF08 AS 'PRJ_APPROVED_DATE' PRJ_MANAGER PRJ_CUSTOMER_NO                   
  PRJ_CUSTOMER_PROJECT_ID PRJ_CALENDAR_ID PRJ_PROGRAM_ID PRJ_CURRENCY_TYPE      
  PRJ_PROBABILITY_TO_WIN PRJ_CUSTOMER_ORDER_ID                                  
 WHERE ( PRJ_PROJECT_ID EQ 'N0110' ) ;                                          
ON TABLE SET CARTESIAN ON                                                       
ON TABLE SET ASNAMES ON                                                         
ON TABLE SET HOLDLIST PRINTONLY                                                 
                                                                                
END                                                                             
-RUN                                                                            
-SET SQLERRNUM = &FOCERRNUM;                                                    
DEFINE FILE                                                                     
NEW_PROJECT RESTORE                                                             
END                                                                             
-IF &SQLERRNUM GT 0 GOTO SQLEXT01;                                              
-SET SQLERRNUM = &FOCERRNUM;                                                    
-IF &RETCODE   GT 1 GOTO SQLEXT01;                                              
-IF &RETCODE   LT 0 GOTO SQLEXT01;                                              
-IF &FOCERRNUM GT 0 GOTO SQLEXT01;                                              
-SQLEXT01                                                                       
-IF &SQLERRNUM GT 0 GOTO SQLFIN;                                                
-SQLFIN                                                                         
POSTSTAT FOCERRNUM &SQLERRNUM                                                   
-* End of SQLTRANS Generated FOCEXEC                                            

You can enable traces via WF Server Console > Diagnostics > Traces > Enable Traces.

Run the SQL SELECT statement and then take a look at the trace file from the same window as above.

Among the hundreds of lines generated by the trace are these juicy tidbits:

SQL SELECT * FROM CAR

generates this:

sqtrn2: SQL from manipulated parse tree
sqtrn2: SELECT * FROM CAR  ;
...
sqtrn2: SQL from J-Tree
sqtrn2: SELECT * FROM CAR  ;
...
sqtrn2: SQL After View Substitution
sqtrn2: SELECT CAR.COUNTRY , CAR.CAR , CAR.MODEL , CAR.BODYTYPE , CAR.SEATS ,
sqtrn2:  CAR.DEALER_COST , CAR.RETAIL_COST , CAR.SALES , CAR.LENGTH , CAR
sqtrn2: .WIDTH , CAR.HEIGHT , CAR.WEIGHT , CAR.WHEELBASE , CAR.FUEL_CAP , CAR
sqtrn2: .BHP , CAR.RPM , CAR.MPG , CAR.ACCEL , CAR.WARRANTY , CAR.STANDARD
sqtrn2:  FROM CAR ;
...
sqtrn2: Fully qualified SQL
sqtrn2: SELECT SQLCOR01.COUNTRY , SQLCOR01.CAR , SQLCOR01.MODEL , SQLCOR01
sqtrn2: .BODYTYPE , SQLCOR01.SEATS , SQLCOR01.DEALER_COST , SQLCOR01.RETAIL
sqtrn2: _COST , SQLCOR01.SALES , SQLCOR01.LENGTH , SQLCOR01.WIDTH , SQLCOR01
sqtrn2: .HEIGHT , SQLCOR01.WEIGHT , SQLCOR01.WHEELBASE , SQLCOR01.FUEL_CAP ,
sqtrn2:  SQLCOR01.BHP , SQLCOR01.RPM , SQLCOR01.MPG , SQLCOR01.ACCEL ,
sqtrn2:  SQLCOR01.WARRANTY , SQLCOR01.STANDARD  FROM CAR SQLCOR01 ;
...
prput: line is 80 bytes, line:
TABLE FILE CAR

message length = 80
prput: line is 80 bytes, line:
PRINT COUNTRY CAR MODEL BODYTYPE SEATS DEALER_COST RETAIL_COST SALES LENGTH

message length = 80
prput: line is 80 bytes, line:
WIDTH HEIGHT WEIGHT WHEELBASE FUEL_CAP BHP RPM MPG ACCEL WARRANTY STANDARD
...
prput: line is 80 bytes, line:
ON TABLE SET CARTESIAN ON

message length = 80
prput: line is 80 bytes, line:
ON TABLE SET ASNAMES ON

message length = 80
prput: line is 80 bytes, line:
ON TABLE SET HOLDLIST PRINTONLY

message length = 80
prput: line is 80 bytes, line:
END

Note that turning traces on with the method above turns it on for all requests. There's probably a method of turning on traces from within the fex so that only that fex is traced.

John's method is nice and clean.

Use the following to get the table file syntax that was generated. Keep in mind that this is taking the SQL and converting it to focus and will do this regardless of data type.

SET TRACEOFF=ALL
SET TRACEON=SQLTRANS/1/CLIENT
SET TRACEUSER=ON


SQL passthru will not translate to focus since you are telling it to ignore the sql. SQL passthru only happens if you specifically state the engine such as

SQL SQLMSS
select * from Northwind.dbo.orders;
END

The following would be translated as it would need to read the master file description and would then be retranslated back to SQL to pass on to the RDBS.
SQL
select * from ORDERS;
END

You can use the following to get the FOCUS to SQL translation.

SET TRACEOFF=ALL
SET TRACEON=STMTRACE//CLIENT
SET TRACEUSER=ON

Thanks everyone, that will come in very handy.