INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...with companys cutting back on training, lack of true support by makers of software, the forums are a great tool in your cyber-toolbox...."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Project and Data Management > Data Management Programming Tools > Informatica Corp. solutions Forum

Single value "Lookup"

Read More Threads Like This One

pbackstrom (Programmer)

19 Jun 03 17:22

I want a single max value of a certain non-related table to be plugged into a field in each row generated by the mapping.

There's nothing to join to or look up by, so it doesn't like joiner or lookup. Really I just want it to act like a system variable.

Putting Select Max() as the SQL query in a second source qualifier and having the field as the only source qualifier port won't let me map from that port to the rest of the record, "Concatenation Disallowed".

Tx for any help.

blom0344 (TechnicalUser)

20 Jun 03 3:33

Sol 1.
Use a special lookup transformation (unconnected) for this.
Use the SQL override function in the lookup to get the single max value form the unconnected table.

Sol 2.
Use the non-related table as a source and use override SQL again. Create a second port that holds a dummy value 'X'. Create the same in the other part of the mapping. Join streams over this dummy-field. This will effectively introduce the aggregated value in every row in the mapping.

T. Blom
Information analyst
tbl@shimano-eu.com

Dagon (MIS)

20 Jun 03 3:57

A third option is to drag the required row from the aggregate table into the existing source qualifier.  You can then simply amend your override to include this table (no join required because it is single row) e.g.

select
   ...,
   agg_col
from source_table,
     (select max(agg_col) as agg_col
       from aggregate_table)

This avoids the need to have two source qualifiers and a joiner.

pbackstrom (Programmer)

20 Jun 03 12:35

Thanks for quick, specific responses.

Another piece of info (sorry); main table is a flat file, lookup is a db file. So doesn't look like I can override SQL in main file.

Blom Sol 1: I followed what doc I could find in creating an unconnected lookup, but get "The lookup condition is empty. This transformation is not valid." Any more hints?

Blom Sol 2: Can't seem to create a dummy field in the flat file SQ. Any more hints?

Dagon: Same issue, there's no SQL for the flat file.

Thanks again.

blom0344 (TechnicalUser)

23 Jun 03 3:18

Sol1:

Effectively create a lookup condition that sets itself always to true, which may require some creative thinking.

Sol2:

The creation of an additional port can be done in a expression transformation after the SQ. This is independent of whether source is flatfile or not.

Drop me an email and i'll send you an example.........

T. Blom
Information analyst
tbl@shimano-eu.com

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Informatica Corp. solutions