I was given the code captioned below from a user. Regretably, my SQL experience is practically zero. These tables are not defined in WebFOCUS.
I wanted to ask for any advice on whether this code code could be translated into a WebFOCUS report request.
Here is the code:
quote:
SELECT INST_ASET.ASET_ACTV_IND, INST_ASET.ASET_ID, sum(INST_ASET.ASET_SMRY_CNT), sum(INST_ASET.ASET_PRIN_NET_BAL_AMT), INST_ASET_DESC.ASET_METAVANTE_MGT_CL_CDE_VAL, INST_ASET_TYP.INST_ASET_TYP_GRP_ID FROM INST_ASET, INST_ASET_DESC, INST_ASET_TYP WHERE ( INST_ASET_DESC.INST_ASET_UNIQ_ID(+)=INST_ASET.INST_ASET_UNIQ_ID ) AND ( INST_ASET.INST_ASET_TYP_UNIQ_ID=INST_ASET_TYP.INST_ASET_TYP_UNIQ_ID(+) ) AND ( ( (INST_ASET.SYS_ACTV_DTTM <= @prompt['Enter Asset As Of Date','D',,Mono,Free,Persistent,,User:0) and INST_ASET.SYS_REPL_DTTM > @prompt('Enter Asset As Of Date','D',,Mono,Free,Persistent,,User:0)) ) AND INST_ASET_TYP.INST_ASET_TYP_GRP_ID = '096' AND INST_ASET.ASET_ACTV_IND = 'Y' ) GROUP BY INST_ASET.ASET_ACTV_IND, INST_ASET.ASET_ID, INST_ASET_DESC.ASET_METAVANTE_MGT_CL_CDE_VAL, INST_ASET_TYP.INST_ASET_TYP_GRP_ID
Any insights or direction is appreciated!
Thanks,This message has been edited. Last edited by: Kerry,
Just pass the SQL as it is to WebFOCUS. Just need to make sure the database connection (data adapter) you will be using is set up in the proper environment.
ENGINE SQLORA SET VARCHAR OFF ENGINE SQLORA SET DEFAULT_CONNECTION database connection (example for where I work its dev07 or db07) ENGINE SQLORA
SELECT INST_ASET.ASET_ACTV_IND, INST_ASET.ASET_ID, sum(INST_ASET.ASET_SMRY_CNT), sum(INST_ASET.ASET_PRIN_NET_BAL_AMT), INST_ASET_DESC.ASET_METAVANTE_MGT_CL_CDE_VAL, INST_ASET_TYP.INST_ASET_TYP_GRP_ID FROM INST_ASET, INST_ASET_DESC, INST_ASET_TYP WHERE ( INST_ASET_DESC.INST_ASET_UNIQ_ID(+)=INST_ASET.INST_ASET_UNIQ_ID ) AND ( INST_ASET.INST_ASET_TYP_UNIQ_ID=INST_ASET_TYP.INST_ASET_TYP_UNIQ_ID(+) ) AND ( ( (INST_ASET.SYS_ACTV_DTTM <= @prompt['Enter Asset As Of Date','D',,Mono,Free,Persistent,,User:0) and INST_ASET.SYS_REPL_DTTM > @prompt('Enter Asset As Of Date','D',,Mono,Free,Persistent,,User:0)) ) AND INST_ASET_TYP.INST_ASET_TYP_GRP_ID = '096' AND INST_ASET.ASET_ACTV_IND = 'Y' ) GROUP BY INST_ASET.ASET_ACTV_IND, INST_ASET.ASET_ID, INST_ASET_DESC.ASET_METAVANTE_MGT_CL_CDE_VAL, INST_ASET_TYP.INST_ASET_TYP_GRP_ID;
TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS INST_ASET END -RUN
What format is the date field (SYS_ACTV_DTTM)? You might need to change the logic there on how the date is being passed.
here is a very short fex on how you can use you sql pass thru. You will have to change your sql for what you have and modified the date display to howerer you want to see it.
ENGINE SQLMSS SET DEFAULT_CONNECTION WFREPORTDATASRV SQL SQLMSS PREPARE SQLOUT FOR select * from p_Phys where cc_fac_no = 3322 END TABLE FILE SQLOUT PRINT cc_phys_no created_user_id created_date_time lastname firstname mi HEADING "HEADING" FOOTING "FOOTING" ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, $ TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, $ TYPE=TITLE, STYLE=BOLD, $ TYPE=TABHEADING, SIZE=12, STYLE=BOLD, $ TYPE=TABFOOTING, SIZE=12, STYLE=BOLD, $ TYPE=HEADING, SIZE=12, STYLE=BOLD, $ TYPE=FOOTING, SIZE=12, STYLE=BOLD, $ TYPE=SUBHEAD, SIZE=10, STYLE=BOLD, $ TYPE=SUBFOOT, SIZE=10, STYLE=BOLD, $ TYPE=SUBTOTAL, BACKCOLOR=RGB(210 210 210), $ TYPE=ACROSSVALUE, SIZE=9, $ TYPE=ACROSSTITLE, STYLE=BOLD, $ TYPE=GRANDTOTAL, BACKCOLOR=RGB(210 210 210), STYLE=BOLD, $ ENDSTYLE END
7610, Windows 64, Win 7 Excel, PDF, HTML
June 09, 2011, 10:16 AM
Tomsweb
Thanks prodrigu and MLM for your help. The date code I've been given is:
quote:
where SYS_ACTV_DTTM=to_date('19-APR-11','DD-MON-YY)
Your select columns don't seem to contain a date column, unless you have added one?
The WHERE date column doesn't print on the report. If you include it in the SELECT columns it should print correctly.
You can change the date output format easily.
--wg
WF 8009m, Clustered vm Windows2008r2 reporting servers; Web interface: tomcat; Output: EXCEL, HTML, PDF; dbms: Oracle 10, db2 on mvs, mssql
June 09, 2011, 05:38 PM
MCKELPA99
First, you can define these tables in WebFOCUS by creating master and acx files for these tables. Then, you can either query the WebFOCUS master files directly and try to achieve what this SQL query is doing or you can use this SQL code in WebFOCUS SQL passthru such as:
ENGINE SQLORA SET DEFAULT_CONNECTION (connection string goes here) SQL SQLORA PREPARE SQLOUT FOR SELECT INST_ASET.ASET_ACTV_IND, INST_ASET.ASET_ID, sum(INST_ASET.ASET_SMRY_CNT), sum(INST_ASET.ASET_PRIN_NET_BAL_AMT), INST_ASET_DESC.ASET_METAVANTE_MGT_CL_CDE_VAL, INST_ASET_TYP.INST_ASET_TYP_GRP_ID FROM INST_ASET, INST_ASET_DESC, INST_ASET_TYP WHERE ( INST_ASET_DESC.INST_ASET_UNIQ_ID(+)=INST_ASET.INST_ASET_UNIQ_ID ) AND ( INST_ASET.INST_ASET_TYP_UNIQ_ID=INST_ASET_TYP.INST_ASET_TYP_UNIQ_ID(+) ) AND ( ( (INST_ASET.SYS_ACTV_DTTM <= @prompt['Enter Asset As Of Date','D',,Mono,Free,Persistent,,User:0) and INST_ASET.SYS_REPL_DTTM > @prompt('Enter Asset As Of Date','D',,Mono,Free,Persistent,,User:0)) ) AND INST_ASET_TYP.INST_ASET_TYP_GRP_ID = '096' AND INST_ASET.ASET_ACTV_IND = 'Y' ) GROUP BY INST_ASET.ASET_ACTV_IND, INST_ASET.ASET_ID, INST_ASET_DESC.ASET_METAVANTE_MGT_CL_CDE_VAL, INST_ASET_TYP.INST_ASET_TYP_GRP_ID ; END -RUN
Next, you would do:
TABLE FILE SQLOUT and print your fields for your report with whatever styling and format you wanted if this is all you needed to do.