Good Afternoon,

I'm trying to join two seperate files that I've not been able to successfully join at this point. We are still very new to the product and learning every day.

This is what my data looks like:

 
Table1
APLH7     P9
Item#     WHSE
2288210   2

Table2
APLH18     A3
Item#     WHSE
2288210   002

We are looking to join together the two files one is a .foc (table1) and the other is a DB2 (table2). Item codes with both being alpha join fine. When I try to join together the WHSE, we cannot get it to go. I've tried converting it in the .mas file to P3 without the zero's, however that did not work.

When I build my summary file as table1 one can I actually change the format over at that time as the best way to go?

Appreciate any and all help!

Thank you,

JC

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

John, you'll have to cast (PTOA) the WHSE number to match the DB2 format. Can you post the code that creates table 1 between [CODE][/CODE]?

Here is the build file:

  
-* File BuildBOXSHP.fex
-* File BuildBOXSHP.fex
-SET &CURRENTYEAR=&DATEYY;
-SET &ONEYEAR=&DATEYY - 1;
-SET &TWOYEAR=&DATEYY - 2;

APP PREPENDPATH imreporting
APP HOLD IMREPORTING

DEFINE FILE ITEMBACKORDERSUMMARY
YR/A4=SQL.SUBSTR(RECORD_ADD_DATE,1,4);
END

TABLE FILE ITEMBACKORDERSUMMARY
SUM
     ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.ORDERED_LINES
     ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.BACK_ORDERED_LINES
     ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.CROSS_SHIP_LINES
     ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.ORDERED_QUANTITY
     ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.BACK_ORDERED_QUANTITY
     ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.CROSS_SHIP_QUANTITY
BY  LOWEST ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.ITEM_CODE
BY  ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.WAREHOUSE
BY  LOWEST ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.YR
WHERE (ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.YR EQ '&CURRENTYEAR' OR '&ONEYEAR' OR '&TWOYEAR');
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE SET FORMULTIPLE ON
ON TABLE NOTOTAL
ON TABLE HOLD AS XBOWHSEITEM FORMAT FOCUS INDEX 'ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.ITEM_CODE'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=REPORT,
     GRAPHCOLOR='GREEN',
$
TYPE=DATA,
     COLUMN=N1,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=DATA,
     ACROSSCOLUMN=N1,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=DATA,
     ACROSSCOLUMN=N2,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=DATA,
     ACROSSCOLUMN=N3,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=DATA,
     ACROSSCOLUMN=N4,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=DATA,
     ACROSSCOLUMN=N5,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=DATA,
     ACROSSCOLUMN=N6,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=DATA,
     ACROSSCOLUMN=N7,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=DATA,
     ACROSSCOLUMN=N8,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=TITLE,
     COLUMN=N3,
     COLOR='WHITE',
     STYLE=BOLD,
$
TYPE=ACROSSVALUE,
     ACROSS=1,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
     JUSTIFY=CENTER,
$
TYPE=ACROSSTITLE,
     ACROSS=1,
     JUSTIFY=CENTER,
$
TYPE=REPORT,
     OBJECT=MENU,
     COLOR='WHITE',
     HOVER-COLOR=RGB(66 70 73),
     BACKCOLOR=RGB(102 102 102),
     HOVER-BACKCOLOR=RGB(218 225 232),
     BORDER-COLOR='WHITE',
$
TYPE=REPORT,
     OBJECT=STATUS-AREA,
     COLOR='WHITE',
     BACKCOLOR=RGB(102 102 102),
$
TYPE=REPORT,
     OBJECT=CURRENT-ROW,
     HOVER-BACKCOLOR=RGB(218 225 232),
     BACKCOLOR=RGB(200 200 200),
$
TYPE=REPORT,
     OBJECT=CALC-AREA,
     COLOR='WHITE',
     BACKCOLOR=RGB(102 102 102),
$
ENDSTYLE
END
TABLE FILE XBOWHSEITEM
SUM
     CNT.XBOWHSEITEM.SEG01.ITEM_CODE/I11 AS 'Item Entries Created'
HEADING
"Successfully Created at &DATEYYMD"
ON TABLE NOTOTAL
END

Ok so I tried this, however the SQL generated got messed up.

  
-* File BuildBOXSHP.fex
-* File BuildBOXSHP.fex
-SET &CURRENTYEAR=&DATEYY;
-SET &ONEYEAR=&DATEYY - 1;
-SET &TWOYEAR=&DATEYY - 2;
 
APP PREPENDPATH imreporting
APP HOLD IMREPORTING
 
DEFINE FILE ITEMBACKORDERSUMMARY
YR/A4=SQL.SUBSTR(RECORD_ADD_DATE,1,4);
ALPHWHSE/A3=PTOA(ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.WAREHOUSE, 'P9', 'A3');
END
TABLE FILE ITEMBACKORDERSUMMARY
SUM 
     ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.ORDERED_LINES
     ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.BACK_ORDERED_LINES
     ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.CROSS_SHIP_LINES
     ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.ORDERED_QUANTITY
     ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.BACK_ORDERED_QUANTITY
     ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.CROSS_SHIP_QUANTITY
BY  LOWEST ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.ITEM_CODE
BY  ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.ALPHWHSE
BY  LOWEST ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.YR
WHERE (ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.YR EQ '&CURRENTYEAR' OR '&ONEYEAR' OR '&TWOYEAR');
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE SET BYDISPLAY ON 
ON TABLE SET FORMULTIPLE ON 
ON TABLE NOTOTAL
ON TABLE HOLD AS XBOWHSEITEM FORMAT FOCUS INDEX 'ITEMBACKORDERSUMMARY.ITEMBACKORDERSUMMARY.ITEM_CODE' 

SQL:

  
SELECT
SUBSTR(T1."RECORD_ADD_DATE",1,4),
T1."ITEM_CODE",
T1."WAREHOUSE",
T1."ORDERED_QUANTITY",
T1."ORDERED_LINES",
T1."BACK_ORDERED_QUANTITY",
T1."BACK_ORDERED_LINES",
T1."CROSS_SHIP_QUANTITY",
T1."CROSS_SHIP_LINES",
T1."RECORD_ADD_DATE"
FROM
HSIPCORDTA/F560966 T1
WHERE
(SUBSTR(T1."RECORD_ADD_DATE",1,4) IN('2012', '2011', '2010'))
FOR FETCH ONLY;

OLD SQL:

  
SELECT
T1."ITEM_CODE",
T1."WAREHOUSE",
SUBSTR(T1."RECORD_ADD_DATE",1,4),
SUM(T1."ORDERED_LINES"),
SUM(T1."BACK_ORDERED_LINES"),
SUM(T1."CROSS_SHIP_LINES"),
SUM(T1."ORDERED_QUANTITY"),
SUM(T1."BACK_ORDERED_QUANTITY"),
SUM(T1."CROSS_SHIP_QUANTITY")
FROM
HSIPCORDTA/F560966 T1
WHERE
(SUBSTR(T1."RECORD_ADD_DATE",1,4) IN('2012', '2011', '2010'))
GROUP BY
T1."ITEM_CODE",
T1."WAREHOUSE",
SUBSTR(T1."RECORD_ADD_DATE",1,4)
ORDER BY
T1."ITEM_CODE",
T1."WAREHOUSE",
SUBSTR(T1."RECORD_ADD_DATE",1,4)
FOR FETCH ONLY;

I tried to do this on the report side instead, however it says "join field is not in the table"

  
DEFINE FILE XBOWHSEITEM
ALPHWHSE/A3=PTOA(XBOWHSEITEM.SEG01.WAREHOUSE, 'P9', 'A3');
END
JOIN
 INNER XBOWHSEITEM.SEG01.ITEM_CODE AND XBOWHSEITEM.SEG01.ALPHWHSE IN XBOWHSEITEM
 TO MULTIPLE E3ITEMA.E3ITEMA.IITEM AND E3ITEMA.E3ITEMA.IWHSE IN E3ITEMA TAG J1
 AS J1
 END
TABLE FILE XBOWHSEITEM
SUM
     XBOWHSEITEM.SEG01.ORDERED_LINES
     XBOWHSEITEM.SEG01.BACK_ORDERED_LINES
     XBOWHSEITEM.SEG01.CROSS_SHIP_LINES
BY  LOWEST XBOWHSEITEM.SEG01.ITEM_CODE
BY  LOWEST XBOWHSEITEM.SEG01.WAREHOUSE
BY  LOWEST J0.E3ITEMA.IWHSE
ACROSS LOWEST XBOWHSEITEM.SEG01.YR
WHERE XBOWHSEITEM.SEG01.ITEM_CODE EQ '&ITEM_CODE';

I could be way off with my understanding of the program.

For optimal performance, you should always allow your relational database (DB2 in this case) to do as much of the processing as possible. Joining files on two different platforms is never optimal, particularly if filtering for both data sources is minimal or join-dependent. If at all possible, you should have DB2 join your two tables and apply the filters, not WebFOCUS. If column WAREHOUSE is in fact numeric in one DB2 table and alpha in another, then there is a DBA somewhere that needs remedial training. If the WAREHOUSE column had the same format in both tables, you should be able to simply do this:

JOIN ITEM_CODE AND WAREHOUSE IN ITEMBACKORDERSUMMARY
  TO MULTIPLE IITEM AND IWHSE IN E3ITEMA AS J1
END
-*
DEFINE FILE ITEMBACKORDERSUMMARY
 YR/A4=SQL.SUBSTR(RECORD_ADD_DATE,1,4);
END
-*
TABLE FILE ITEMBACKORDERSUMMARY
 SUM
  ITEMBACKORDERSUMMARY.ORDERED_LINES
  ITEMBACKORDERSUMMARY.BACK_ORDERED_LINES
  ITEMBACKORDERSUMMARY.CROSS_SHIP_LINES
  ITEMBACKORDERSUMMARY.ORDERED_QUANTITY
  ITEMBACKORDERSUMMARY.BACK_ORDERED_QUANTITY
  ITEMBACKORDERSUMMARY.CROSS_SHIP_QUANTITY
 BY ITEMBACKORDERSUMMARY.ITEM_CODE
 BY ITEMBACKORDERSUMMARY.WAREHOUSE
 ACROSS YR
 WHERE (YR EQ '&CURRENTYEAR' OR '&ONEYEAR' OR '&TWOYEAR');
END

I assume there are columns from table E3ITEMA that need to be on the report that you forgot to include in your post, because if not, then no JOIN is necessary. Also, the MULTIPLE keyword in the JOIN statement may not be necessary because DB2 may automatically perform a one-to-many join.

If the WAREHOUSE columns in the two tables have different formats, then try this (by converting column WAREHOUSE to numeric):

JOIN ITEM_CODE AND WHSE_P WITH WAREHOUSE IN ITEMBACKORDERSUMMARY
  TO MULTIPLE IITEM AND IWHSE IN E3ITEMA AS J1
END
-*
DEFINE FILE ITEMBACKORDERSUMMARY
 YR/A4=SQL.SUBSTR(RECORD_ADD_DATE,1,4);
 WHSE_P/P9 = EDIT(WAREHOUSE);
END
-*
TABLE FILE ITEMBACKORDERSUMMARY
 SUM
  ITEMBACKORDERSUMMARY.ORDERED_LINES
  ITEMBACKORDERSUMMARY.BACK_ORDERED_LINES
  ITEMBACKORDERSUMMARY.CROSS_SHIP_LINES
  ITEMBACKORDERSUMMARY.ORDERED_QUANTITY
  ITEMBACKORDERSUMMARY.BACK_ORDERED_QUANTITY
  ITEMBACKORDERSUMMARY.CROSS_SHIP_QUANTITY
 BY ITEMBACKORDERSUMMARY.ITEM_CODE
 BY ITEMBACKORDERSUMMARY.WAREHOUSE
 ACROSS YR
 WHERE (YR EQ '&CURRENTYEAR' OR '&ONEYEAR' OR '&TWOYEAR');
END

You could use SQL.CAST or SQL.CONVERT in place of EDIT in the DEFINE for field WHSE_P. If WebFOCUS complains about the JOIN (because the WITH clause is necessary for a DEFINE-based JOIN), then move the join on the WAREHOUSE columns to a WHERE clause:

JOIN ITEM_CODE IN ITEMBACKORDERSUMMARY
  TO MULTIPLE IITEM IN E3ITEMA AS J1
END
-*
DEFINE FILE ITEMBACKORDERSUMMARY
 YR/A4=SQL.SUBSTR(RECORD_ADD_DATE,1,4);
 WHSE_P/P9 = EDIT(WAREHOUSE);
END
-*
TABLE FILE ITEMBACKORDERSUMMARY
 SUM
  ITEMBACKORDERSUMMARY.ORDERED_LINES
  ITEMBACKORDERSUMMARY.BACK_ORDERED_LINES
  ITEMBACKORDERSUMMARY.CROSS_SHIP_LINES
  ITEMBACKORDERSUMMARY.ORDERED_QUANTITY
  ITEMBACKORDERSUMMARY.BACK_ORDERED_QUANTITY
  ITEMBACKORDERSUMMARY.CROSS_SHIP_QUANTITY
 BY ITEMBACKORDERSUMMARY.ITEM_CODE
 BY ITEMBACKORDERSUMMARY.WAREHOUSE
 ACROSS YR
 WHERE (YR EQ '&CURRENTYEAR' OR '&ONEYEAR' OR '&TWOYEAR');
 WHERE (E3ITEMA.IWHSE EQ WHSE_P);
END

Let me try and show a small example of what the end result will be.

Table1
APLH7     P9    
Item#     WHSE  OrdLines  BacLines XshpLines  
2288210   2     100       50       20
2288210   3     50        0        0

Table2
APLH18     A3    Service Goal
Item#     WHSE
2288210   002    98.5
2288210   003    97.5

End Result:

Report

Service ItemCount  Order Lines  Def/Fill Rate
98.5    1          100          Define 
97.5    1          50           Define  

The ordered lines/ Backorder Lines/Cross ship lines live on one system in the format mentioned above. This logs all our stocking items daily (1+ Billion Records in this table). DB2 Format

Our purchasing system lives in another system. (There are approx 900K-1M records in this table). Also DB2

My methodology could be wrong to summarize the 1B+ table yearly at the item/whse level by year (approx 2.7Mish record), then try to add the service information from E3 direct? Should I try and build 2 summary table to link together? Or link the two large files together and just summarize it down from there?

Really appreciate all the input!

John

Normally for optimal performance, you want DB2 to perform the join, apply the filters, and summarize the records. But if the two DB2 tables live on two different systems and have two different connection strings, then performance may suffer. I don't have experience with that scenario.

This message has been edited. Last edited by: Dan Satchell,

When I encounter n issue like this my first reaction is to break it down to the simplest terms - specially when I'm looking for help on FocalPoint.

As you point out, the issue seems to be a lack of a common format for the Warehouse field. So I would strip out all th fields but one and all the code relating to years, and all the report styling and just concentrate on getting the join to work. The styling you have here is irrelevant in any case since you are simply HOLDing the result.