I have a situation where I am inserting a record set into a database and I need to set a field with an id looked up in a table in the database by a name. I tried to use a data input to do the lookup of a single row followed by a join to the record set, but it requires a join key (a simple cross product against the input single driver record would do what I want). Is there a way to get two source records and transform without requiring any join key? One workaround I've thought of is using an explicit INSERT...SELECT instead of INSERT...VALUES, but then I have to code the logic into every component that needs the value, so it's less than ideal.
-
The only solution I've found is to use a fake field with a constant value like 0 as a default value, then add a data input table component that queries a single row with the id value and a separate join that joins that single record with all the target rows , joining on the fake fields in the two records. This is also less than ideal because of the added complexity of the graph and the additional code required for the transformation in the additional join.
Please sign in to leave a comment.
Comments 1