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
Parameters passed into SQL
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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#
Eg.
SELECT CUSTOMER_ID, CUSTOMER_NAME
FROM CUSTOMER
WHERE CREATION_DATE >= #StartDate#
AND CREATION DATE <= #EndDate#
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.