Re: IGNORED SQL STATEMENT IN WHERE CLAUSE

I tried email but got a bounceback saying no such user so I'll post this here.

On Mon, 28 Oct 1996 11:47:37 -0500, you wrote:

>CALLING ALL DBMS_PIPE EXPERTS:
>
>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

I'm not a pipe expert but a see something that might be causing problems.

The 6th line in the where clause:

        CALL_CALL_START > '26-SEP-96' AND
Should that have a to_date function in it or are you actually storing a text representation of the date? If that's failing your qualifications, the ignored statement may never be seen.

There also may be a reason you're doing it that way but the final 5 EXISTS statements in your where clause could be better written as:

	exists (select 1 
			from contact_answers 
			where cont_valid_ans_ques_id = 745521 
				and cont_valid_ans_seq IN  (2,3,4,5,6) 
				and contact_answer_v.cont_call_recor_id = 

contact_answers.cont_call_recor_id)
i
i
i
i
i
i
i
i
i
i
i
i
i
i
i
i
i
i Received on Fri Nov 01 1996 - 00:00:00 CET