I am a newbie to Informatica and i think i m doing something real stupid to fix it. Your help is greatly appreciated and below the scenario
I have the following query to do for my informatica mapping. In this mapping, I need the ports from Atable only, but i am comparing it with columns of the Btable(no port from Btable is used in rest of mapping).
My select statement is like this
select Acolumn1, Acolumn2,Acolumn3,Acolumn4
This is how i started developing the code
I got two sources and sourceQualifiers for Atable and Btable and then I deleted one sourceQualifier for Btable and borrowed the ports of Btable Source to Atable SourceQualifier
I am using SQL Override in (Atable)Source Qualifier, but this one is not working and showing the error that "port names should be in respective order" I checked again and made sure the ports are in order
But still now I see the same error, I am not able to fix this.
Can you suggest on this.
I found ur problem.
Till step 1 what did is correct, from SQ to exp transformation(ANCHOR) connect required fields. After select and Edit SQ ,in SQL override click generate SQL in right bottom most button and you can c now the required query genearated by Informatica.
Save and validate both mapping and session.
Let me know if you need any further assistance.?
Thanks Krishna for your reply. But I was infact using the expression transformation in the mapping already(as the requirement needed, but I didnot mention as I thought problem was in SourceQualifier Override)
Actual problem is there and stopping at Validating the code inside the SourceQualifier Ovverride. It is still persisting, so the whole mapping is turning invalid .
lets do it again.
delete the SQ from your mapping and drag and drop again the same source back and connect required field from SQ to EXP.
Now go to SQ Override and Click Generate SQL,Now u can c the Informatica generated SQL query in SQL Override.Copy that query put it in your database(Oracle etc) and you can confirm that query written by informatica whether its correct or not while query returns result.
And also after finishing the above task go to Mapping tab in Designer ,from that click validate,after clicking the VALIDATE, u can c whether that mappings is valid or not in OUTPUT WINDOW.
If you not able to find anything from OUTPUT WINDOW,just copy that OUTPUT WINDOW message and show me.
Will discuss further if you have any queries.
If both teh tables are in same database then you can try the following:
Only include the source and source qualifier for Atable and then use a sql override in the source qualifier where you join Atable and Btable.
I'm not really sure why you need to use a SQL Override at all.
Step1 that you follow is fine.
As for Step2 - all you are using the SQL override to do is specify the join condition for Atable to Btable. There are 2 other ways of doing this which are both neater: