Accessing Files with Multiple Members Using SQL / Alteryx

[kellywalentiny]

Hello,

As we continue to transition our old Showcase queries into Alteryx and further streamline our processes, I've come across a roadblock that I haven't found anyone in our organization seems to be able to figure out. 

One of our data sources is AS400 and within that, we have multiple-member files. My showcase query code looks like this:

SELECT
AHOUSE,
AVALUC,
AITNBR,
AORDNO,
APOISQ,
AVNDNR,
AUNIT,
ANATU,
ASTDCS,
ACURCS,
AQTYOR,
ARCVST,
AINVST,
ARCVST - AINVST AS ARCVST_1,
SUM( ( ARCVST - AINVST ) * ASTDCS ) AS ARCVST_2,
AUDT1
FROM
FILES.AP010F:MBR01 A_MBR01

But the last part is referencing a member of that FILES.AP010F that errors out when I try to replicate in Alteryx. Does anyone know how to remedy this? If I pull off the MBR01 it does not retrieve the correct data.

I found this article if at all helpful:

https://www.ibm.com/support/pages/accessing-files-multiple-members-using-sql

[jamielaird]

Have you tried the CREATE ALIAS approach described in the article you linked to and in more detail here?

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/sqlp/rbafycreatingalias.htm

I don't know anything about DB2 but I was intrigued by what a multiple member file was so did some Googling.

It looks like creating an alias that refers to MBR01 and then using that alias in your FROM clause may be the way to go.

What's not totally clear to me (assuming this is the right approach) is if you should:

1. Add the CREATE ALIAS line into your SQL query in Alteryx (looking at the error message, I'm not convinced that would be allowed)
2. Run it as a Pre-SQL Statement
3. Run it manually within your DB2 database

Hopefully someone with more specific knowledge of this comes along but hope that's some help in the meantime.