IGNORED SQL STATEMENT IN WHERE CLAUSE

From: Jennifer Brickner <brickner_at_vistachrome.com>
Date: 1996/10/28
Message-ID: <3274E3A9.768B_at_vistachrome.com>#1/1

CALLING ALL DBMS_PIPE EXPERTS: I am retrieving a where clause from a table and compiling it in a stored procedure using dbms_pipe. One of my statements is being ignored!!! I definitely do not get an error during runtime or in "WHEN OTHERS" table.  I am stumped!!!!! Does anybody have any idea what it could be?

HERE IS MY SQL STATEMENT (WORKS FINE FROM PROMPT)     build_sql := 'insert into leads (lead_contact_id, lead_gener_id)'||

                  'select  distinct con_id, '||uniq_seq ||
                 ' from contact_answer_v  where ('||
                 rec.buye_criteria_WHERE || ') and ' ||
                 'not exists (select 1 from leads l, lead_generations lg 
' ||


                           'where contact_answer_v.con_id = 


l.lead_contact_id ' ||


                           'and l.lead_gener_id = lg.id ' ||

                           'and lg.id < ' || uniq_seq ||


                           ' and lg.buyer_id = ' || rec.buye_id || ')';


    begin
      err_stage := 1;
      cursor_1 := dbms_sql.open_cursor;
      dbms_sql.parse(cursor_1,build_sql,dbms_sql.v7);
      return_1 := dbms_sql.execute(cursor_1);
      dbms_sql.close_cursor(cursor_1);
      cursor_1 := 0;
      commit;
    exception
      when others then  
      err_num := SQLCODE;
      err_msg := SUBSTR(SQLERRM,1,100);
      insert into hb3_errors values
        (0,
	sysdate,
	user,
	1,
	1,
	err_num,
	'ERROR_STAGE '||ERR_STAGE||ERR_MSG,
	'GENLEAD');

commit;
HERE IS VARIABLE FOR WHERE_CLAUSE
exists (select 1 from contact_answers where cont_valid_ans_ques_id = 271307 and cont_valid_ans_seq
= 1 and contact_answer_v.cont_call_recor_id = contact_answers.cont_call_recor_id ) AND            

CALL_MEDIUM = 'EC' AND

                                                 
                            
CALL_CALL_START > '26-SEP-96' AND
                                      
                            

( exists (select 1 from contact_answers where cont_valid_ans_ques_id = 745521 and cont_valid_ans_seq

• 2 and contact_answer_v.cont_call_recor_id = contact_answers.cont_call_recor_id ) OR

 exists (select 1 from contact_answers where cont_valid_ans_ques_id = 745521 and cont_valid_ans_seq
= 3 and contact_answer_v.cont_call_recor_id = contact_answers.cont_call_recor_id ) OR             

 exists (select 1 from contact_answers where cont_valid_ans_ques_id = 745521 and cont_valid_ans_seq
= 4 and contact_answer_v.cont_call_recor_id = contact_answers.cont_call_recor_id ) OR             

 exists (select 1 from contact_answers where cont_valid_ans_ques_id = 745521 and cont_valid_ans_seq
= 5 and contact_answer_v.cont_call_recor_id = contact_answers.cont_call_recor_id ) OR             

 exists (select 1 from contact_answers where cont_valid_ans_ques_id = 745521 and cont_valid_ans_seq
= 6 and contact_answer_v.cont_call_recor_id = contact_answers.cont_call_recor_id ) ) AND           

CON_MOVE_DATE < (sysdate+180) -- IGNORED STATEMENT

Regards,
Jennifer Received on Mon Oct 28 1996 - 00:00:00 CET