Skip to main content
Solved

Problem using Lookup

  • 11 July 2024
  • 3 replies
  • 49 views

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] 2Info] SQLCommand: SELECT `consignmentno` FROM `woodside`.`pallettrackduplicateprevention` WHERE (`consignmentno` = @p1); @p1='EPB0925'
12024-07-11T11:23:07.371] 2Info] 1DRIVER] 2024-07-11T11:23:07.371+01:00	2	13553|Q-Id]	7EXEC|Parsed] Executing query: rSELECT econsignmentno] FROM Twoodside].epallettrackduplicateprevention] WHERE iconsignmentno] = @p1]
i2024-07-11T11:23:07.371] 2Info] 1DRIVER] 2024-07-11T11:23:07.371+01:00	2	13553|Q-Id]	7EXEC|Parsed] Parameter: p1 = EPB0925
t2024-07-11T11:23:07.371] 2Info] 1DRIVER] 2024-07-11T11:23:07.371+01:00	3	13553|Q-Id]	7TCP |Send  ] Send Begin - Length: 242
g2024-07-11T11:23:07.371] 2Info] 1DRIVER] 2024-07-11T11:23:07.371+01:00	3	13553|Q-Id]	7TCP |Send  ] Send End.
S2024-07-11T11:23:07.371] 2Info] 1DRIVER] 2024-07-11T11:23:07.371+01:00	3	13553|Q-Id]	7TCP |Receiv] DataIn, Length: 65
a2024-07-11T11:23:07.371] 2Info] 1DRIVER] 2024-07-11T11:23:07.371+01:00	2	13553|Q-Id]	7EXEC|Messag] Executed query: eSELECT xconsignmentno] FROM Twoodside].epallettrackduplicateprevention] WHERE iconsignmentno] = @p1] Success: (0 ms)
]2024-07-11T11:23:07.371] 2Info] -1 row(s) affected.
 2024-07-11T11:23:07.371] 2Debug] Result column(s): (consignmentno)
:2024-07-11T11:23:07.371] 2Debug] No record matched the query.
a2024-07-11T11:23:07.371] 2Error] 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 , Objectt] )
   at Arc234p.lam.AddMessageHeader(String , String , String )
   at Arc234p.eks.XM(Message , String )
e2024-07-11T11:23:07.371] 2Info] 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

 

 

3 replies

lohith
Userlevel 3

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?

 

Lohith
James B
Userlevel 6
Badge

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.  

lohith
Userlevel 3

Hi Russel,

I am pleased to inform you that the issue has been resolved in the latest website release of application version 24.2.8965, available at https://arc.cdata.com/support/builds/. This version allows an empty string as the lookup header value when no matching records are found (When “Use Empty String” is selected).

I see you are still using version 23 of the application. To install version 24, you will need an upgraded key. You can obtain the upgraded key through our self-service portal at https://portal.cdata.com/. Please register and get the key from there.

Let me know if you have any further questions.

 

 

Lohith

Reply


Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings