Case insensitivity in IN statements is a property of the LHS of the expression, not the right. You'd need to find away to apply it the column on your MyTable. Either that or submit a patch that implements a case insensitive version if IN. We have similar special cases for other operators. On Sun, 05 Mar 2017 at 01:09, Chris Sunderland <[email protected]> wrote:
> Yes JobStatus column is also case insensitive and works fine when doing > just an equal sign > > jobstatus = 'any case value' > > > On Thursday, February 23, 2017 at 6:42:10 AM UTC-6, Thomas Mueller Graf > wrote: > > Hi, > > > DATABASE_TO_UPPER=FALSE > > This is for identifiers, not for values. > > You will have to check that the jobstatus column is case insensitive. > > Regards, > Thomas > > On Tue, Feb 21, 2017 at 8:34 PM, Chris Sunderland <[email protected]> > wrote: > > I'm having an issue when using the TABLE function that comparison is case > sensitive. > > http://www.h2database.com/html/functions.html#table > > Here is the query I'm using: > SET @jobstatus = ?; > SELECT * FROM mytable WHERE jobstatus IN (SELECT jobstatus FROM > table(jobstatus VARCHAR_IGNORECASE = @jobstatus); > > In code, I'm setting the @jobstatus variable with a String array. > > The result is only values inside the jobstatus column that exactly match > the case of the values I put inside the String array @jobstatus. > > Using the LOWER or UPPER function as > SELECT * FROM mytable WHERE jobstatus IN (SELECT LOWER(jobstatus) FROM > table(LOWER(jobstatus) VARCHAR_IGNORECASE = @jobstatus); > does resolve the issue but in my situation would require the user to enter > this function in our UI so it's really practical. > > Is there some setting or connection parameter that will allow this to be > case insensitive? I'm currently have to use DATABASE_TO_UPPER=FALSE in my > connection string. > > Thanks, > Chris > > > > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > > > Visit this group at https://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
