2543293 - SQLCode -1009145, SQLState 'QFA2E': Data type conversion is not possible. - SAP IQ

• The following SQL executed in the procedure fails with a datatype conversion error:

INSERT INTO mytable(..., app_id,...)
     SELECT...
               MAX(case when seq_val = 9 then case when d.pv is null then null
                                                                       when d.pv = 'null' then null
                                                                       when LENGTH(TRIM(d.pv)) = 0 then null
                                                                       else cast(d.pv as int)
                                                                end
                             else null
                     end) as app_id
                ...
     from (SELECT ...
                          ldt.param_value as pv,
                          ldt.param_index as seq_val
             from your_view as ldt
                 join ....) as d    
     ....;

• The error reporting in the .iqmsg file:

Exception Thrown from dflib/dfe_Cast.cxx:955, Err# 1047, tid 5 origtid 5
O/S Err#: 0, ErrID: 9216 (df_Exception); SQLCode: -1009145, SQLState: 'QFA2E', Severity: 14
Data type conversion is not possible. Failure converting from INT for expression CAST(ldt.param_value AS pv AS INT).
-- (dflib/dfe_Cast.cxx 955)

• Migrating to IQ 16.1 from IQ 15.4.

• This SQL works in IQ 15.4 with no errors.

SAP IQ 16.1

varchar, cast, convert, implicit , KBA , BC-SYB-IQ , Sybase IQ , Problem