Parameters passed into SQL

[jeredleo]

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

[vmcburney]

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#

[ray.wurlod]

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.