6 Replies Latest reply: Nov 29, 2012 10:33 AM by sharpe RSS

    Conditional Master-detail Relations

    sharpe
      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
          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
            Thanks.
            • 3. Re: Conditional Master-detail Relations
              sharpe
              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
                I tried the suggested solution, but it didn't work.
                • 5. Re: Conditional Master-detail Relations
                  MLBrown
                  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
                    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.