This discussion is archived
6 Replies Latest reply: Nov 29, 2012 8:33 AM by sharpe RSS

Conditional Master-detail Relations

sharpe Newbie
Currently Being Moderated
Hi. I have a master-detail relationship between two tables...Call_Queue and Reporter_History. The relation is defined as:
call_queue.participant_id = reporter_history.reporter_id

The Call_Queue table contains the following key values:
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)
The Reporter_History has the following column:
reporter_id number(8)
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?

Here's what I've tried in the Relation's Join property so far, but neither will compile:
call_queue.participant_id = reporter_history.reporter_id and call_queue.participant_type='001'
cc_call_lists.participant_id = to_char(reporter_history.reporter_id)
Edited by: sharpe on Nov 28, 2012 11:38 AM
  • 1. Re: Conditional Master-detail Relations
    MLBrown Journeyer
    Currently Being Moderated
    The idea of joining a VARCHAR2 to a NUMBER is a little bit scary to me. I would change the REPORTER_INFO.REPORTER_ID to VARCHAR2(10) and create a PK-FK relationship in the database, but...

    If the current relationship (below) is working, I would leave it alone.
    call_queue.participant_id = reporter_history.reporter_id
    You could then change your REPORTER_HISTORY block's "Where Clause" in the property palette to
    participant_type='001'
    I would think that would work for you.
  • 2. Re: Conditional Master-detail Relations
    sharpe Newbie
    Currently Being Moderated
    Thanks.
  • 3. Re: Conditional Master-detail Relations
    sharpe Newbie
    Currently Being Moderated
    Nope...this didn't work. When the Master record is a Prospect (which can have characters in the value) the relation still fails with:
    FRM-40356: Invalid number in example record. Query not issued.
    In the Detail block's Default Where property I tried adding this where condition, but I'm still getting the error message:
     
    a.participant_type = '001'
    Any other thoughts on working around this issue would be greatly appreciated.
  • 4. Re: Conditional Master-detail Relations
    sharpe Newbie
    Currently Being Moderated
    I tried the suggested solution, but it didn't work.
  • 5. Re: Conditional Master-detail Relations
    MLBrown Journeyer
    Currently Being Moderated
    I was afraid of that since you were mixing NUMBER with VARCHAR2. If you are not allowed to update the REPORTER_HISTORY.REPORTER_ID column from NUMBER to VARCHAR2, then I would create a view with the same columns as REPORTER_HISTORY but convert the REPORTER_ID to VARCHAR2 and reference that in the form module instead of the table.

    So... if the REPORTER_HISTORY has a layout of:
    REPORTER_ID    NUMBER(8)
    REPORT_TEXT    VARCHAR2(200)
    etc...
    Then I would create a view based on that table:
    create or replace view REPORTER_HISTORY_VW as
    select to_char(reporter_id) reporter_id,
           report_text,
           ....
    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.
  • 6. Re: Conditional Master-detail Relations
    sharpe Newbie
    Currently Being Moderated
    That sounds like a good idea. I'll give that a shot.

    Also, I think the error is not critical and could be suppressed without harm since no records would be retrieved even if the data types did match as Char.

    Thanks so much.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points