What is in this SP?

[grnzbra]

I have an Access database that has many queries driving a report. One query uses two tables. My job is to replace these two local tables with queries based on Oracle back end tables.

One of the back end "tables" is a stored procedure. I have a report that contains a subreport. When using the query that uses the query referencing the two table, the report works fine. When I use the query containing the SP, I get a message that tells me that I cannot use a Pass Through query or Non-Fixed_column Crosstab query as a record source for a subform or subreport. If I remove the SP from the query and replace the field that comes from the SP with a 0, the report/subreport combination works fine. If I put the SP back, even if I don't select any fields from it, I get the error message and the subreport section of the report remains blank. The following is the SQL for the SP (as seen by Access in the design mode):

select dl_id,
GetColtDetails.GetAdditionalBorrAllowed(dl_id) AdditionalBorrAllowed,
Getsecfacevalamt(dl_id) Secfacevalamt,
GetColtDetails.GetCurrColtAmtForDeal(dl_id) CurrColtAmt
from structured_facility


Is this some kind of crosstab query? Or is a query that references an SP considered to be a pass through query?

 

[carp]

This does not look like an SP. It looks more like a view. And without knowing what the functions referenced in the select statement do, we have no way of telling you what it's doing.

 

[grnzbra]

Thanks. I can't see the back end db, I have no idea what the referenced functions are. I only see this in the design view as an Access query. Since its name starts with sp, I assumed it was a stored procedure.

 

[carp]

All three of the columns in the query are supplied by calls to functions. So you will need to find somebody with access to these functions that can tell you how they operate and what they are providing.
Good luck!