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
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