OLE DB Source - SQL Command
I came across something simple on OLE DB Source last week. I was using “SQL Command” as Data Access Mode. Most of source SQL statements were simple, such as
SELECT oid,baseid
FROM STG_PAS99
WHERE baseid = ?
For the above SQL I can pass a parameter without any problem.
For one reason or the other I had to write a source SQL something like this. Yes I do know that I don’t need inside parameter if I have one outside. I am using it to explain my point plus it does improve performance.
SELECT oid,baseid
FROM STG_PAS99
INNER JOIN (
SELECT MAX(loaded), oid, baseid
FROM STG_PAS99
WHERE baseid = ?
GROUP BY oid, baseid
)
WHERE baseid = ?
If I enter the above SQL into “SQL Command Text” and click on Parameters I got the following message
SELECT oid,baseid
FROM STG_PAS99
WHERE baseid = ?
For the above SQL I can pass a parameter without any problem.
For one reason or the other I had to write a source SQL something like this. Yes I do know that I don’t need inside parameter if I have one outside. I am using it to explain my point plus it does improve performance.
SELECT oid,baseid
FROM STG_PAS99
INNER JOIN (
SELECT MAX(loaded), oid, baseid
FROM STG_PAS99
WHERE baseid = ?
GROUP BY oid, baseid
)
WHERE baseid = ?
If I enter the above SQL into “SQL Command Text” and click on Parameters I got the following message
As the error message suggests I used the SQL Command from variable to get around that issue. If you don’t know how to do that please have a look at Jamie’s Blog
Thanks
Sutha
Thanks
Sutha
0 Comments:
Post a Comment
<< Home