Massaging job parameters in an OLEDB Where clause
Moderators: chulett, rschirm, roy
Massaging job parameters in an OLEDB Where clause
Hi,
I have a simple job to pull data into a sequential file from a SQL Server database using OLEDB. I want to restrict the records I am reading in by school year. We are passing in a 9 character school year ex. 2001/2002 but the database table uses a 4 digit school year ex. 2002. Therefore I need to grab just the last 4 characters of the 9 character string to do my comparison. So my statement in the Selection pane reads as:
sch_yr = RIGHT(#PARAM_1#,4)
where sch_yr is the column from the database and PARAM_1 is the 9 character school year. Although, the job compiles and runs, it is not returning any rows.
Can I manipulate job parameters coming into an OLEDB stage in the Where clause? If so, what is wrong with the above statement?
Thanks
I have a simple job to pull data into a sequential file from a SQL Server database using OLEDB. I want to restrict the records I am reading in by school year. We are passing in a 9 character school year ex. 2001/2002 but the database table uses a 4 digit school year ex. 2002. Therefore I need to grab just the last 4 characters of the 9 character string to do my comparison. So my statement in the Selection pane reads as:
sch_yr = RIGHT(#PARAM_1#,4)
where sch_yr is the column from the database and PARAM_1 is the 9 character school year. Although, the job compiles and runs, it is not returning any rows.
Can I manipulate job parameters coming into an OLEDB stage in the Where clause? If so, what is wrong with the above statement?
Thanks
Is RIGHT() a SQL Server function? The SQL you are generating in the ODBC stage is sent to to be executed on the database therefore it must be in the database's format. Parameters are the only exception I know of. One thing you could do is define the school year as a key column and use the RIGHT function in a derivation. Because it is a key column the where clause would be generated for you.
Your original code:
Shouldn't it have quotes, as it is a string value?
If you were typing this in as a literal, it would look like this:
so try it with quotes.
Just a guess.
Code: Select all
sch_yr = RIGHT(#PARAM_1#,4)
Code: Select all
sch_yr = RIGHT('#PARAM_1#',4)
If you were typing this in as a literal, it would look like this:
Code: Select all
sch_yr = RIGHT('2001/2002',4)
Just a guess.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Okay, reverse that, are you comparing a numeric sch_yr to a string value, and that's where the problem lies? Just a guess.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 145
- Joined: Fri May 02, 2003 9:59 am
- Location: Seattle, Washington. USA
Since the errors coming via the oledb stage can be misleading (it does not return the error coming from SQL Server) I would recommend that you turn on the SQL Profiler and watch for the errors. This has been the only way that we have been able to track down these errors. Once you have the real SQL Server error it is a lot easier to track down the problem.justlrng wrote:![]()
![]()
![]()
![]()
Okay, I spoke a little too soon. The Direct read does not seem to pass parameters back to the database. So, basically I still need help!
Shawn Ramsey
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
-
- Participant
- Posts: 145
- Joined: Fri May 02, 2003 9:59 am
- Location: Seattle, Washington. USA
If Ascential would pass the real error messages back in the OLEDB and SQL Bulk stages we could avoid having to use profiler. :Djustlrng wrote:Well, we finally figured it out with the help of the profiler. single quotes are required so that the code will be:
sch_yr = RIGHT('#Param_1#',4)
I thought we had tried everything including single and double quotes but evidently not.
Thanks again.
Shawn Ramsey
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams