Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL code as Focexec query

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] SQL code as Focexec query
 Login/Join
 
Master
posted
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. Confused

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,


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Platinum Member
posted Hide Post
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.


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Member
posted Hide Post
Hello PRodrigu

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
 
Posts: 5 | Registered: June 02, 2011Report This Post
Master
posted Hide Post
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)


and it displays in a report as 22-JAN-11.


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Gold member
posted Hide Post
Tomsweb,

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
 
Posts: 81 | Location: Monroe LA | Registered: January 07, 2005Report This Post
Gold member
posted Hide Post
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.


WebFOCUS 7.7.02
Unix
All Output
 
Posts: 70 | Registered: March 24, 2011Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL code as Focexec query

Copyright © 1996-2020 Information Builders