Have the CASE rules changed in FB4?
The next SQL stopped running on FB4 with error "Datatypes are not
comparable in expression CASE".
SQL> select
CON> STORE.ID STORE_ID, STORE.CODE STORE_CODE, STORE.DESCRIPTION
STORE_NAME, STORE.FISCALMODULE,
CON> STORE.COMPANY_ID,
CON>
CON> case
CON> when cast(123123123 as BIGINT) = 19000 then 0
CON> when PARENT_STFAM.DELETED = 1 then 1
CON> else STORE_FAMILY.DELETED end DELETED
CON>
CON>
CON> from STORE
CON> LEFT JOIN STORE_FAMILY on STORE.ID = STORE_FAMILY.STORE_ID and
CON> STORE_FAMILY.FAMILY_ID = 19000
CON> LEFT JOIN STORE_FAMILY PARENT_STFAM on PARENT_STFAM.STORE_ID = STORE.ID
and
CON> PARENT_STFAM.FAMILY_ID = 19000
CON> JOIN COMPANY on COMPANY.ID = STORE.COMPANY_ID
CON> where STORE.DELETED = 0 and STORE.STATUS <> 2 and COMPANY.DELETED = 0
CON> ORDER BY STORE.ID ;
Statement failed, SQLSTATE = HY004
SQL error code = -104
-Datatypes are not comparable in expression CASE
SQL>
but changing the case to :
case
when cast( 123123123 as BIGINT) = 19000 then 0
when cast(PARENT_STFAM.DELETED as BIGINT) = 1 then 1
else cast(STORE_FAMILY.DELETED as integer) end DELETED
the statement runs ok
FB4 beta 2 ( 1963 )
DELETED datatype is SMALLINT
jGoncalves
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
