I'm building a new query subject in framework manager. I'm receiving the following error:
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.
This is the query I'm entering on the SQL tab of the Query Subject Definition:
Select
Distinct FUND_ATTRIBUTES.FUND, FUND_ATTRIBUTES.FUND_DESC,
(Select A.ATTRIBUTE_VALUE || ' - ' || A.ATTRIBUTE_VALUE_DESC
from FUND_ATTRIBUTES A
where A.ATTRIBUTE_TYPE = 'HFUNDCLS'
and A.FUND = FUND_ATTRIBUTES.FUND
and A.FUND_STATUS = 'A'
and A.ATTRIBUTE_TYPE_STATUS ='A') FUND_CLASS,
(Select A.ATTRIBUTE_VALUE || ' - ' || A.ATTRIBUTE_VALUE_DESC
from FUND_ATTRIBUTES A
where A.ATTRIBUTE_TYPE = 'HFUNDCAT'
and A.FUND = FUND_ATTRIBUTES.FUND
and A.FUND_STATUS = 'A'
and A.ATTRIBUTE_TYPE_STATUS ='A') FUND_CATEGORY
from
FUND_ATTRIBUTES FUND_ATTRIBUTES
where
(FUND_ATTRIBUTES.ATTRIBUTE_TYPE = 'HFUNDCAT'
or FUND_ATTRIBUTES.ATTRIBUTE_TYPE = 'HFUNDCLS')
and FUND_ATTRIBUTES.FUND_STATUS = 'A'
and FUND_ATTRIBUTES.ATTRIBUTE_TYPE_STATUS ='A'
What is the data type of the ATTRIBUTE_VALUE column in FUND_ATTRIBUTES? Is it by any chance numeric? If so, you will need to cast it as crar or varchar before concatenating it.
If that's not the issue, you might try adding the physical schema name - either to the data source connection in FM or by prefixing each table name with the schema.
Good luck!
MF.
Looks to me a large part of the SQL is redundant. You are generating 2 inline sets of data that are not referenced in the outer part of the query
Thanks all!
It ended up being that I had the SQL options set to Cognos SQL and not Native SQL. After switching that all is well in the world again.