I am attempting to query a database catalog table to generate sql on the fly and then re execute it. I know I can do this in SAS, but I am unclear how to do the same thing in Web Focus. Has anyone attempted to do this before? In SAS the query fields are written out to a file and then read back in using an include statement. An example would be appreciated.

For WebFocus it's exactly the same.

CHECK FILE filename HOLD
Generates a file containing all of the details aboult WebFocus Metadata

So as an example

CHECK FILE CAR HOLD
TABLE FILE HOLD
PRINT FIELDNAME
END

How you play with the fieldnames afterwards is up to your creativity

SQL SQLORA SET SERVER INFADEV
SQL SQLORA
SELECT DYN_SELECT FROM
[SELECT
CASE WHEN T1.COLUMN_NUMBER =1 THEN
'SELECT '||T1.COLUMN_NAME||','
WHEN T1.COLUMN_NUMBER<T4.MAXID THEN
T1.COLUMN_NAME||','
ELSE
'FROM '||T1.TABLE_NAME
END AS DYN_SELECT,
T1.COLUMN_NUMBER,
T4.MAXID
FROM REP_TARG_TBL_COLS T1, PROFILER.DPR_PRFL_RUN_DTLS T2,
PROFILER.DPR_PRFL_OJ_FN T3,
[SELECT MAX [COLUMN_NUMBER] AS MAXID
FROM REP_TARG_TBL_COLS T1, PROFILER.DPR_PRFL_RUN_DTLS T2,
PROFILER.DPR_PRFL_OJ_FN T3
WHERE T1.SUBJECT_AREA=T2.PRFL_FOLDER_NAME AND
T2.PRFL_VER_KEY=T3.PRFL_VER_KEY AND
T1.TABLE_NAME=T3.SOURCE_NAME2 AND
T2.PRFL_RUN_KEY=88] T4
WHERE T1.SUBJECT_AREA=T2.PRFL_FOLDER_NAME AND
T2.PRFL_VER_KEY=T3.PRFL_VER_KEY AND
T1.TABLE_NAME=T3.SOURCE_NAME2 AND
T2.PRFL_RUN_KEY=88
ORDER BY T1.COLUMN_NUMBER]
UNION ALL
SELECT 'WHERE;' FROM DUAL;

I used square brackets because it would not allow me to use parentheis

The previous code creates a query, I can use to execute as a passthrough query to the oracle instance. I would like to write the query output out and read it back in to use in the procedure to execute another pass through query to the database.

Kee,

Ah, it's good to see the old "Let's use SQL to generate SQL" trick . The technique to do include generated code with WebFOCUS is basically the same as you were doing in SAS, only the syntax is different. Here's a rough outline:

FILEDEF myfile DISK myfile.fex
SQL SQLORA connect-string
SQL SQLORA
-* Here is your SQL generating SQL
;
TABLE FILE SQLOUT
PRINT *
ON TABLE SAVE AS MYFILE FORMAT ALPHA
END
-RUN
SQL SQLORA
-INCLUDE MYFILE
;
TABLE FILE SQLOUT
-* Now issue a nice looking report
END

The only "trick" here is that when you do your FILEDEF, it has to have .fex on the end of the filename; this way FOCUS recognizes your file as a FOCUS procedure that can be included.


BTW, if you're coming over from the SAS world, you'll find that FOCUS Dialogue Manager can do everything that the SAS Macros can do - and a lot more.

-* File profile_oj_source.fex
FILEDEF TBLCOLS DISK /app/ibi_dev/ibi/srv52/wfs/edatemp/tblcols6.fex
SQL SQLORA SET SERVER INFADEV
SQL SQLORA
SELECT DYN_SELECT FROM
[select CASE WHEN T1.source_field_number =1
THEN 'SELECT '||T1.source_field_name||','
WHEN T1.source_field_number<T4.MAXID
THEN T1.source_field_name||','
WHEN T1.source_field_number=T4.MAXID
THEN T1.source_field_name||' FROM '||T1.source_name
END AS DYN_SELECT,
t1.source_field_number,
T2.PRFL_RUN_KEY,
T4.MAXID
from metarep.rep_all_source_flds t1,
PROFILER.DPR_PRFL_RUN_DTLS T2,
PROFILER.DPR_PRFL_OJ_FN T3,
[SELECT SOURCE_FIELD_NUMBER AS MAXID,T2.PRFL_RUN_KEY
FROM metarep.rep_all_source_flds T1, PROFILER.DPR_PRFL_RUN_DTLS T2,
PROFILER.PMDP_OUTER_JOIN T3
WHERE T1.SUBJECT_AREA=T2.PRFL_FOLDER_NAME AND
T2.PRFL_VER_KEY=T3.PROFILE_VERSION_KEY AND
T1.source_name=T3.DETAIL_SRC_NAME
AND T1.source_field_NAME =T3.DETAIL_COLNAME_1] T4
WHERE T1.SUBJECT_AREA=T2.PRFL_FOLDER_NAME AND
T2.PRFL_VER_KEY=T3.PRFL_VER_KEY AND
T1.source_name=T3.SOURCE_NAME2 AND
T2.PRFL_RUN_KEY=&prf_rnkey and
t1.source_field_number <=T4.MAXID and
T2.PRFL_RUN_KEY=T4.PRFL_RUN_KEY
ORDER BY T1.source_field_number];
TABLE FILE SQLOUT
ON TABLE SAVE AS TBLCOLS
PRINT *
END
SQL SQLORA SET SERVER LNDDEV
SQL SQLORA
-INCLUDE /app/ibi_dev/ibi/srv52/wfs/edatemp/tblcols6.fex
WHERE &prf_colname='&prf_code';
TABLE FILE SQLOUT
PRINT *
ON TABLE SUBHEAD
"Informatica Profiler Run Details"
"As of: <+0>&DATEMDYY <+0> "
HEADING
""
""
"ORPHAN ROWS"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT EXL2K
-*ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-INCLUDE reptsty2.sty
END

I executed the above code in webfocus. Everytime on the first execution, it errors and then when I refresh the browset it finds the file. Also whenever I query for another piece of sql to the same file, it keeps the previous version until I execute again, any suggestions?

Kee,

Try adding a -RUN after the TABLE FILE SQLOUT, like this:

TABLE FILE SQLOUT
ON TABLE SAVE AS TBLCOLS
PRINT *
END
-RUN

Also, I notice that you seem to be allocating your file permanently. This may or may not be the desired effect. For example, let's say something goes wrong with your first query which generates the SQL. The second query will still run because tblcols6.fex is still hanging around from the prior run (unless you explicitly delete it later on in your program). If this is what you want, then leave your program as is.

Alternatively, you can try doing your FILEDEF and -INCLUDE like this:

FILEDEF TBLCOLS DISK tblcols6.fex
-* etc
-* etc
-INCLUDE TBCOLS

With this approach, WebFOCUS will create the file in a temporary directory which only exists for the duration of your program.

I have tried the temporary allocation, but web focus cannot find the file. That is why you see the code the way it is.

You should try adding a -RUN immediately after the FILEDEF, it looks like the stack is running out of sequence.

That should also resolve the use of a temp file.

Another thing that sometimes occurs is that you need to explicitly FILEDEF to the temp directory, try as follows

FILEDEF TBLCOLS DISK ./tbcols.fex
-RUN

Kee,

Regardless of the FILEDEF issue, did inserting a -RUN solve your problem?

Eric

I have a new problem, I was able to solve the problem of creating the file dynamically and reuse it, but now I have moved the code to another program so it executes first before I get to one of my drill down reports. It works fine as long as I don't try to generate another SQL file to the same physical location, when I do, for some reason web focus will not overwrite the file. What can I do to make sure that web focus runs my report everytime instead of caching it to an html page. It is interesting to note when I dumped the internet explorer cache, it rexecuted and created the, the new file. The program is in a sequence of drill down reports and looks like this:

-* File profile_run_detls.fex
FILEDEF TBLCOLS DISK /app/ibi_dev/ibi/srv52/wfs/edatemp/tblcols0.fex
-RUN
SQL SQLORA SET SERVER INFADEV
SQL SQLORA
SELECT DYN_SELECT FROM
[select CASE WHEN T1.source_field_number =1
THEN 'SELECT '||T1.source_field_name||','
WHEN T1.source_field_number<T4.MAXID
THEN T1.source_field_name||','
WHEN T1.source_field_number=T4.MAXID
THEN T1.source_field_name||' FROM '||T1.source_name
END AS DYN_SELECT,
t1.source_field_number,
T2.PRFL_RUN_KEY,
T4.MAXID
from metarep.rep_all_source_flds t1,
PROFILER.DPR_PRFL_RUN_DTLS T2,
PROFILER.DPR_PRFL_OJ_FN T3,
[SELECT SOURCE_FIELD_NUMBER AS MAXID,T2.PRFL_RUN_KEY
FROM metarep.rep_all_source_flds T1, PROFILER.DPR_PRFL_RUN_DTLS T2,
PROFILER.PMDP_OUTER_JOIN T3
WHERE T1.SUBJECT_AREA=T2.PRFL_FOLDER_NAME AND
T2.PRFL_VER_KEY=T3.PROFILE_VERSION_KEY AND
T1.source_name=T3.DETAIL_SRC_NAME
AND T1.source_field_NAME =T3.DETAIL_COLNAME_1] T4
WHERE T1.SUBJECT_AREA=T2.PRFL_FOLDER_NAME AND
T2.PRFL_VER_KEY=T3.PRFL_VER_KEY AND
T1.source_name=T3.SOURCE_NAME2 AND
T2.PRFL_RUN_KEY=&prf_rnkey and
t1.source_field_number <=T4.MAXID and
T2.PRFL_RUN_KEY=T4.PRFL_RUN_KEY
ORDER BY T1.source_field_number];
TABLE FILE SQLOUT
ON TABLE SAVE AS TBLCOLS
PRINT *
END
-RUN
-*SQL
-*SELECT T1.PRFL_RUN_DT,
-*T1.PROFILE_NAME,
-*T1.PRFL_FOLDER_NAME,
-*T1.PROFILE_ID,
-*T1.PRFL_RUN_KEY,
-*T1.PRFL_VER_KEY,
-*T1.PRFL_RUN_STATUS,
-*T2.FUNCTION_TYPE
-*FROM DPR_PRFL_RUN_DTLS T1, DPR_PRFL_FN_DTLS T2
-*WHERE T1.PRFL_VER_KEY=T2.PRFL_VER_KEY;
-*TABLE FILE SQLOUT
-*ON TABLE HOLD AS RUN_DTLS
-*PRINT *
-*END
TABLE FILE DPR_PRFL_RUN_DTLS
PRINT
PRFL_RUN_DT AS 'Profile Run Date'
BY
PROFILE_NAME AS 'Profile Name'
BY
PRFL_FOLDER_NAME AS 'Profile Folder Name'
BY
PROFILE_ID AS 'Profile Id'
BY
PRFL_RUN_KEY AS 'Profile Run Key'
BY
PRFL_VER_KEY AS 'Profile,Version,Key'
BY
PRFL_RUN_STATUS AS 'Profile,Run,Status'

ON TABLE SUBHEAD
"Informatica Profiler Run Details"
"As of: <+0>&DATEMDYY <+0> "
HEADING
" "
" "
"Run Names"
WHERE ( PROFILE_NAME EQ '&prf_name' ) AND ( PROFILE_ID EQ &prf_id ) AND ( PRFL_RUN_STATUS EQ '&prf_status' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-INCLUDE reptsty2.sty
TYPE=DATA,
COLUMN=N4,
DRILLMENUITEM='Orphan Analysis',
FOCEXEC=PROFILE_ORPHAN_DTLS_KZ[prf_rnkey=N4],
$
TYPE=TITLE,
COLUMN=N4,
DRILLMENUITEM='Orphan Analysis',
FOCEXEC=PROFILE_ORPHAN_DTLS_KZ[prf_rnkey=N4],
$
TYPE=REPORT,
COLUMN=N7,
SQUEEZE=OFF,
$
ENDSTYLE
END
-RUN

clearing the cache did not solve the problem.

First thing to check are your browser settings.
Make sure that you have "every visit to the page" selected in your settings.
IE controls what action it takes if this is not set correctly.

boy do I feel dumb, the parameter prf_rnkey was not being satisfied, so the sql statement never ran unless I ran it stand alone. I move the code to the next program in the drill down sequence, and surprise, since it had the drill down parameter of prf_rnkey passed to it, it then executed correctly. Thanks for all your help.

kz