I got an array of id's :
ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245]
I would like to get a string of strings (to be used in SQL WHERE..IN clause
" '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' "
If a use ids.join(','), I get "22443,22468,22371,22218,22472,22377,22245" which is obviously not useful for SQL....
is there any shorcuts or should I concatenate each element ?
thanks
joss
ids.map {|i| "'#{i}'"}.join(" ,")
martin
On 12/19/06, Josselin <josselin@wanadoo.fr> wrote:
I got an array of id's :
ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245]
I would like to get a string of strings (to be used in SQL WHERE..IN clause
" '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' "
If a use ids.join(','), I get
"22443,22468,22371,22218,22472,22377,22245" which is obviously not
useful for SQL....
ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245]
"IN ('"+ids.join("', '")+"')"
Shiwei
(The views expressed are my own and not necessarily those of Oracle and its affiliates.)
Josselin wrote:
I got an array of id's :
ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245]
I would like to get a string of strings (to be used in SQL WHERE..IN clause
" '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' "
If a use ids.join(','), I get "22443,22468,22371,22218,22472,22377,22245" which is obviously not useful for SQL....
is there any shorcuts or should I concatenate each element ?
thanks
joss
"'#{ids.join("','")}'"
Johan
On 12/19/06, Martin DeMello <martindemello@gmail.com> wrote:
On 12/19/06, Josselin <josselin@wanadoo.fr> wrote:
> I got an array of id's :
>
> ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245]
>
> I would like to get a string of strings (to be used in SQL
WHERE..IN clause
>
> " '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' ,
'22245' "
>
> If a use ids.join(','), I get
> "22443,22468,22371,22218,22472,22377,22245" which is obviously not
> useful for SQL....ids.map {|i| "'#{i}'"}.join(" ,")
martin
thanks Martin... I realize that the error I got was not due to integers list , "22443, 22468, 22371, 22218, 22472, 22377, 22245" is accepted... so the join(',') is ok..
but the list is too long ... for a select statement.. (I can have 50 numbers or more.....) there is a characters limit to the select
joss
On 2006-12-19 09:29:38 +0100, "Martin DeMello" <martindemello@gmail.com> said:
On 12/19/06, Josselin <josselin@wanadoo.fr> wrote:
I got an array of id's :
ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245]
I would like to get a string of strings (to be used in SQL WHERE..IN clause
" '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' "
If a use ids.join(','), I get
"22443,22468,22371,22218,22472,22377,22245" which is obviously not
useful for SQL....ids.map {|i| "'#{i}'"}.join(" ,")
martin
Maybe you can generate ranges for adjacent values. IIRC we had a thread here at some point in the past - maybe it's even a quiz.
A completely different option is to create a temp table (depends on your DB whether and how that is done), put all the values in that temp table and join it against the table you are querying.
Kind regards
robert
On 19.12.2006 10:23, Josselin wrote:
On 2006-12-19 09:29:38 +0100, "Martin DeMello" <martindemello@gmail.com> > said:
On 12/19/06, Josselin <josselin@wanadoo.fr> wrote:
I got an array of id's :
ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245]
I would like to get a string of strings (to be used in SQL WHERE..IN clause
" '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' "
If a use ids.join(','), I get
"22443,22468,22371,22218,22472,22377,22245" which is obviously not
useful for SQL....ids.map {|i| "'#{i}'"}.join(" ,")
martin
thanks Martin... I realize that the error I got was not due to integers list , "22443, 22468, 22371, 22218, 22472, 22377, 22245" is accepted... so the join(',') is ok..
but the list is too long ... for a select statement.. (I can have 50 numbers or more.....) there is a characters limit to the select
Robert Klemme wrote:
A completely different option is to create a temp table (depends on your
DB whether and how that is done), put all the values in that temp table
and join it against the table you are querying.
Talk about serendipity... I was looking for a way to avoid having to do
either any sort of query string building (which I hate) or multiple
SELECTS (for obvious reasons) for a WHERE ... IN situation for work
stuff, and this one sounds quite nifty. Would the performance of that be
comparable to using a WHERE ... IN?
David Vallner
Depends on the number of items in the temp table and probably also on indexing. My guess would be that it's pretty fast for some hundred entries. Note that not all RDBMS allow to have indexes on temp tables. MS SQL Server also has something called "table variable" which might give different results. Lots of options... 
Kind regards
robert
On 21.12.2006 01:14, David Vallner wrote:
Robert Klemme wrote:
A completely different option is to create a temp table (depends on your
DB whether and how that is done), put all the values in that temp table
and join it against the table you are querying.Talk about serendipity... I was looking for a way to avoid having to do
either any sort of query string building (which I hate) or multiple
SELECTS (for obvious reasons) for a WHERE ... IN situation for work
stuff, and this one sounds quite nifty. Would the performance of that be
comparable to using a WHERE ... IN?
Robert Klemme wrote:
Robert Klemme wrote:
A completely different option is to create a temp table (depends on your
DB whether and how that is done), put all the values in that temp table
and join it against the table you are querying.Talk about serendipity... I was looking for a way to avoid having to do
either any sort of query string building (which I hate) or multiple
SELECTS (for obvious reasons) for a WHERE ... IN situation for work
stuff, and this one sounds quite nifty. Would the performance of that be
comparable to using a WHERE ... IN?Depends on the number of items in the temp table and probably also on
indexing. My guess would be that it's pretty fast for some hundred
entries. Note that not all RDBMS allow to have indexes on temp tables.
MS SQL Server also has something called "table variable" which might
give different results. Lots of options...Kind regards
robert
I don't really need an index, it's a throwaway temp table. I just want
to avoid either handcoding parameter escaping or making a parameterised
query using "WHERE foo IN (?,?,?, [...] ?)" and setting those I have an
unrational fear of looping over integers; argh pure JDBC apps, yay lack
of common sense and using an API designed to do the lowest level of
abstraction for application programming. The query is rather small,
probably in the order of tens of items at best, so a batch insert into
the temp table and then the join shouldn't hurt performance over the
hackish method and still end up better than doing selects one by one
because of the reduced lag. Although my hunch is what's killing that one
is that it involves basically a join on (unindexed) varchar columns, so
I don't expect miracles 
Now, if only this weren't Oracle 8i that only supports that weird
"predefined table for temporary data" concept instead of true
transaction-scoped temp tables... *sigh*
David Vallner
On 21.12.2006 01:14, David Vallner wrote:
Keep in mind that temp-tables in MSSQL are still written to disk in
TempDB. So as a general rule, if you're concerned about performance, if
you don't *need* a temp-table, don't use it.
Not that performance should always be a #1 concern of course. Just that
I've seen hundreds if not thousands of stored-procedures that use
temp-tables as a matter of course just because the developer wasn't
comfortable with sub-selects, grouping, etc. I'm sure you won't fall
into that trap though.
There are also in-memory tables, but I don't remember the caveats. I
think perhaps they might have a global scope or something inconvienent
like that, but don't quote me on that.
-Sam
David Vallner wrote:
Robert Klemme wrote:
> On 21.12.2006 01:14, David Vallner wrote:
>> Robert Klemme wrote:
>>> A completely different option is to create a temp table (depends on your
>>> DB whether and how that is done), put all the values in that temp table
>>> and join it against the table you are querying.
>>
>> Talk about serendipity... I was looking for a way to avoid having to do
>> either any sort of query string building (which I hate) or multiple
>> SELECTS (for obvious reasons) for a WHERE ... IN situation for work
>> stuff, and this one sounds quite nifty. Would the performance of that be
>> comparable to using a WHERE ... IN?
>
> Depends on the number of items in the temp table and probably also on
> indexing. My guess would be that it's pretty fast for some hundred
> entries. Note that not all RDBMS allow to have indexes on temp tables.
> MS SQL Server also has something called "table variable" which might
> give different results. Lots of options...
>
> Kind regards
>
> robert
>I don't really need an index, it's a throwaway temp table. I just want
to avoid either handcoding parameter escaping or making a parameterised
query using "WHERE foo IN (?,?,?, [...] ?)" and setting those I have an
unrational fear of looping over integers; argh pure JDBC apps, yay lack
of common sense and using an API designed to do the lowest level of
abstraction for application programming. The query is rather small,
probably in the order of tens of items at best, so a batch insert into
the temp table and then the join shouldn't hurt performance over the
hackish method and still end up better than doing selects one by one
because of the reduced lag. Although my hunch is what's killing that one
is that it involves basically a join on (unindexed) varchar columns, so
I don't expect miraclesNow, if only this weren't Oracle 8i that only supports that weird
"predefined table for temporary data" concept instead of true
transaction-scoped temp tables... *sigh*David Vallner
--------------enig8B737F04AD777652669F493C
Content-Type: application/pgp-signature
Content-Disposition: inline;
filename="signature.asc"
Content-Description: OpenPGP digital signature
X-Google-AttachSize: 188
Sam Smoot wrote:
Keep in mind that temp-tables in MSSQL are still written to disk in
TempDB. So as a general rule, if you're concerned about performance, if
you don't *need* a temp-table, don't use it.
Well, the Powers That Be said "it's probably the query lag". So this
should clear it. My opinion is that it's the varchar column that could
use a unique index constraint, and since it's a read-only rather small
table (in the order of tens of records, maybe hundreds at most) from the
POV of the client app I'm working on, I'd prefer to just prefetch it on
startup and stop fooling around.
Not that performance should always be a #1 concern of course. Just that
I've seen hundreds if not thousands of stored-procedures that use
temp-tables as a matter of course just because the developer wasn't
comfortable with sub-selects, grouping, etc. I'm sure you won't fall
into that trap though.
Erm. Using a temp table to store data that -already is- in the DB? Eugh.
I presume that's the same kind of developer that's not comfortable with
nesting function calls and gets into 9 levels of indentation and umpty
local variables. And if I have the necessary privileges on a DB, any and
all subselects I see are very good candidates to be axed and put into a
view.
There are also in-memory tables, but I don't remember the caveats. I
think perhaps they might have a global scope or something inconvienent
like that, but don't quote me on that.
This is Oracle 8i, working in Mysterious Ways (tm), and the only temp
tables you get are globally scoped with locally scoped data. Luckily I
think developers have create table rights, so it should be possible to
sneak this in.
Right, thanks for the hints!
David Vallner
Keep in mind that temp-tables in MSSQL are still written to disk in
TempDB. So as a general rule, if you're concerned about performance, if
you don't *need* a temp-table, don't use it.
The fact that they reside in tempdb does not necessarily mean they are written to disk or that they are slow. Small temp tables will easily fit into the page cache. And since they are created directly before usage likelihood of finding those pages in the cache is pretty high. Also, tempdb has recovery model simply which reduces burden on the disk somewhat. Also, I am not sure whether operations on temp tables are logged at all.
Not that performance should always be a #1 concern of course. Just that
I've seen hundreds if not thousands of stored-procedures that use
temp-tables as a matter of course just because the developer wasn't
comfortable with sub-selects, grouping, etc. I'm sure you won't fall
into that trap though.
Yeah, that's what I heard also: often people use temp tables because they do not know SQL and the capabilities of their RDBMS very well.
There are also in-memory tables, but I don't remember the caveats. I
think perhaps they might have a global scope or something inconvienent
like that, but don't quote me on that.
They are called "table variables". Scope is not an issue, they are scoped like local variables (unless maybe if they are returned from a procedure). IIRC limitation is that they do not allow indexes and constraints.
Kind regards
robert
PS: Please don't top post.
On 22.12.2006 01:34, Sam Smoot wrote:
The fact that they reside in tempdb does not necessarily mean they are
written to disk or that they are slow. Small temp tables will easily
fit into the page cache. And since they are created directly before
usage likelihood of finding those pages in the cache is pretty high.
Also, tempdb has recovery model simply which reduces burden on the disk
somewhat. Also, I am not sure whether operations on temp tables are
logged at all.
I think you've got it right about whether a temp-table _must_ be
written to disk during it's lifetime. Looking it up just now I found
conflicting accounts, so I can't authoritatively say either way. :-/
> There are also in-memory tables, but I don't remember the caveats. I
> think perhaps they might have a global scope or something inconvienent
> like that, but don't quote me on that.They are called "table variables". Scope is not an issue, they are
scoped like local variables (unless maybe if they are returned from a
procedure). IIRC limitation is that they do not allow indexes and
constraints.
It looks like I was actually thinking about global temp-tables using
the ##table syntax. Shows how often I've found a use for them I
suppose.
PS: Please don't top post.
Sorry about that. :-o