I have to do a multiple join between 2 hold file and this is the case:

The two fields of the join for first hold are:

Grp segment 'A15V'
Month 'P4'

The two fields of the join for Second hold are:

Grp segment 'A15V'
Vim 'A2V' (Contain only numeric values)


What could you suggest to make the best join for this case ??

Thank you in advance .

This message has been edited. Last edited by: <Kathryn Henning>,

You cannot JOIN hold files based on a key that it's not the same format and you can JOIN only one field that must be indexed for the second one, at least, when it's a FOCUS files.

What is the relationship between Month and Vim ? Are they both containing Month number ?

If so:
1- convert P4 to A4
2- convert A2V to A4
3- merge both : A4 || A15V to create new key field for both files then this new key field can be used to JOIN

The Vim contain these values: 00,01,10,11,20,21,28,29,30,31,38,39

Do you suggest to create the new defines inside of the hold files or outside ??

So Vim it's not a month number (01-12), it's a number of months (28, 29, 30 months) ?

Does Month from the other file have same values than Vim ?

Is there a relationship between Month and Vim ?

And yes I suggest to create the new DEFINES at each creation of the HOLD files where you'll be able to INDEX on that new fields and then JOIN them. This way you'll save one step.

And have something like this:

DEFINE FILE a
MonthX /A4 = EDIT(Month);
KEYA   /A19 = GrpSegment || MonthX;
END
TABLE FILE a
PRINT ...
BY KEYA
BY ...
ON TABLE HOLD AS FILA FORMAT FOCUS INDEX KEYA
END
-RUN

DEFINE FILE b
VimX /A4 = Vim;
KEYB /A19 = GrpSegment || VimX;
END
TABLE FILE b
PRINT ...
BY KEYB
BY ...
ON TABLE HOLD AS FILB FORMAT FOCUS INDEX KEYB
END
-RUN

JOIN KEYA IN FILA
TO   KEYB IN FILB TAG J01 AS J01
END

TABLE FILE FILA
PRINT ...
BY ...
WHERE KEYA EQ KEYB;
END
-RUN

For the month i have a month number (1-12).

Actualy i dont know why they use this join between Month and VIM,I have to migrate a report from BO to webfocus that's why i have to use the same join in order to have a correct result.

For the index i have to add it manualy by code ? Is there others method to do it by infoassist ?

Not sure about InfoAssist, not familiar with it but I know that you can JOIN files together, will it create its INDEX by itself, I don't know.

Suggestion : create the new DEFINES and try to create your JOIN based on them with IA. You will see if it's feasible or not...

quote:

FOCUS INDEX

I created the defines and join but when i launched the document on mode deferred i didnt have a result, it falls into Unknown.

Try running it interactive first one piece at a time to insure that every steps are working properly.

I tried to run the hold file seperatly and this is the result of the hold file that contain the conversion from P4 to A4 :

WebFOCUS was called with an invalid request.

For the second hold file i have the data.

Share your code for the first HOLD file that generate the error.

*-HOLD_SOURCE
-*COMPONENT=Define_DWH_ACQUIRING_BY_MERCHANT_CREDIT
DEFINE FILE DWH_ACQUIRING_BY_MERCHANT_CREDIT
Var_Month/A4=PTOA(DWH_ACQUIRING_BY_MERCHANT_CREDIT.FINPROCESSING_DATE.MONTH_NO,'(P4)','A4' );
Var_concat/A19=DWH_ACQUIRING_BY_MERCHANT_CREDIT.GRP_MAIN.VIOL_VIM || Var_Month ;
END
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE DWH_ACQUIRING_BY_MERCHANT_CREDIT
SUM DWH_ACQUIRING_BY_MERCHANT_CREDIT.TXN_NBR_BY_DATE_FIN.DF_TRX_NBR_FIN_YTD_1
DWH_ACQUIRING_BY_MERCHANT_CREDIT.TXN_NBR_BY_DATE_FIN.DF_TRX_NBR_FIN_YTD
DWH_ACQUIRING_BY_MERCHANT_CREDIT.TXN_ACNT_COM_AMT_BY_DATE_FIN.DF_TRX_COM_CMT_FIN_YTD
DWH_ACQUIRING_BY_MERCHANT_CREDIT.TXN_ACNT_COM_AMT_BY_DATE_FIN.DF_TRX_COM_CMT_FIN_YTD_1
DWH_ACQUIRING_BY_MERCHANT_CREDIT.TXN_ACNT_AMT_BY_DATE_FIN.DF_TRX_CMT_FIN_YTD
DWH_ACQUIRING_BY_MERCHANT_CREDIT.TXN_ACNT_AMT_BY_DATE_FIN.DF_TRX_CMT_FIN_YTD_1
BY Var_concat
BY DWH_ACQUIRING_BY_MERCHANT_CREDIT.FINPROCESSING_DATE.YEAR_NO
BY DWH_ACQUIRING_BY_MERCHANT_CREDIT.GRP_MAIN.GROU_GROUP_SEGMENTATION_CODE
BY DWH_ACQUIRING_BY_MERCHANT_CREDIT.FINPROCESSING_DATE.MONTH_NO
BY DWH_ACQUIRING_BY_MERCHANT_CREDIT.TXN_DETAILS.BRAND_CODE
BY DWH_ACQUIRING_BY_MERCHANT_CREDIT.TXN_DETAILS.DF_BRAND_CODE
BY DWH_ACQUIRING_BY_MERCHANT_CREDIT.GRP_ADDRESS.GRAD_COUNTRY_CODE
BY DWH_ACQUIRING_BY_MERCHANT_CREDIT.TXN_DETAILS.DF_AREA_CARDHOLDER_COMPANY
BY DWH_ACQUIRING_BY_MERCHANT_CREDIT.TXN_DETAILS.AREA_COMPANY
WHERE DWH_ACQUIRING_BY_MERCHANT_CREDIT.FIL_PROCESSING_DATE.FI_YEAR_PREVYEAR ;
ON TABLE HOLD AS HF_q_ytd FORMAT BINARY
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET ASNAMES MIXED
ON TABLE SET HOLDATTRS ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES MIXED
ON TABLE SET HOLDATTRS ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, PAGESIZE=A4, HFREEZE=OFF, $
ENDSTYLE
END
-RUN

Why are you concatenating VIM and Month ?
I thought that your keys for the JOIN must be (GrpSegment + Vim) = (GrpSegment + Month) as you stated in your first post.

You are creating an HOLD file, so rid off:

ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET ASNAMES MIXED
ON TABLE SET HOLDATTRS ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES MIXED
ON TABLE SET HOLDATTRS ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, PAGESIZE=A4, HFREEZE=OFF, $
ENDSTYLE

No use to have it there.

Try to isolate the error, execute your code without the DEFINES, then add them one at the time.

I'm almost sure that:

Var_Month/A4=PTOA(DWH_ACQUIRING_BY_MERCHANT_CREDIT.FINPROCESSING_DATE.MONTH_NO,'(P4)','A4' );

is invalid. A P4 is a packed decimal is need more space than only 4 characters...

Try displaying DWH_ACQUIRING_BY_MERCHANT_CREDIT.FINPROCESSING_DATE.MONTH_NO and Var_Month to insure that they display the same value.

Yes The right join is : (GrpSegment + Vim) it was a mistake when i pasted the code.

When i execute the report without the DEFINES it work fine.

I think that this error 'WebFOCUS was called with an invalid request.' is genereted when the quantity of data is big , because when i tried to execute the report with a small period for example 1 month it work fine.

What do you think about that ?

Process all your development with a small set of data and validate.
Then once you're sure that it works and provide proper report, try why more and more data (2 months, 6 months, ...)

It's possible that you'll end with a differed execution. But at that point you'll have to insure that your users will only have the differed execution option if it's the only way. Otherwise they will have the same issue as you do