This code does not work (Note the case expression):

SQL
select count(*) as row_cnt,
prfl_run_key as prfl_run_key,
function_key as function_key,
col1_value as col1_value,
col2_value as col2_value,
col3_value as col3_value,
master_row_flag as master_row_flag,
CASE
WHEN col1_value is null then 'Y'
ELSE 'N' END AS null_flag
From dpr_prfl_oj_fn_verbose
group by
prfl_run_key,
function_key,
col1_value,
col2_value,
col3_value,
master_row_flag;
TABLE FILE SQLOUT
ON TABLE HOLD AS ORFKEYS
PRINT *
END

The error is as follows:
0 ERROR AT OR NEAR LINE 30 IN PROCEDURE FOCSQL01FOCEXEC *
(FOC012) THE WORD 'FILE' OR THE FILENAME APPEARS TWICE
BYPASSING TO END OF COMMAND


This code brings back rows in the browser:

SQL
select count(*) as row_cnt,
prfl_run_key as prfl_run_key,
function_key as function_key,
col1_value as col1_value,
col2_value as col2_value,
col3_value as col3_value,
master_row_flag as master_row_flag,
CASE
WHEN col1_value is null then 'Y'
ELSE 'N' END AS null_flag
From dpr_prfl_oj_fn_verbose
group by
prfl_run_key,
function_key,
col1_value,
col2_value,
col3_value,
master_row_flag;



This code actually saves the file but does not include the case:

SQL

select count(*) as row_cnt,
prfl_run_key as prfl_run_key,
function_key as function_key,
col1_value as col1_value,
col2_value as col2_value,
col3_value as col3_value
From dpr_prfl_oj_fn_verbose
group by
prfl_run_key,
function_key,
col1_value,
col2_value,
col3_value,
master_row_flag;
TABLE FILE SQLOUT
ON TABLE HOLD AS ORFKEYS
PRINT *
END

Can anyone shed any light on this? I can pass the query I am sure directly to the database, but I would prefer to use the metadata if possible.

Change the first instance to this:

SQL
select count(*) as row_cnt,
prfl_run_key as prfl_run_key,
function_key as function_key,
col1_value as col1_value,
col2_value as col2_value,
col3_value as col3_value,
master_row_flag as master_row_flag,
CASE
WHEN col1_value is null then 'Y'
ELSE 'N' END AS null_flag
From dpr_prfl_oj_fn_verbose
group by
prfl_run_key,
function_key,
col1_value,
col2_value,
col3_value,
master_row_flag;
TABLE
ON TABLE HOLD AS ORFKEYS
END

TABLE FILE ORFKEYS
PRINT *
END

You shouldn't (or can't, not sure here) do a ON TABLE HOLD before a print. Also, the FILE SQLOUT is only used when executing sql stored procedures.

As far as I know all SQL passthroughs use SQLOUT, stored procedures or not.

This is not a sql pass through query. It is using the web focus sql engine to execute the code through the metadata that exists. If it were pass through, it would explicitly use the following syntax:

Note the key word oracle and the reference to the database instance INFADEV:

SQL SQLORA SET SERVER INFADEV
SQL SQLORA
SELECT
CASE WHEN SOURCE_FIELD_NUMBER <8 THEN 7
ELSE
SOURCE_FIELD_NUMBER END AS MAXID,
T2.PRFL_RUN_KEY,
t3.prfl_ver_key,
t3.function_key,
T3.SOURCE_NAME1,
T3.COLNAME1
FROM infarep.rep_all_source_flds T1,PROFILER.DPR_PRFL_RUN_DTLS T2, PROFILER.DPR_PRFL_COL_FN T3
WHERE T1.SUBJECT_AREA=T2.PRFL_FOLDER_NAME AND
T2.PRFL_VER_KEY=T3.PRFL_VER_KEY AND
T1.source_name=T3.source_NAME1 AND
T1.source_field_NAME =T3.COLNAME1 AND
T2.PRFL_RUN_KEY=&prf_rnkey AND
T3.FUNCTION_KEY=&prf_fnkey;
TABLE FILE SQLOUT
PRINT *
ON TABLE SAVE AS MAXCOL
END