Output column mismatch. User defined SQL query in Source Qualifier

Question

I think I’m missing something obvious here. I'm tasked to duplicate a mapping that works fine, by changing ONLY the source qualifier portion.

The original mapping looks like this,

First of all, I don’t understand how the original mapping simply connects from the Source Qualifier to the Expression. The column names are supposed to be changed completely because of the user defined query.

eg.

INSERT_DM to max(HVOLE.INSERT_DM)

In my new duplicated mapping, my new Source qualifier is giving me this error when I click "Validate",

It's weird that it mentions "exactly 3 fields", when my query actually outputs 5 separate columns.

Note that I've created o_BEADHEIGHT1 and o_BEADHEIGHT2 for the columns that don't exist. These columns are newly created by my user-defined query.

Answer 1

It does not matter if the port names in SQ does not match with the select query fields. Only the order of ports matters. Also it only considers the ports that are connected with the next transformation.

Answer 2

The reason why you are getting this issue is 2 out of the 5 ports in the Source Qualifier is not linked with the Source Definition. This validation considers only the Source Qualifier ports you have linked with the source definition as well as the next transformation.

The funda is

1) The number of fields selected in the SQL override query should match the number the number of ports in the Source qualifier which are LINKED to the next transformation. The names are not required to be the same but order needs to be the same.

Interestingly Informatica maps the fields from the SQL query to the Source qualifier output links instead of Source Qualifier ports. So the first column in the SQL query gets mapped to the first link, second column to the second link and so on.

2) Also all the ports in the Source Qualifier transformation needs to be linked with the Source definition. You can delete the unused ports in the Source Qualifier transformation to avoid confusion.

Answer 3

Source qualifier is not expression transformation and can not be used like one.For your example, LOT_NO,MCD_ID and TEST_TS are conneced to imput and output link which is why you are getting error 'exactly 3 fields projected.'. The fields in source qualifier should be connected to input and output link to consider it as valid port. Even if you connected 5 ports of source qualifier output but you connected 3 as input. So, to resolve this,

• Connect o_bedhight1 and o_beadheight2 from source to source qualifier.
• Remove PROCCD, MAT_TYPE if you want clean SQ