Single value "Lookup"

[pbackstrom]

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]

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]

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]

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]

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