6 Replies Latest reply on Nov 29, 2012 4:33 PM by sharpe

    Conditional Master-detail Relations

      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
          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
          I would think that would work for you.
          • 3. Re: Conditional Master-detail Relations
            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
              I tried the suggested solution, but it didn't work.
              • 5. Re: Conditional Master-detail Relations
                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)
                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,
                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.
                1 person found this helpful
                • 6. Re: Conditional Master-detail Relations
                  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.