April 4, 2011 at 2:04 pm
Query, that needs to be used in OLEDB Source(orcale)
select A, B, C, D,ADDED_DATE, CLIENT_TYPE from Table1
where CLIENT_TYPE <> 4 and ADDED_DATE > ?
This ADDED_DATE Should get, resultset from another query
"select convert(datetime,char_value) from table X where value_desc = 'Abc', making sure this query output comes to an variable Var_Z( Data type: DATE, not sure if the Datetype needs to be DATE )
and then creating another variable Var_ZZ and making sure that the evaluate expression is set true and then framing this as below:
"select A, B, C, D,ADDED_DATE, CLIENT_TYPE from Table1
where CLIENT_TYPE <> 4 and ADDED_DATE >" + @[User::Var_Z]
But don't know for some reason this is showing we wrong ....can anyone correct me if am going wrong
April 6, 2011 at 1:23 am
Hey,
What is the error that you are getting?
You can also pass parameter in your OLEDB source adapter. Select Data Access mode as 'SQL Command' then write your query like this-
select A, B, C, D,ADDED_DATE, CLIENT_TYPE from Table1
where CLIENT_TYPE <> 4 and ADDED_DATE > ?
Then go to the parameter button and setup your parameter so that date value will be fetched from the date variable User::Var_Z
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
April 6, 2011 at 9:22 am
i can't do this, as i am using Orcale OLEDB....
April 7, 2011 at 9:01 am
Hey,
In your case you are missing single quotes ('). Your query should be written inside Var_ZZ's expression property like this.
"select A, B, C, D,ADDED_DATE, CLIENT_TYPE from Table1
where CLIENT_TYPE <> 4 and ADDED_DATE > '" + @[User::Var_Z] + "'" --Single quote enclosing date
Also I am assuming
1. You have selected DataAccessMode as "SQL Command from Variable" in you OLE DB source adapter and variable value is set to User::Var_ZZ.
2. You are writing this query in your variable's expression property.
3. And that variable's "EvaluateAsExpression" property is set to True.
4. Make sure that your date variable is containing single value (as it is getting its value from another query). If not the case then you need to use IN operator instead of =.
Please make sure that you are applying these two condition.
Hope it will help.
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
April 8, 2011 at 8:57 am
Hi,
If the resulting string is intended to be Oracle SQL, you will need to supply a date mask.The parameter should be a string; assuming that it is coming from a SQL Server DATETIME, it will have a default format like '2011-04-08'. So:
select A, B, C, D,ADDED_DATE, CLIENT_TYPE from Table1
where CLIENT_TYPE <> 4 and ADDED_DATE > to_date (?, 'yyyy-mm-dd')
If you pass the string value in as a parameter, Oracle will convert it to a date. For other formats, see this manual page. HTH
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply