Execute the Query based on dynamic values

Question

Post reply

Execute the Query based on dynamic values

karthikly.bng

SSC Rookie

Points: 48
More actions
March 7, 2011 at 12:09 pm

#246582

Hi All,
I have to pull the data from Oracle using SQL command. I am using OLEDB connection.
For example: Assume that below is my source query.
Select ProjectID, ProjectName from tabProject where projectID in ('100','200');
In the above query WHERE clause projectIDs are hard coded. If ID changes then I have to modify the values in the SQL query too.
ProjectID for which I need to pull the data is available in SQL Server DB, tabProjectID table contains the ProjectIDs for which I need to pull the data from Oracle.
Now what I am trying to do is instead of hardcoding the values in the SQL query I want to pull the projectIDs from SQL Server DB and use it in my SQL query that will run against Oracle.
Can anyone please guide me on this?
Thanks,
Karthik.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rgerald 53299 Valued Member Points: 63 More actions · Answer 1

Modify your WHERE clause as: WHERE ProjectID IN (SELECT ProjectID FROM tabProjectID)

Then all you have to do is to change the values in the tabProjectID table and never touch the code in the query.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Or join to the table.

Select ProjectID, ProjectName

from tabProject p

join tabProjectID pid on p.ProjectID = pid.ProjectID

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 3

but he mentioned he's pulling from oracle...so joining a linked server table to your local table will threaten performance, because the command will copy the entire oracle table to tempdb, then do the join to the temp data, and then return results.

how many items are you joining against? it might be more valuable to tackle it differently.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Lowell (3/7/2011)
but he mentioned he's pulling from oracle...so joining a linked server table to your local table will threaten performance, because the command will copy the entire oracle table to tempdb, then do the join to the temp data, and then return results.
how many items are you joining against? it might be more valuable to tackle it differently.

Good point, guess I just sort of skipped over the Oracle bit. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

karthikly.bng SSC Rookie Points: 48 More actions · Answer 5

I am joining 3 tables in my actual query. This is a huge dataware house.

Is there any way to retrieve project IDs from SQL Server table and store it somewhere say variable. And then make use of that variable in the query which runs against Oracle.

Note: ProjectIds are in SQL Server DB. Whereas actual query runs against Oracle DB.

karthikly.bng SSC Rookie Points: 48 More actions · Answer 6

Right now I am able to generate the SQL query dynamically using "SCRIPT TASK". In the Script Task I have written the code to retrieve the projectIDs from SQL Server Table into a string and then I build the query dynamically. Then I assign this dynamically generated query to a package variable.

Script Task is followed by the Data Flow. In the Data Flow, I am selecting Variable as data access mode.

When I run the package I am getting a error:

---------------------------------------------------------------------

Error at Load Clarity & Resource Table from Oracle DB [Retrieves Data from Clarity DB [4489]]: An OLE DB error has occurred. Error code: 0x80040E0C.

An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

---------------------------------------------------------------------

Any idea why I am getting the above error.