Query Subject in FM (sql is not executing)

[CognosTalk]

I get a sql error message when creating thsi query subject in the framework manager version 8.2 (UDA-SQL-0357 )

the query is
Select
QUESTION_DIM.ID,
QUESTION_DIM.START_DATE,
QUESTION_DIM.END_DATE,
QUESTION_DIM.CURRENT_INDICATOR,
QUESTION_DIM.SOURCE_ROW_ID,
QUESTION_DIM.DATE_CONFIRMED_DEFICIENCY,
QUESTION_DIM.DEFICIENCY_APPROVED,
QUESTION_DIM.DEFICIENCY_CONFIRMED,
QUESTION_DIM.QUESTION_COMPLIED,
QUESTION_DIM.SOURCE_QUESTION_ID,
QUESTION_DIM.QUESTION_TIMING,
QUESTION_DIM.QUESTION_CODE,
QUESTION_DIM.QUESTION_GROUP,
QUESTION_DIM.QUESTION_GROUP_SEQUENCE,
QUESTION_DIM.GUIDING_QUESTION,
QUESTION_DIM.OPTY_ID,
QUESTION_DIM.ASSET_ID,
QUESTION_DIM.VIOLATION_CATEGORY_DESC,
QUESTION_DIM.COMPLIANCE_TYPE_DESC,
QUESTION_DIM.SOURCE_TRANS_DATE,
QUESTION_DIM.CREATE_DATE,
QUESTION_DIM.UPDATE_DATE,
QUESTION_DIM.LOAD_ID,
QUESTION_DIM.CONSEQUENCE,
QUESTION_DIM.LIKELIHOOD,
QUESTION_DIM.IMPACT,
QUESTION_DIM.DEFICIENCY_RESPONSE_DUE_DATE,
QUESTION_DIM.PROVISION,
( Select
MAX ( BI_ADMIN_LOG.LOADING_TRANS_DATE)
From
[dwqpv212].BI_ADMIN_LOG as BI_ADMIN_LOG

Where BI_ADMIN_LOG.LOADED_TABLE = 'LWIS_STAGING_AREA') as LWIS_LOADING_DATE
From
[dwqpv21].QUESTION_DIM QUESTION_DIM

I tried using different sql types: Cognos, native, or Pass-trough. What am i doing wrong?
similar query executes well in toad.
Thank you very much for your cooperation and support!

 

[blom0344]

Just a guess:
Perhaps you should specify the full path of the query subjects involved.
I suppose query subject dwqpv212 is part of a certain namespace?

Ties Blom

 

[CognosTalk]

Ties, thanks,
Actually it is in the same namespace, it is just different oracle db schema, but point to one connection. One is pointing to the dimension schema, one to log schema, it is done automatically when importing table. i am not sure what is wrong.

 

[blom0344]

check whether this will do the trick:
Create A Synonym For An Object Owned By A Different Schema:

http://www.psoug.org/reference/synonyms.html

Ties Blom

 

[CognosTalk]

Ties,

I selected from Public schema, synonyms and getting the same error

Select
QUESTION_DIM.DEFICIENCY_RESPONSE_DUE_DATE,
( Select MAX ( BI_ADMIN_LOG.LOADING_TRANS_DATE)
From
[dwqpv213].BI_ADMIN_LOG as BI_ADMIN_LOG
Where BI_ADMIN_LOG.LOADED_TABLE = 'LWIS_STAGING_AREA') as LWIS_LOADING_DATE

From
[dwqpv213].QUESTION_DIM as QUESTION_DIM

when changing the sql to pass-trough is complaining about the table name:
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-00903: invalid table name

The point is the one is the log file, and the other is dimensional table. the log file does not have any join points.
What is the best way to implement something like this?
I have to create a calculation in report studio
Deficiency respond_date - loding_date.

Thank you in advance for your support!


when executing the query using cognos sql i am getting the following message:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0357 A syntax error was detected in the SQL request.
<Message Name="CCLMessage" File="udamsgs" Severity="Error" Nesting="34" ><MessageComponents ID="0xaa6df68d" ><MessageParms><MessageParm Name="CCLMessageParm" Type="string" Value="select" /></MessageParms></MessageComponents></Message>

 

[blom0344]

I guess you could create a database view that stores the data from both
QUESTION_DIM and
BI_ADMIN_LOG even if there is no join possible:

select DEFICIENCY_RESPONSE_DUE_DATE,MAX ( BI_ADMIN_LOG.LOADING_TRANS_DATE)  from BI_ADMIN_LOG,QUESTION_DIM
group by DEFICIENCY_RESPONSE_DUE_DATE

which is a cartesian product, but not a problem with your data.
Or write the entire query subject in this fashion..

Ties Blom

 

[CognosTalk]

Ties, i tried to do it , however when creating the query subject and then trying to test, screen freezes and then crushes, not sure how to proceed, i really need to extract this loading date which come from the standalone table from the date that appears on the other regular dimensional table. What to do?

 

[blom0344]

From menu: Project --> Edit governors
Enable the 'cross product joins' in the runtime activities section..

Ties Blom