Upgrading SQL Based Report stops working in Impromptu 7.1

[ashishh]

I have this strange problem. This report (SQL Query Based) worked perfectly fine with version 7.0.702.
When I upgrade the report and catalog to 7.1.116.11, the report gives strange errors in prompt variables. Could someone please help.

Attached is the exact SQL
********
select T2.SR_NO_TXT, T2.SR_CRTD_DT, T2.SR_STAT_NM, T3.PROD_NM, T2.SR_SUBJ_TXT, T2.SR_SUBJ_DETL_TXT, T2.SR_ABSTR_TXT,T2.SR_DESCN_TXT, T1.EXTD_DESCN_TXT, T2.SR_STT_TXT, T4.PROF_TYP_NM
from svc_reqst_ord_itm t1, svc_reqst t2, crm_intrn_prod T3, svc_reqst_contct T4
where T2.SR_CRTD_DT BETWEEN ?Begin Date? AND ?End Date? AND
t1.sr_row_id_txt(+) = t2.sr_row_id_txt AND
t2.sr_Prod_ID = t3.prod_row_id_txt AND
t2.sr_contct_id_txt = t4.contct_row_id_txt AND
decode(?Product_Name?, ' ALL', 'X', t3.Prod_nm) IN decode(?Product_Name?, ' ALL','X',?Product_Name?) AND
(contains(T1.EXTD_DESCN_TXT,?Search_Text?) > 0 OR
contains(T2.SR_DESCN_TXT,?Search_Text?) > 0 OR
contains(T2.SR_SUBJ_DETL_TXT,?Search_Text?) > 0 OR
contains(T2.SR_ABSTR_TXT,?Search_Text?) > 0 OR
contains(T2.SR_SUBJ_TXT,?Search_Text?) > 0)
ORDER BY T2.SR_CRTD_DT
*********

To make it work, I had to take out the prompts, the report works but is pretty useless. The following query works.

select T2.SR_NO_TXT, T2.SR_CRTD_DT, T2.SR_STAT_NM, T3.PROD_NM, T2.SR_SUBJ_TXT, T2.SR_SUBJ_DETL_TXT, T2.SR_ABSTR_TXT,T2.SR_DESCN_TXT, T1.EXTD_DESCN_TXT, T2.SR_STT_TXT, T4.PROF_TYP_NM
from svc_reqst_ord_itm t1, svc_reqst t2, crm_intrn_prod T3, svc_reqst_contct T4
where
t1.sr_row_id_txt = t2.sr_row_id_txt AND
t2.sr_Prod_ID = t3.prod_row_id_txt AND
t2.sr_contct_id_txt = t4.contct_row_id_txt AND
(contains(T1.EXTD_DESCN_TXT,?Search_Text?) > 0 OR
contains(T2.SR_DESCN_TXT,?Search_Text?) > 0 OR
contains(T2.SR_SUBJ_DETL_TXT,?Search_Text?) > 0 OR
contains(T2.SR_ABSTR_TXT,?Search_Text?) > 0 OR
contains(T2.SR_SUBJ_TXT,?Search_Text?) > 0)
ORDER BY T2.SR_CRTD_DT

 

[dennydm]

Hi,
Sometimes cognos doesnt work well with IN clauses. So you can replace IN with OR... it may not be an optimal solution but it worked for me once..

Also, if above doesnt work, then try re-building the whole report in the new version of Cognos.

 

[ashishh]

The problem has been solved. Apparently all we needed was curly braces {} for the entire query. Once we put it on, the report started working fine.

Just wondering if anyone else ran into this problem or not.

BTW, thanks for your response dennydm!

Ashish