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.
Excellent work!
ReplyDeleteVery nice explaination.
ReplyDeleteBut, I have one more problem. If i am flat files then SQL override option will be disabled. Can somebody tell me how to do this with flat files????
Nice article.
ReplyDeletefor informatica interview questions and answers click here
This is very informative and this is very usefull to us.
ReplyDeleteInformatica online training , informatica training in bangalore
informatica online training , informatica training in bangalore , informatica training , informatica online trainig in bangalore
This is very good information
ReplyDeleteinformatica training , infromatica online training, informatica training in bangalore, infromatica online training in bangalore
I feel Informatica is a very good and useful tool through the use of which one can easily solve sophisticated and complex database problems easily with voluminous operations.
ReplyDeleteInformatica Read Rest Api
Great Article
ReplyDeleteArtificial Intelligence Projects
machine learning final year projects 2020
JavaScript Training in Chennai
JavaScript Training in Chennai Project Centers in Chennai
I have read your blog its very attractive and impressive. I like it your blog. Digital Marketing Company in Chennai
How to return multiple values using" a programming concept is a common query. Silva Madden This phrase suggests a desire to extract several outcomes from a single function or method.
ReplyDelete