Using Unconnected Lookup Transformation 1

[suchit]

Hi,

I want to perform the following in Informatica:

SELECT 
      <field_1>, <field_2>
FROM
      <table_1>
WHERE
      <field_1>=(select <field_1> from <table_2>);

I have only <table_1> as source in my map. Can I use the nested select statement? I think I cannot. If there's any way to use it like this, please contribute.

As an alternative, I defined a Lookup Transformation on <table_2> to retrieve <field_1> of <table_2>. And now I am trying to use the Select Query as:

SELECT 
      <field_1>, <field_2>
FROM
      <table_1>
WHERE
      <field_1>= :LKP.LKP_myLookUp('some_parameter')

And while running, it gives me SQL Command not propoerly ended. Can someone explain what I am doing wrong?

Thanks in advance!
-su

 

[suchit]

Just to add,

the Select statement above is to be used in the Source Qualifier of <table_1>

 

[blom0344]

Your assumption on a nested expression is 100% wrong.
Basically you can use any expression as a SQL override as long as you make sure that the objects coming from the SQ are connected to the next widget in the mapping.

[We've mappings that even use stored procedures in the extraction SQL (sic)]

You cannot adress a lookup in the SQ as you describe. But it is possible to address a mapping variable
You should see this as an extra in INFA


Ties Blom

 

[suchit]

Hi blom0344,

Could you rather give me a solution as to how can I make things work in the above scenario?

Thanks.

 

[blom0344]

You need a valid SQL statement (and your example does not look like valid) and use the override setting in the source qualifier. It does not matter then, that your query references a table that is not connected to the SQ.
The ETL engine will execute the SQL from the override and will take the output of the query as input for the rest of the mapping.



Ties Blom

 

[suchit]

Hi Ties,

I thought a table needs to be connected to the SQ to enable the SQ query to reference it. Misconception.

Thanks a ton for the help!

-Suchit

 

[blom0344]

It needs to be connected if you do not use a SQL override..

Ties Blom