[SOLVED] Multiple Conditional Joins does not work?

March 23, 2010, 05:22 PM

Francis Mariani

I was hoping to have over 10 Multiple Conditional Joins in a program, but just having two gives me this error:

 
(FOC1028) THIS TYPE OF RECURSIVE JOIN IN NOT SUPPORTED
(FOC1132) INSUFFICIENT STORAGE.  LN

All the files are flat files for the moment, until they get added to the database.

This is the fex:

JOIN LEFT_OUTER FILE R104H001 AT R104H001.CIS_2_QTR_AGO
TO UNIQUE FILE BSLRPT_TXT_CB_CI_SCR AT CIS2.CI_SCR_LABEL TAG CIS2 AS CIS2
WHERE R104H001.CIS_2_QTR_AGO GE CIS2.CI_SCR_LO AND R104H001.CIS_2_QTR_AGO LE CIS2.CI_SCR_UP;
END

JOIN LEFT_OUTER FILE R104H001 AT R104H001.CIS_1_QTR_AGO
TO UNIQUE FILE BSLRPT_TXT_CB_CI_SCR AT CIS1.CI_SCR_LABEL TAG CIS1 AS CIS1
WHERE R104H001.CIS_1_QTR_AGO GE CIS1.CI_SCR_LO AND R104H001.CIS_1_QTR_AGO LE CIS1.CI_SCR_UP;
END

JOIN LEFT_OUTER FILE R104H001 AT R104H001.CIS
TO UNIQUE FILE BSLRPT_TXT_CB_CI_SCR AT CI_SCR_LABEL TAG CIS AS CIS
WHERE R104H001.CIS GE CI_SCR_LO AND R104H001.CIS LE CI_SCR_UP;
END
-RUN

TABLE FILE R104H001
PRINT
R104H001.CIS_2_QTR_AGO
SEG.CIS2.CI_SCR_LABEL

R104H001.CIS_1_QTR_AGO
SEG.CIS1.CI_SCR_LABEL

R104H001.CIS
SEG.CIS.CI_SCR_LABEL

WHERE READLIMIT EQ 1000
END
-RUN

Commenting-out any two of the joins and related fields will make this work, having two joins and related fields will make this fail. I'm hoping I get confirmation that this happens ONLY because all these files are flat files, otherwise there's no point in me asking the DBA to add the files as DB2 tables.

This message has been edited. Last edited by: Kerry,

March 23, 2010, 05:34 PM

GinnyJakes

quote:
The conditional join is supported for FOCUS and all relational data adapters.

I found this here:
http://documentation.informati...t/source/topic94.htm
Francis, I went to check this out because I was pretty sure that conditional joins didn't work against fixed files. Maybe you could do a quick load into FOCUS files to confirm that what you want to do is possible.

March 23, 2010, 05:41 PM

Francis Mariani

Ginny!

Why didn't I think of that?!

I'll make them all FOCUS DB's and report back here.

Thanks for nudging my brain into thinking again!

March 23, 2010, 05:50 PM

GinnyJakes

It's the sleepy time of the day. I totally understand. I can't wait to hear the results.

March 23, 2010, 06:15 PM

Francis Mariani

It works with FOCUS databases! I've tried it with three - now to expand to seven!

Thanks so much Ginny!

This message has been edited. Last edited by: Francis Mariani,

March 23, 2010, 06:39 PM

Francis Mariani

Seven Conditional Joins work!

Hallelujah, it's a miracle!

March 23, 2010, 08:43 PM

j.gross

By the nature of a join to a flat file, the flat file is read sequentially in parallel with the host, and the two need to be conformally sorted. Multiple joins to the same flat file, with or without the "conditional" syntax, wouldn't yield useful results.