DSXchange

Is there a way to pass parameters into the SQL in an ODBC stage to a database. I want to select records on a rolling date without having to manually change the SQL all the time AND without letting the transform weed out the records because I don't want to slow the job down by bringing back and processing thousands of unneeded records.

Thanks,
JB

There are two ways to add parameters. Some database stages allow you to add a where clause that gets appended to the generated SQL. You should see a "Where" tab on your ODBC Selection screen. You can put pameters directly into that where clause using the # delimeters. If you don't have a where clause tab then you can use "User Defined SQL" which lets you write out the SQL statement.

Eg.
SELECT CUSTOMER_ID, CUSTOMER_NAME
FROM CUSTOMER
WHERE CREATION_DATE >= #StartDate#
AND CREATION DATE <= #EndDate#

Just be wary that some ODBC drivers/databases insist upon quotes, for example:
SELECT CUSTOMER_ID, CUSTOMER_NAME
FROM CUSTOMER
WHERE CREATION_DATE >= '#StartDate#'
AND CREATION DATE <= '#EndDate#'

Job parameters still work quite happily; they are resolved when DataStage constructs the SQL statement that is sent to the database to be prepared for execution.