Cognos 7.2 not working with Informix 10. Help

[twrookie]

Hello,
Please help....some of my reports are not working since we upgraded our database from Informix 7 to 10. Cognos seems having trobule to translate the sql statment. 2 things I notice:
1. the outer join statment
2. it creates 2 select stmt instead of 1. And the 2nd sql doesn't have any where clause so it is doing a sequential search.

Some info about the joins between these tables:
main_tbl.ch_tar_num outer join order_header.release_no
order_header.doc_no join order_detail.doc_no

I have upgraded my ODBC driver but doens't help to resolve the issue.

When the catalog is pointing to Informix 10, I get the query:

select T1.tar_num, T1.serial_num, T1.comp_date, T3.doc_no
from 'root'.main_tbl T1, OUTER('root'.order_header T3)
where T1.ch_tar_num=T3.release_no and T1.comp_date<=DATETIME( 2007-01-15 ) YEAR TO DAY and T1.comp_date>=DATETIME( 2007-01-01 ) YEAR TO DAY and T1.tar_num>=400000
order by 4 asc

select T2.doc_no
from 'root'.order_header T2
order by 1 asc

When I points to Informix 7.0 I get:
select T1.tar_num c1, T1.serial_num c2, T1.comp_date c3, T2.doc_no c4
from 'root'.order_detail T2,
'root'.main_tbl T1 LEFT OUTER JOIN 'root'.stoordre T3 on T1.ch_tar_num=T3.release_no
where T3.doc_no=T2.doc_no
and T1.tar_num>=400000
and T1.comp_date between DATETIME( 2007-01-01 ) YEAR TO DAY and DATETIME( 2007-01-15 ) YEAR TO DAY
order by 1 asc

Any help is appericated.

Thanks,
Tara

 

[xs4citrix]

Cognos is a company, not a product. What are you using?
7.2 is no longer supported, so cognos support cannot help you.
Have you considered installing 7.4 on a testmachine to see how that responds to informix 10?

Stefan

http://www.printingsupport.com

 

[twrookie]

Hi xs4citrix,

Thanks for your reply. It is Impromtu that I'm referring to. Unforunatly, we won't be able to upgrade to 7.4.

What I have found regarding the issue is that Impromtu can't work properly on the thrid table when it is not directly define the join with the first table.

I'm trying a work around by joing a view for table 2 and 3. Then have my 1st table outer join the view table. Hope this will work.

If you can think of any other suggestion, please do so.

Thanks,
Tara