[teiid-issues] [JBoss JIRA] (TEIID-3555) Like expression in select statement fails against Sybase

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-3555?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-3555:
----------------------------------
       Issue Type: Bug  (was: Feature Request)
    Fix Version/s: 8.12
         Priority: Major  (was: Minor)
      Component/s: JDBC Connector


Added a case conversion into the base sybase logic as both sybase and sql server have a similar limitation.  Also used the expression:

case when condition then 1 when not condition then 0 end

So that null is preserved (it would not be using a single when or the bit type).

> Like expression in select statement fails against Sybase
> --------------------------------------------------------
>
>                 Key: TEIID-3555
>                 URL: https://issues.jboss.org/browse/TEIID-3555
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 8.9.1
>            Reporter: Bram Gadeyne
>            Assignee: Steven Hawkins
>             Fix For: 8.12
>
>
> Sometimes I use a like statement within the select statement to evaluate whether or not some text contains a certain sub-string. e.g:
>  
> select outcome like '%died%' as died
> from patients
>  
> Some databases support this and return a boolean value but others (like sybase) don't. This results in an exception "Incorrect syntax near keyword 'like'"
>  
> Would is be possible to rewrite this for Sybase (and maybe others) to something like:
> select case when icuoutcomename like '%overleden%' then cast(1 as bit) else cast(0 as bit) end as mort
> from patients
>  
> I used cast(1 as bit) and cast(0 as bit) here because it seems like Sybase does not support true and false.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)