Thursday 6 March 2014

Exercise 2: How to get (return) multiple values from unconnected Lookup?

Exercise 2: How to get (return) multiple values from unconnected Lookup?

Theory/Analysis:
As we already know when using unconnected lookup we can return only value with the help of return port. We can't select multiple port as return port. This constraint is imposed by Informatica itself.
We are smarter than Informatica, we will achieve it logically. Yes, you are right we need to override the default SQL query of Lookup. We will concatenate all required port value and would return it as a single value.

Practically: 

We will load DNAME and LOC value along with EMP table value in target. We will do lookup on Dept table in Unconnected Mode.

Step 1: Drag and Drop your source to Mapping Designer.

Step 2: 
Lookup
Create Lookup on Dept table and follow steps below..

A) Lookup-->Right Click-->Edit-->Port tab
     i. Create a port DEPTNO1 (Input Port)
     ii. Delete Loc port
     iii. DNAME port (Return Port and Lookup Port should be checked)
     iv. Increase the Prec of DNAME port to 50.

B) Lookup-->Right Click-->Edit-->Properties Tab
     Lookup Sql Override
    SELECT DEPT.DNAME||','||DEPT.LOC as DNAME, DEPT.DEPTNO as DEPTNO FROM DEPT

C) Lookup-->Right Click-->Edit-->Condition Tab
     Lookup Table Column   Operator  Transformation Port
                 DEPTNO                =              DEPTNO1
Step 3:
Expression
Create three ports

LKP_VALUE (Variable Port) = :LKP.LKPTRANS(DEPTNO)
Note: LKPTRANS is Lookup Transformation Name. If you have given different name please write that one.
DNAME (Output Port) = SUBSTR(LKP_VALUE, 1, INSTR(LKP_VALUE, ',', 1, 1)-1)
LOC (Output Port) = SUBSTR(LKP_VALUE, INSTR(LKP_VALUE, ',', 1, 1)+1)

Step 4: Connect your ports to target table.-

BINGO!!!


Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com

11 comments:

  1. Hi the above scenario can be done in any of the version in informatica

    ReplyDelete
  2. Yes, because in every version we have an option to Override SQL in Lookup.

    If you are unable to do or getting any error please let me know.

    Anyway which version you are using? I am associated with Informatica from version 7.1 and each and every version sql override option is there for lookup.

    Thanks.

    ReplyDelete
    Replies
    1. I tried as shown above but no result came. Plz explain...

      Delete
  3. Hi i think there need a correction in the logic use in Expression transformation, instead of LKP_VALUE in the below expression we should used DNAME and LOC as below:

    DNAME (Output Port) = SUBSTR(DNAME, 1, INSTR(DNAME, ',', 1, 1)-1)
    LOC (Output Port) = SUBSTR(LOC, INSTR(LOC, ',', 1, 1)+1)

    ReplyDelete
    Replies
    1. An unconnected look up t/f can have only one port as return port. In your case it can be either DNAME or LOC but not both.

      Delete
  4. The logic is correct. Please find the explanation below.
    1. As per you if you should use DNAME, LOC: This is not possible using unconnected lookup as unconnected look up t/f can have only one port as return port. In your case it can be either DNAME or LOC but not both.
    2. As per above stated logic:
    LKP_VALUE (Variable Port) = :LKP.LKPTRANS(DEPTNO)
    This means the return port from unconnected lookup t/f (which is DNAME from unconnected lookup t/f) will be assigned to LKP_VALUE (Variable Port) of expression transformation.

    P.S: Don’t get confused with DEPTNO present in LKP_VALUE (Variable Port) = :LKP.LKPTRANS(DEPTNO). This doesn’t mean DEPTNO from unconnected lookup t/f will be assigned to LKP_VALUE (Variable Port) of expression transformation. This will not happen as DEPTNO from unconnected lookup is never marked as return port. The DEPTNO indicates lookup table column. You can confirm this from lookup condition on the Condition tab DEPTNO = DEPTNO1.

    ReplyDelete
  5. :LKP.LKPTRANS(DEPTNO) how it will come DEPTNO... Mine showing Dept_ID

    ReplyDelete
  6. Hi why we have to delete loc port in lookup...

    ReplyDelete
  7. Can you have a video for this task

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. if i want to return more than 2 columns the above example you have mentioned DNAME and LOC only that's fine. For example i want to return DNAME,LOC,PINCODE,COUNTRY like this then we couldn't able to separate them using SUBSTR and INSTR right could you explain it if possible.

    ReplyDelete