Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have a task that fails during CDC on three tables. They have failed with similar problem as below. I am using one table as an example. EX25. I have masked the schema name. We are loading from MySQL 8 into target Snowflake AWS.
==> 00012980: 2024-07-02T03:11:34:258900 [TARGET_APPLY ]E: RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column [1022502] (ar_odbc_stmt.c:5046)
How can we resolve this please?
Excerpt of the logs.
Failed to execute bulk insert statement. 'INSERT INTO "PROJ"."EX25" (Column 1, Column 2, Column 3, ........)
SELECT CAST ( IFF( "PROJ"."attrep_changesACC417CB1D0C8E44"."seq" >= 63 and "PROJ"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "PROJ"."attrep_changesACC417CB1D0C8E44"."col1", null ) as VARCHAR(131072)), CAST ( IFF( "PROJ"."attrep_changesACC417CB1D0C8E44"."seq" >= 63 and "PROJ"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "PROJ"."attrep_changesACC417CB1D0C8E44"."col2", null ) as VARCHAR(131072)), CAST ( IFF( "PROJ"."attrep_changesACC417CB1D0C8E44"."seq" >= 63 and "PROJ"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "PROJ"."attrep_changesACC417CB1D0C8E44"."col3", null ) as VARCHAR(131072)), CAST ( IFF( "PROJ"."attrep_changesACC417CB1D0C8E44"."seq" >= 63 and "PROJ"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "PROJ"."attrep_changesACC417CB1D0C8E44"."col4", null ) as VARCHAR(200)), CAST ( IFF(
...........
..............
"PROJ"."attrep_changesACC417CB1D0C8E44"."seq" >= 63 and "PROJ"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "PROJ"."attre
00012980: 2024-07-02T03:11:34:258900 [TARGET_APPLY ]E: Failed (retcode -1) to execute statement: INSERT INTO "PROJ"."EX25" ("col 1, col2, col3, ...
") SELECT CAST ( IFF( "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" >= 663 and "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" <= 707, "PROJ"."attrep_changesAF42D7D4807E5C45"."col1", null ) as VARCHAR(65536)), CAST ( IFF( "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" >= 663 and "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" <= 707, "PROJ"."attrep_changesAF42D7D4807E5C45"."col2", null ) as VARCHAR(65536)), CAST ( IFF( "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" >= 663 and "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" <= 707, "PROJ"."attrep_changesAF42D7D4807E5C45"."col3", null ) as VARCHAR(65536)), CAST ( IFF( "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" >= 663 and "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" <= 707, "PROJ"."attrep_changesAF42D7D4807E5C45"."col4", null ) as VARCHAR(200)), CAST ( IFF( "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" >= 663 and "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" <= 707, "PROJ"."attrep_changesAF42D7D4807E5C45"."col5", null ) as VARCHAR(100)), CAST ( IFF( "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" >= 663 and "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" <= 707, "PROJ"."attrep_changesAF42D7D4807E5C45"."col6", null ) as VARCHAR(100)), CAST ( IFF( "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" >= 663 and "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" <= 707, "PROJ"."attrep_changesAF42D7D4807E5C45"."col7", null ) as VARCHAR(100)), CAST ( IFF( "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" >= 663 and "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" <= 707, "PROJ"."attrep_changesAF42D7D4807E5C45"."col8", null ) as VARCHAR(100)), CAST ( IFF( "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" >= 663 and "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" <= 707, "PROJ"."attrep_changesAF42D7D4807E5C45"."col9", null ) as VARCHAR(100)), CAST ( IFF( "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" >= 663 and "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" <= 707, "PROJ"."attrep_changesAF... [1022502] (ar_odbc_stmt.c:5038)
00012980: 2024-07-02T03:11:34:258900 [TARGET_APPLY ]E: RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column [1022502] (ar_odbc_stmt.c:5046)
00012980: 2024-07-02T03:11:34:258900 [TARGET_APPLY ]E: Failed to execute bulk insert statement. 'INSERT INTO "PROJ"."EX25" 5","CONO25","ISOC25","VGMMethod25","TQUL25") SELECT CAST ( IFF( 663 and "PROJ"."attrep_changesAF42D7D4807E5C45"."seq" <= 707' [1022509] (cloud_bulk.c:583)
00001332: 2024-07-02T03:11:34:477665 [UTILITIES ]I: Mail "[Server\Any errors] Task3c:encountered errors" sent successfully (notification_manager.c:1923)
00006400: 2024-07-02T03:11:34:508916 [TASK_MANAGER ]W: Table 'DB'.'EX25' (subtask 0 thread 1) is suspended. (replicationtask.c:3150)
00001332: 2024-07-02T03:11:35:477768 [UTILITIES ]I: Uses proxy env variable no_proxy == 'localhost,127.0.0.1' (notification_manager.c:1776)
00001332: 2024-07-02T03:11:35:477768 [UTILITIES ]I: Found bundle for host: 0x1aedcc2f6c0 [serially] (notification_manager.c:1776)
Hello @desmondchew ,
Thanks for contacting Qlik community forum!
QLIK is interpreting a zero length string as NULL. Open the table from the bottom right of the Designer screen. Select Transform. On the screen that opens, click the Expression field for the column. Enter: CASE $ColumnName WHEN null Then ' ' Else $ColumnName END Actually this might work just as well: ifnull($ColumnName,' ')
OR
Could you please add the following Internal parameter to the snowflake endpoint and let me know it goes:
emptyFieldAsNull (enable the checkbox)
Regards,
Sachin B