IBMi Datastore Query only Recognizing 1 of 2 Libraries

[dunncrew]

I have a Datastore pointing to an IBMi system. The files (Tables) in the Datastore are in 2 different IBMi Libraries.

I want to write a query like this, referencing files in the same datastore (but different IBMi libraries)

SELECT *

FROM FileA (which is in Library A)

JOIN FileB on FileA.Key = FileB.Key (which is in Library B)

But I get an error, because it's looking for both files to be in Library A, even though the datastore knows FileB is in Library B.

I found that I could add Library B to the ODCB Driver Library List. I thought that might be a solution, but get the same error. It appears to ONLY use the default Library , NOT the library list. When I change the Default Library, the error changes to the other Library.

If I qualify the library names in the code, it works, but we have a practice of NOT hardcoding Library names in Code.

Thoughts ?

[christian_bartels]

I guess the problem is that the ODBC driver does not understand the concept of a library list, which is unique to IBM i. If possible, you could try to switch the ODBC driver from SQL naming ("schema.table") to system naming ("schema/table"), but I don't know if that would work with the rest of your code.

Alternatively you could create a view (or DDS-described logical file) that joins the two tables as requested and access the view or logical file in your data store.

Kind regards, Christian Bartels.

[dunncrew]

The ODBC driver has a field to enter a library list, and help text suggests it should work, but it doesn't.

Thanks for the suggestions.

[dunncrew]

Thanks for the suggestions.

The ODBC connection has a specific library list field, and the help text says it should work, but it doesn't. Frustrating