Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
desmondchew
Creator III
Creator III
3 weeks ago

Error: 100072 Message: NULL result in a non-nullable column [1022502] during CDC load

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)

 

Labels (1)
Labels
103 Views
0 Likes
1 Reply
SachinB
Support
Support
3 weeks ago

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

 

58 Views