I am looking for a way to pass variable into an SQL statement so I can run it against different database instances. I have a JDBC Reader object in my plan but can only define one data source.

In the below simple example query I need to define edw01d and kennel01d with a variable so I can move them from development to test and projection without modifying the plan.

select distinct agency_id, intake_type, intake_subtype,
(select CARDS_INTAKE_TYPE from kennel01d.cards.INTAKE_BEST_GUESS G where G.INTAKE_TYPE =T.INTAKE_TYPE AND G.INTAKE_SUBTYPE = T.INTAKE_SUBTYPE) AS NEW_TYPE
FROM edw01d.cards.dim_animal_intake AS T
WHERE INTAKE_SUBTYPE LIKE '%Euth%'

Would look something like

select distinct agency_id, intake_type, intake_subtype,
(select CARDS_INTAKE_TYPE from ${kennel}.cards.INTAKE_BEST_GUESS G where G.INTAKE_TYPE =T.INTAKE_TYPE AND G.INTAKE_SUBTYPE = T.INTAKE_SUBTYPE) AS NEW_TYPE
FROM ${edw}.cards.dim_animal_intake AS T
WHERE INTAKE_SUBTYPE LIKE '%Euth%'

Where edw and kennel are replaced. I am new to the tools but didn't see an example doing something like this. I see you can extend the tools by writing eclipse plugins but that seemed like overkill for what I am wanting to do.

thanks
James

James, I don't think there is a way to pass variables to DQC plans at runtime. What I have done in the past is to modify the runtimeCfg.xml of the plan using a deploy script such as Ant when moving code from Dev to Test to Prod. This way the the database connections are changed to match the target environment and the changes are done using an automated script.

James;
The short answer is yes - We pass Variables from our Flows to the DQC Plans.

No time to write a novel - e-mail me and we can make arrangements to talk about it.