NAB Datasource error using NOT IN (subquery)

[myersra]

I am attempting to use a subquery to return a list of products from an inventory master file. The subquery is selecting items from an exclusion table. I want to include only those items from the inventory master file that are NOT in the subquery list. The subquery is qualified by a customer id and shipto fields. I've run my statement outside of Valence using another SQL tool and it works. However when I attempt to run it in Valence via the Datasource builder I get the "Error - No response from Server. Please check your connection" message. There isn't any messages waiting in the QSYSOPR log but I do see this under the ERRORS Portal VV00044 - Security violation: no session ID...which doesn't provide anything that I can determine if it is syntax related.

Here is my query. I am on version 6.3.20250516.0.

select mpopad, mpitem, mpdes1, mpcode, mpnutr from orderlib/minplnpf
where mpitem not in (select peitem from orderlib/padxclpf where pecust = 232757 and peshpt = 'BROD' ) and mpopad <> ' '

[Johnny Major]

Are you, by chance, using a VPN when you're trying to create or update this data source?

[myersra]

Hi Johnny....yes I am working remotely and using a VPN. My connection hasn't dropped as I am also using a green screen IBM ACS connection as well and haven't had any issues.

[Johnny Major]

I suspect the VPN is blocking the calls because it detects SQL and assumes it's an SQL injection. We have had other's experience this issue before on VPN's.

There is a setting in Portal Admin > Settings under the Nitro App Builder group labeled "Encrypt NAB SQL Clauses" Once you check that box and save the settings, you should be ready to go after closing and reopening NAB

[myersra]

I tried the Encrypt NAB SQL Clauses setting and it still throws the same error. I believe the issue is in the parser for the SQL statement. If I remove the subquery clause I can run the query through the process to retrieve data.

Here is the statement that works:
elect mpopad, mpitem, mpdes1, mpcode, mpnutr from orderlib/minplnpf

Here is the statement that doesn't with the subquery:
select mpopad, mpitem, mpdes1, mpcode, mpnutr from orderlib/minplnpf where mpitem not in (select peitem from orderlib/padxclpf where pecust = 232757 and peshpt = 'BROD' ) and mpopad <> ' '

[Johnny Major]

If the parser/regenerator doesn't support something, you would get a message indicating what it's erroring on or an "invalid SQL statement" message from the backend. However, you're stating that you're not getting a response from the backend, meaning a request was sent but never responded to. This usually indicates that the VPN has decided to block the request.

Is there a way for you to access a computer that is directly connected to the network without a VPN and create the data source with your statement? If you want, feel free to email support, and we can set up a quick online call.

I tested our parser/regenerator, and it handles the subquery with this test statement:?

SELECT
  cusno,
  cname,
  ccountry
FROM
  democmast
WHERE
  (
    cusno not in (
      SELECT
        cusno
      FROM
        democmast
      WHERE
        (ccountry <> 'United States')
    ) and ccountry <> ''
  )

[myersra]

Hi Johnny,

I noticed in your query example you had wrapped the entire where clause with parenthesis. My statement only had the parenthesis surrounding the subquery statement. When I added the extra parenthesis the query worked. I guess I will wrap my where clauses in the future with parenthesis just to be safe.

[Johnny Major]

That's interesting. I didn't manually wrap it in parentheses; the NAB SQL data source automatically ensures the WHERE clause is wrapped. I originally entered:

SELECT
  cusno,
  cname,
  ccountry
FROM
  democmast
WHERE
  cusno not in (
    SELECT
      cusno
    FROM
      democmast
    WHERE
      (ccountry <> 'United States')
  ) and ccountry <> ''