[CLOSED] run sql report via different SQL schema

Hey;

I need to run a report where where statement comes from one Oracle schema and the final select query will apply on another Oracle schema. Can i achieve that in WebFocus?

Thanks

-Maria

This message has been edited. Last edited by: Kerry,

====================
Production: WebFOCUS 7.1.1 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLOGIC 8.1 sp4
Dev: WebFOCUS 7.6.4 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLogic 8.1 sp4

Posts: 52 | Registered: June 28, 2005

Doug

Expert

posted

Hide Post

Yes, I'm sure that can be done in WebFOCUS... However, we (at least I) would need more info on the schemas before providing suggestions on how to accomplish that...

Please advise...

In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206

Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005

Ignored post by Doug posted

Show Post

Francis Mariani

Expert

posted

Hide Post

Maria,

If the two schemas are on the same server you shouldn't have any problem.

Is your program using SQl passthru (are you using select instead of TABLE FILE)?

To help clarify your question why not post some code?

Cheers,

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

Maria Tan

Gold member

posted

Hide Post

Hey;

Thanks for your input.

What i try to applomplish is:

1) Get the rule criteria:

SQL SQLORA PREPARE SQLOUT FOR
select * from rules
;
END
TABLE FILE SQLOUT
PRINT
ROLENAME
CONDITIONLOGIC
ATTRIBUTE
ATTRIBUTEVALUE
HEADING
"Rule Definition"
WHERE ROLENAME EQ '&RoleNm.Select Role Name.';

2) I need to query via another schema to calcalate how many persons got included based on pre-defined rule.

ENGINE SQLORA SET DEFAULT_CONNECTION SRM
SQL SQLORA PREPARE SQLOUT FOR
select * from icsynergy.aps_account where acct_user_id in
(
select attr_user_id from icsynergy.aps_attribute
where
(attr_key = 'location' and attr_value='BKK')
intersect
select attr_user_id from icsynergy.aps_attribute
where (attr_key = 'department' and attr_value='IA')
intersect
select attr_user_id from icsynergy.aps_attribute
where rule = rule-criteria
;
END
SET NODATA = ' '
DEFINE FILE SQLOUT

the number of rule-criteria will vary based on the first query.

for example:
(attr_key = 'employeeGroup' and attr_value='IFN')
(attr_key = 'costCenter' and attr_value ='0016')
)

Regards

-Maria

====================
Production: WebFOCUS 7.1.1 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLOGIC 8.1 sp4
Dev: WebFOCUS 7.6.4 on Window 2003
WebFOCUS 7.1.1 Client on HP-UX on WebLogic 8.1 sp4

Posts: 52 | Registered: June 28, 2005

Ignored post by Maria Tan posted

Show Post

Gobinath Velusamy

Platinum Member

posted

Hide Post

Hi

You can use
SQL SQLORA SET USER &SCHMA; syntax to connect different schemas in the same database .

Thanks
Gobinath Velusamy

WebFOCUS 7610
Windows

Posts: 121 | Registered: September 25, 2007

Ignored post by Gobinath Velusamy posted

Show Post