Problem using Lookup

Userlevel 5
Badge +1

CData Arc™ 2023 - 23.4.8789.0


I’m trying to use a SQL Server Lookup to determine if a matching record exists or not

Here are my settings





If I use “Use NULL Value” I do not get the Lookup-consignmentno header at all.

If I use “Use Empty String” I get an Error “Lookup: Field cannot be null”

In the logs I get 

[2024-07-11T11:23:07.371] [Info] SQLCommand: SELECT `consignmentno` FROM `woodside`.`pallettrackduplicateprevention` WHERE (`consignmentno` = @p1); @p1='EPB0925'
[2024-07-11T11:23:07.371] [Info] [DRIVER] 2024-07-11T11:23:07.371+01:00 2 [3553|Q-Id] [EXEC|Parsed] Executing query: [SELECT [consignmentno] FROM [woodside].[pallettrackduplicateprevention] WHERE [consignmentno] = @p1]
[2024-07-11T11:23:07.371] [Info] [DRIVER] 2024-07-11T11:23:07.371+01:00 2 [3553|Q-Id] [EXEC|Parsed] Parameter: p1 = EPB0925
[2024-07-11T11:23:07.371] [Info] [DRIVER] 2024-07-11T11:23:07.371+01:00 3 [3553|Q-Id] [TCP |Send ] Send Begin - Length: 242
[2024-07-11T11:23:07.371] [Info] [DRIVER] 2024-07-11T11:23:07.371+01:00 3 [3553|Q-Id] [TCP |Send ] Send End.
[2024-07-11T11:23:07.371] [Info] [DRIVER] 2024-07-11T11:23:07.371+01:00 3 [3553|Q-Id] [TCP |Receiv] DataIn, Length: 65
[2024-07-11T11:23:07.371] [Info] [DRIVER] 2024-07-11T11:23:07.371+01:00 2 [3553|Q-Id] [EXEC|Messag] Executed query: [SELECT [consignmentno] FROM [woodside].[pallettrackduplicateprevention] WHERE [consignmentno] = @p1] Success: (0 ms)
[2024-07-11T11:23:07.371] [Info] -1 row(s) affected.
[2024-07-11T11:23:07.371] [Debug] Result column(s): (consignmentno)
[2024-07-11T11:23:07.371] [Debug] No record matched the query.
[2024-07-11T11:23:07.371] [Error] Lookup: Field cannot be null.
Stack Trace:
at Arc234p.eks.XM(Message , String )
at Arc234p.eks.Xd(Message , String , String )
at Arc234p.eks.ATP(Message )
Inner Exception: Field cannot be null.
Inner Stack Trace:
at Arc234p.TmU.Qb(String , String , Object[] )
at Arc234p.lam.AddMessageHeader(String , String , String )
at Arc234p.eks.XM(Message , String )
[2024-07-11T11:23:07.371] [Info] Message FetchFileFromVigo-20240711-103014693-z39K_1V3A6Y03N5K5 is finalized. Name: EPB0925.xml, Type: Input, Status: Error, Message: Field cannot be null., Processing Time: 250ms.



The header *is* set



2 replies

Userlevel 2

Hi Russel,


It looks like there could be an issue when you select "Use Empty String" from the drop-down. Do you still need an empty string for `consignmentno` when no matching records are found?

Selecting "Use Null Value" can pass the file successfully when no matching records are found in the table.


Would you like to continue processing the file when no records are matched from the table?


Userlevel 6

To clarify, the behavior that you are seeing when you select “Use NULL value” is expected here - the headers that accompany the message are essentially strings, and the only way to use a null value here is the omission of the header. 

It’s not expected that “Use empty string” return an error, however - this should return a header with an empty string value, and we have opened this with our development team to resolve in a future update.