Re: [h2] Table function not case insensitive
Ah, ignore that. It should work, there is probably a bug in propagating column type information somewhere. On Sun, 05 Mar 2017 at 12:11, Noel Grandin wrote: > 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 > 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 >> 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 h2-database...@googlegroups.com. >> To post to this group, send email to h2-da...@googlegroups.com. >> >> >> 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 h2-database+unsubscr...@googlegroups.com. >> To post to this group, send email to h2-database@googlegroups.com. >> 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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Table function not case insensitive
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 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 > 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 h2-database...@googlegroups.com. > To post to this group, send email to h2-da...@googlegroups.com. > > > 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 h2-database+unsubscr...@googlegroups.com. > To post to this group, send email to h2-database@googlegroups.com. > 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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Table function not case insensitive
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 > 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 h2-database...@googlegroups.com . >> To post to this group, send email to h2-da...@googlegroups.com >> . >> 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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Table function not case insensitive
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 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 h2-database+unsubscr...@googlegroups.com. > To post to this group, send email to h2-database@googlegroups.com. > 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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
