Framework Manager TO_DATE error ....... Oracle Functions Error

[DSR]

Hi All,

Whenever I cerate a relationship between two data Sources Query Subjects (Both are Oracle only,But accessing two databases.).

I am getting Data base function Error.... DS1( Data Source) Query Subject is having Trunc and To_date Functions used in it ...Both the Query subjects are Manually written SQL Queries only....In both SQL's am using #prompt to pass date from the report.

If any one is having any idea .....why am getting this kind of error while executing the SQL after creating the join....Please let me know ........

Note:Can I use two Data Sources From two Different Databases in the same model?

Regards
Sanjeev

[blom0344]

What's your code?
Yes, it is possible to have multiple datasources pointing to different databases in a model, but this may result in the Cognos server performing the join between sets..

[DSR]

Hi,

Thanks for your quick responce....

SQL1:

select #prompt( 'p_date', 'date', '(_add_days(current_date,-1))')#,x...z from ds1.a,ds1.b,ds1.c where to_date(a.date1,'DD-MM-YYYY')=to_date(#prompt( 'p_date', 'date', '(_add_days(current_date,-1))')#,'DD-MM-YYYY')

SQL2:

Second SQL is also some kind of similar SQL where am using only 1 table in the SQL.for satisfying the selected date in the where

Select g.* from ds2.tab1 g where to_date(g.date2,'DD-MM-YYYY')=to_date(#prompt( 'p_date', 'date', '(_add_days(current_date,-1))')#,'DD-MM-YYYY')

am joing both queries on

SQL1.a=SQL2.a
SQL1.b=SQL2.b
SQL1.c=SQL2.c

here in these three joins I don't have any date columns those are only ID's.

Please let me know if you need more explanation than this.