How to return muliple values using Unconnected lookup?

Today we discuss how to return muliple values using Unconnected lookup. First of all, the UnConnected lookup has one return port and returns one column from each row. If the interviewer asked the above scenario, follow the below solution.

• Let us assume EMP will be source table and DEPT will be LOOKUP table.
• Create a target table T_UNCONN_LKP in target designer. Table should contain all ports of EMP table plus DNAME and LOC.
• Go to the Mapping Designer and create the mapping with source, target, lookup and expression transformations as shown below:

• Then concatnate multiple ports with the separator '&' into single port in Lookup transformation.

• To do edit Lookup transformation and create a return port (DNAME).
• Then go to Properties tab, there write the following expression in Lookup SQL Over.

SELECT dname||'&'||loc FROM dept

• In expression transformation spilt it into multiple ports as follow.
• Declare 2 output variables with

o_DName = SUBSTR (dname, 1, (INSTR(dname, '&', 1)) o_Loc = SUBSTR(dname, INSTR (dname, '&'))

• Connect all ports from Expression transformation to target table.