I have a query like this:
SELECT 0 AS ID,
9999999999 AS CODE
SELECT VEH.TP_ID AS ID,
VEH.VEH_ID AS CODE
So I have an interface with two data sets, one for each query.
The first data set represents the "select from dual" "constant values" and the second represents the "select * from tb_vehicle".
In the first data set I don´t have a source, then ODI generates a select without a table "select 0 AS ID, 99999 AS CODE FROM".
Any ideas on how can I achieve my objective? I need to populate one row on target table with constant values.
You can write a procedure or add a step into IKM, but it can be done exactly as you described this, too. I have just tried and had fun:
1. created a synonym in my source schema for sys.dual and called it MYDUAL.
2. created a corresponding datastore in my model.
3. Created an interface with a meaningful table as a source and UNION ALL with MYDUAL as a source.
4. Set implementation to desired constant without connecting any source columns to the target.
Alternatively , try this :
Duplicate the LKM and IKM you are using, create copies , something like LKM xyz (No Source) , IKM xyz (No Source).
Then adust the KM steps accordingly, remove the 'FROM' part and replace with VALUES ( ) , keeping the odiref substitution call in the parenthesis.
So you basically have a knowlede module that accepts no source data, generating :
insert into C$
(col 1, col 2, col 3 etc)
(Target mapping 1, target mapping 2, target mapping 3 etc)
I've seen it done, nice and tidy, keeps all the lineage in ODI etc.