Unconnected Lookup does not seem to be working

[suchit]

Hi,

I am calling a LKP Transformation in an Aggregator like this:

:LKP.LKP_GET_E_NAME

The workflow is running and the target table is being populated perfectly except for the column that calls the LKP Transformation. What could be the reason?

Thanks!
su

 

[blom0344]

If you are going to use an unconnected lookup, then there should be - at least - one input field for the lookup transformation.
Like : :LKP.LKP_GET_E_NAME(ADDRESS)

Then, for every row, the lookup tranformation should return a value for each ADDRESS


Ties Blom

 

[suchit]

Ties, hi

Well I have called the LKP with 2 parameters. But I am not getting the expected column back. Any ideas?

Thanks,

Suchit

 

[suchit]

Hi Ties,

I did call the LKP with 2 parameters. But I cant understand why it returns a NULL. When I open the log file and copy-paste the script into SQL, I get values alrite. But, LKP doesnt seem to be working. Any pointers?

Thanks,
Suchit

 

[blom0344]

Are you using an overwrite in the lookup?
If this is the case you HAVE to use aliases from the fields that are to be returned (AFAIK)

So syntax should be :

SELECT FIELD1 AS A, FIELD2 AS B FROM .......

Ties Blom

 

[suchit]

Yes, I am using an override query.
And, yes, I have used the aliases.

Actually I clicked on Generate SQL and the aliases were there. I just added the WHERE condition, tahts all.

Phew! Can't understand whats going wrong!

 

[blom0344]

What you can do is switch on verbose logging on both the lookup and the tranformation that is performing the lookup action. Can there be a type mismatch?
What is the exact expression that is used in the aggregator transf?

Ties Blom

 

[suchit]

OK.
I am using this to call the LKP from the aggregator:

IIF ( STATUS='S',
:LKP.LKP_GET_NAME(par1, par2),
IIF (STATUS='B',
IIF( SUB_STATUS='K' ,
:LKP.LKP_GET_NAME(par3, par2),
:LKP.LKP_GET_NAME(par4, par3) ) ) )

The field in the AGG calling the LKP is DECIMAL(2) and the COLUMN in the LKP marked R is also DECIMAL(2).

Thanks!

 

[blom0344]

Well, this may be just a syntax thing, but why not use:

DECODE(STATUS,
'S',:LKP.LKP_GET_NAME(par1, par2),
'B',:LKP.LKP_GET_NAME(par3, par2),
'K',:LKP.LKP_GET_NAME(par4, par3),
NULL)

Are you sure that your nested IIF's are going to do the correct thing?

Ties Blom

 

[suchit]

I am sure the IIF's are doing fine.

The thing is, the data i am testing it on has all status=S
and the same IIF structure is being used for another field in the AGG (only diff is that no LKP is called here) and the IIF is working perfectly fine.

So, there's some prob with the LKP for sure. Any other ideas for this? Thanks!

-Suchit

 

[blom0344]

Well, ideas?
It could be something as simple as trying to match data where one of the sources has trailing spaces (I am used to AS400 data) and that would result in no matches found.
Anything in the session log? (run a test version with verbose logging on)

Ties Blom

 

[suchit]

Ok. How do I enable verbose logging ON? I run using the Workflow Manager and not by a script. Is the option available in the WF MGR? lemme check it out. And hope i get something outta this

Thanks.
-suchit

 

[suchit]

I got the Verbose option.
Running it now...

-suchit

 

[blom0344]

You can set this with the session for a specific transformation.

If you have no luck with tracing the problem, then try the following:

Perform the lookup prior to the agg transformation and store the variations in variable ports. Then use an additional outgoing port to select from the various variable ports..

Ties Blom

 

[suchit]

Ties!

My bad man. One of the params in the LKP was a Date and I had to do NVL in the AGG before passing it as a parameter. That's the reason y it was failing.

But thanks a lot for giving me tips for Verbose Log Mode.
It helped me a lot in debugging!

-Suchit

 

[blom0344]

Glad you found the culprit. It is quite often a small aspect!

Ties Blom

 

[suchit]

As far as working goes, the map is giving the right output.
But, in the LOG I am able to see the folowing warning.

TE_7004 Transformation Parse Warning; transformation continues...

If you have any idea why this might be appearing, please help me out!

 

[blom0344]

I witness these errors almost daily. My guess is that the INFA engine handles a (datatype) - conversion implicitly but is smart enough to log it and continue.

A TE_7002 would result in a failure (if you use the default setting on fail on first error)



Ties Blom