how to use IN-Clause with Parameter - select * from test where id in (%1)

Thomas Heusser

Hallo,is there a way to useselect * from test where id in (%1)Problem: the ...id in (%1)...is translated to SQL as... id in ('123, 234, 345, 456') ... which does not match the need (the List of all ID's is translated to one string).Thomas

praveen_nagarajan

Message from Praveen Nagarajan <praveen@supaisystems.com> via eLink

Hi Thomas,

You can user the following SQL functions to get your desire results.

Run the below function in your database

CREATE FUNCTION [<dbusername>].[StrSplitToNum](@String varchar(8000), @Delimiter char(1))�

returns @temptable TABLE (items varchar(8000))�

as�

begin�

declare @idx int�

declare @slice varchar(8000)�

select @idx = 1�

if len(@String)&lt;1 or @String is null return�

while @idx!= 0�

begin�

set @idx = charindex(@Delimiter,@String)�

if @idx!=0�

set @slice = left(@String,@idx - 1)�

else�

set @slice = @String�

if(len(@slice)&gt;0)�

insert into @temptable(Items) values(@slice)�

set @String = right(@String,len(@String) - @idx)�

if len(@String) = 0 break�

end�

return�

end

Use the following query to get the output:

select * from test where id in ( �SELECT * FROM��<dbusername>.StrSplitToNum( %1, ',' )�)

WHERE %1 is user input string in LiveReport.

I hope this helps.

Best Regards

Praveen

On Wed, Apr 27, 2011 at 5:00 PM, eLink Discussion: Live Reports Discussion <livereportsdiscussion@elinkkc.opentext.com> wrote:

how to use IN-Clause with Parameter - select * from test where id in (%1)

�

Posted by thomas.heusser@infocentricresearch.com (Heusser, Thomas) on 2011/04/27 07:29

� Hallo, is there a way to use select * from test where id in (%1) Problem: the ...id in (%1)... is translated to SQL as ... id in ('123, 234, 345, 456') ... which does not match the need (the List of all ID's is translated to one string). Thomas

---

[To reply to this thread, use your normal E-mail reply function.]
Discussion:	Live Reports Discussion
Livelink Server:	knowledge-wlweb01

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.