Skip to main content
Solved

Problem using Lookup

  • July 11, 2024
  • 3 replies
  • 92 views
R
Forum|alt.badge.img+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

 

 

Best answer by James B

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.  

    This topic has been closed for replies.

    3 replies

    lohith
    • lohith
    • Employee
    • July 11, 2024

    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
      Forum|alt.badge.img
      • James B
      • Employee
      • Answer
      • July 11, 2024

      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
        • lohith
        • Employee
        • July 25, 2024

        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
        State of AI Data Connectivity Report: 2026 Outlook
        Terms and ConditionsCookie settingsAccessibility statement