The Reporter_History has the following column:
participant_type varchar2(3) -- '001' is a Reporter type, '002' is a Prospect type. Reporter Ids only contain only number characters (e.g. 123456), but Prospect Ids can contain characters (e.g. W123456) participant_id varchar2(10)
My problem is that not all Participants are Reporters. In cases where call_queue.participant_id contains a letter this relation is failing. Is there a way to make the Relation conditional based on Participant_Type? Any other way to solve this issue?
call_queue.participant_id = reporter_history.reporter_id and call_queue.participant_type='001'
Edited by: sharpe on Nov 28, 2012 11:38 AM
cc_call_lists.participant_id = to_char(reporter_history.reporter_id)
You could then change your REPORTER_HISTORY block's "Where Clause" in the property palette to
call_queue.participant_id = reporter_history.reporter_id
I would think that would work for you.
In the Detail block's Default Where property I tried adding this where condition, but I'm still getting the error message:
FRM-40356: Invalid number in example record. Query not issued.
Any other thoughts on working around this issue would be greatly appreciated.
a.participant_type = '001'
Then I would create a view based on that table:
REPORTER_ID NUMBER(8) REPORT_TEXT VARCHAR2(200) etc...
I would then modify the form module to reference the view instead of the table. The join should work without any problems since you will now be joining VARCHAR2 to VARCHAR2.
create or replace view REPORTER_HISTORY_VW as select to_char(reporter_id) reporter_id, report_text, ....