restriction in sql clause

[gaurav.shetti]

say i have a query of this form

select * from cases where case_id in (a,b,c, .... )

What is the maximum no of elements or ids i can have within those brackets ?

Is there any restriction ?

[mark]

There's no coded limit on the number of items in an in.
The SQL statement itself may be limited to 8k characters so that's a limitation if you put the values inline as literals. You can use a parameter to get around that limit as well as avoid SQL escapement issues.

<strfmt "%s," $cases>
<$qcases=(convert($ret, 'strlst' ))>
<SQL "select * from cases where case_id in ($qcases)">

[John]

That depends a little on how you are submitting the query. There may be an 8K limit to the size of the query in Vortex if you aren't using a parameter. If you do use a parameter then there isn't a limit.

[gaurav.shetti]

I tried using what mark had suggested. But this is the error i got
SQL command too large

I guess the variable $qcases itself is greater than 8K characters. Does that matter?

[John]

It shouldn't matter. The message suggests it was put in as a literal, not a parameter and so got included directly into the SQL rather than by reference, e.g.

<SQL "select * from cases where case_id in (" $qcases" )">

instead of

<SQL "select * from cases where case_id in ($qcases)">

[gaurav.shetti]

Okay i tried this
<strfmt "%s" $grpperson>
<$qgroupids=(convert($ret, 'strlst' ))>
<SQL ROW SKIP=$skip MAX=$maxRecordsPerPage
$startquery
$indexquery
"ohr_id in (" $qgroupids ")"
$endquery
>


$grpperson is a string like .. 124,124325,1233425,1342, ...

still i am getting error : SQL command too large

[John]

That's making it part of the literal SQL instead of a parameter. You need the comma at the end of the string, which is why there is a comma in the strfmt format string. It needs to be a parameter, eg.:

<strfmt "%s," $grpperson>
<$qgroupids=(convert($ret, 'strlst' ))>
<SQL ROW SKIP=$skip MAX=$maxRecordsPerPage
$startquery
$indexquery
"ohr_id in ($qgroupids)"
$endquery
>

[mark]

Oops, also <strfmt "%s," $grpperson> should be <sum "%s," $grpperson> to get all elements in the $grpperson array instead of only the first.

If you're getting the too long msg with the strfmt that seems to imply you already have everything concatenated into $grpperson.

[gaurav.shetti]

yes $grpperson has everything concatenated. That is values are stored as 1,2,3,4,5 into $grpperson.
So when we do a select and we get comma separated ids.

Do i still have to do
<strfmt "%s," $grpperson> and
<sum "%s," $grpperson>

[John]

Yes, you need a comma as the last character, e.g.

1,2,3,4,5,

What type is ohr_id?