2 Replies Latest reply: Jan 4, 2013 8:33 AM by Budjum RSS

    Table Lookup in Data Load problem

    Budjum
      Hello all,

      Could you please suggest what can be a reason of the problem:
      While data import and doing table lookup all works fine untill the column with Key value is numeric (such as surrogate PK) but if i'm trying to use varchar NK - lookup doesn't work.
      It simply doesn't find the loookup value without any additionall error.

      For example:

      table TMP_MAP (
      DICT_ID number,
      VAL1 varchar2(10),
      VAL2 varchar2(10),
      VAL3 varchar2(10),
      )

      table TMP_DICT(
      DICT_ID number,
      TMP_DESC varchar2(10)
      )


      for a following set of data all will work fine:
      table TMP_DICT

      DICT_ID TMP_DESC
      1 one
      2 two


      and data to import to TMP_MAP with lookup on TMP_DICT for DICT_ID column with uploaded column TMP_DESC
      one;11;11;11
      two;22;22;22

      But if DICT_ID will be changed to varchar2(20) and will contain character values like below, same import flow doesn't work:
      DICT_ID TMP_DESC
      ONE one
      TWO two



      APEX 4.1.1.00.23
      Oracle 11gR2

      Thanks in advance

      Edited by: Budjum on 03.01.2013 3:29
        • 1. Re: Table Lookup in Data Load problem
          riedelme
          Budjum wrote:
          Hello all,

          Could you please suggest what can be a reason of the problem:
          While data import and doing table lookup all works fine untill the column with Key value is numeric (such as surrogate PK) but if i'm trying to use varchar NK - lookup doesn't work.
          It simply doesn't find the loookup value without any additionall error.
          . . .
          But if DICT_ID will be changed to varchar2(20) and will contain character values like below, same import flow doesn't work:
          DICT_ID TMP_DESC
          ONE one
          TWO two



          APEX 4.1.1.00.23
          Oracle 11gR2
          Should work if done properly. Look very carefully on the values you are searching for - are you trying to find the '1' you started with, or are you searching for the new value 'ONE'?

          IDs usually work better as numbers. Fewer cycles for the CPU to process
          • 2. Re: Table Lookup in Data Load problem
            Budjum
            Hi,

            I know that it should work, but it doesn't :(.

            Idea is - and you can easely test it - once your key column is not numeric but character lookup doesn't work.

            It's work fine if you have 1,2.... in table as ID and trying to find it by Desc 'one,two...', but if you have 'ONE,TWO...' as ID lookup is failed.

            Unfortunately can't use numbers...

            Edited by: Budjum on 03.01.2013 22:47

            Edited by: Budjum on 04.01.2013 6:33