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,

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...

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,

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

Hi

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

Thanks
Gobinath Velusamy