Oracle Sql statement running from VB in Excel - add text from Excel cell
Experts,
I have a program running in Excel with the Sql statement in an Excel column (A1). I would like to know if there is a way to put a list in another cell i.e (A2) with the ids that I would like to be in part of a statement such as
If classid in ('134', '145', '646', '232')
I want to put ('134', '145', '646', '232') into cell "B1" and call it into my query.
The reason for this is there are often more than 30,000 of these and if they are coded in the query, the query exceeds the allowable size.
Is there an equivalent to putting the values into multiple cells that be put into macros and then the query could be somethiing like this:
if classid in ¯o1 or classid in ¯o2,,,,,
Macro1 being contents of B1 macro2 being contents of B2
I have a program running in Excel with the Sql statement in an Excel column (A1). I would like to know if there is a way to put a list in another cell i.e (A2) with the ids that I would like to be in part of a statement such as
If classid in ('134', '145', '646', '232')
I want to put ('134', '145', '646', '232') into cell "B1" and call it into my query.
The reason for this is there are often more than 30,000 of these and if they are coded in the query, the query exceeds the allowable size.
Is there an equivalent to putting the values into multiple cells that be put into macros and then the query could be somethiing like this:
if classid in ¯o1 or classid in ¯o2,,,,,
Macro1 being contents of B1 macro2 being contents of B2
ASKER
Mortimer,
What I was hoping to do is put a string of data in a cell and call it into my query.
Cell B1 would contain this value
('134', '145', '646', '232')
I want to use the above value in my sql statement so my SQL now reads:
Select * from filea
where id in ('134', '145', '646', '232');
The challenge is how to get the value of B1 in a query (if that is possible)
On other platforms I would assign the value in B1 to a macro and then I would have a statement that looks like this:
Select * from filea
where id in ¯o
What I was hoping to do is put a string of data in a cell and call it into my query.
Cell B1 would contain this value
('134', '145', '646', '232')
I want to use the above value in my sql statement so my SQL now reads:
Select * from filea
where id in ('134', '145', '646', '232');
The challenge is how to get the value of B1 in a query (if that is possible)
On other platforms I would assign the value in B1 to a macro and then I would have a statement that looks like this:
Select * from filea
where id in ¯o
This line will append B1 to the sql statement.
strSQL = "Select * from filea " & _
"where id in " & Range("B1").TextASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In Oracle an IN-LIST has a maximum of 1000 entries.
ASKER
Worked great. Thanks
NOTE: if it really is possible that there may be up to 30K elements in the list, you should consider building a temporary table (or stored procedure) first and then reference that in your query.
-Glenn
-Glenn
ASKER
I am not sure how to do that. Can Oracle read the table in the query. Can you point me to an example.
Not sure I fully understand but if you want to be able to check if the classid is in a cell... i.e. check if '134' is in cell B2, you can check like this...
Open in new window