[solved]Looking for missing secondary file items(left-outter join again)
I tried the car file and it works, but not working when I use my files. Can someone please help me? Thanks.
here is the car example...
TABLE FILE CAR
PRINT
COUNTRY
WHERE COUNTRY EQ 'ENGLAND' OR 'FRANCE' OR 'ITALY' OR 'W GERMANY';
ON TABLE HOLD AS EUROPE FORMAT ALPHA
END
TABLE FILE CAR
PRINT CAR
WHERE NOT COUNTRY IN FILE EUROPE
END
I got error when using my files...
TABLE FILE ITEMBL
PRINT
ITNBR
WHERE HOUSE EQ 'BVA';
ON TABLE HOLD AS H_BVA FORMAT ALPHA
END
TABLE FILE EZPXREF
PRINT
MAPICSXREF
PART_NUMBER
PART_DESCRIPTION
WHERE NOT PART_NUMBER IN FILE H_BVA;
END
1
0 NUMBER OF RECORDS IN TABLE= 51538 LINES= 51538
0
0 ERROR AT OR NEAR LINE 14 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: 21378-10
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
It also give me error when I using H_BVA for my secondary join file. What would be the best approach in this case?
Thanks,
HuaThis message has been edited. Last edited by: Hua,
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
June 11, 2009, 03:00 PM
Francis Mariani
Is the length of the PART_NUMBER field in file EZPXREF a minimum of 8 characters?
Also, if your hold file H_BVA contains 51538 lines, this probably will not work because there are limits to how much data can be in a hold file used in a WHERE statement.
Why not join the two files on the common field and have only one TABLE with the only first WHERE statement?
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 11, 2009, 03:14 PM
Hua
Francis, thank you for your reply.
Both field lengths are 15A.
I tried using join, I seem to have problem joining to a temporary hold file(H_BVA). I didn't join to the DB2 file because ITNBR is not unique and giving me false match.
TABLE FILE ITEMBL
PRINT
ITNBR
WHERE HOUSE EQ 'BVA';
ON TABLE HOLD AS H_BVA FORMAT ALPHA
END
JOIN
LEFT_OUTER EZPXREF.EZPXREF.PART_NUMBER IN EZPXREF TO MULTIPLE H_BVA.H_BVA.ITNBR
IN H_BVA AS J9
END
TABLE FILE EZPXREF
PRINT
MAPICSXREF
PART_NUMBER
PART_DESCRIPTION
ITNBR
END
1
0 NUMBER OF RECORDS IN TABLE= 51538 LINES= 51538
0
(FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED
What am I doing wrong here?
Thanks,
HuaThis message has been edited. Last edited by: Hua,
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
June 11, 2009, 03:31 PM
Francis Mariani
If ITMNBR is not unique then you should be using SUM and BY:
TABLE FILE ITEMBL
SUM ITNBR
BY ITNBR NOPRINT
WHERE HOUSE EQ 'BVA';
ON TABLE HOLD AS H_BVA FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END
FIELD OUT OF SEQUENCE occurs because the values in the hold file are not sorted.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 11, 2009, 03:47 PM
Hua
Thank you.
I need to sort the from field PART_NUMBER to get it working.This message has been edited. Last edited by: Hua,
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
June 12, 2009, 03:54 PM
PBrightwell
If you are going to be doing a join, hold H_BVA FORMAT FOCUS INDEX ITNMBR
and you won't have to sort EZPXREF. Also, you will only have unique instances of ITNBR so you don't need a 1 to many join
Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes
June 12, 2009, 04:11 PM
Hua
Hi Pat,
quote:
H_BVA FORMAT FOCUS INDEX ITNMBR
I can't use H_BVA for the secondary file in the join, it gives me this error:
0 NUMBER OF RECORDS IN TABLE= 51538 LINES= 51538
(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT:
H_BVA.H_BVA.ITNBR
BYPASSING TO END OF COMMAND
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: H_NFD
BYPASSING TO END OF COMMAND
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: H_NFD2
BYPASSING TO END OF COMMAND
somehow I was told it's only working when join-to hold file is ALPHA, not FOCUS. Can you explain it?
Thanks,
Hua
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
June 16, 2009, 09:34 AM
PBrightwell
Try this:
SET ALL=PASS
FILE ITEMBL
BY
ITNBR
WHERE HOUSE EQ 'BVA';
ON TABLE HOLD AS H_BVA FORMAT FOCUS INDEX ITNBR
END
JOIN
LEFT_OUTER EZPXREF.EZPXREF.PART_NUMBER IN EZPXREF TO ITNBR
IN H_BVA AS J9
END
TABLE FILE EZPXREF
PRINT
MAPICSXREF
PART_NUMBER
PART_DESCRIPTION
ITNBR
WHERE PART_NUMBER NE ITNBR
END
Another option is to do a MATCH with your hold being OLD-NOT-NEW for unmatched items.
Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes
June 16, 2009, 12:30 PM
Hua
Pat,
I tried your solution and I don't know why it didn't work:
SET HOLDLIST = PRINTONLY
SET PASS=ALL
TABLE FILE ITEMBL
BY
ITNBR
WHERE HOUSE EQ 'BVA';
ON TABLE HOLD AS H_BVA FORMAT FOCUS INDEX ITNBR
-*ON TABLE HOLD AS H_BVA FORMAT ALPHA
END
JOIN
LEFT_OUTER EZPXREF.EZPXREF.MAPICSXREF IN EZPXREF TO UNIQUE H_BVA.H_BVA.ITNBR
IN H_BVA AS J0
END
TABLE FILE EZPXREF
PRINT
PART_NUMBER
PART_DESCRIPTION
ITNBR
BY MAPICSXREF
WHERE MAPICSXREF NE 'nomatch' OR '0';
ON TABLE NOTOTAL
ON TABLE HOLD AS H_NFD FORMAT FOCUS
END
-EXIT
0 NUMBER OF RECORDS IN TABLE= 51544 LINES= 51544
(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT:
H_BVA.H_BVA.ITNBR
BYPASSING TO END OF COMMAND
But it works for ALPHA file
SET HOLDLIST = PRINTONLY
SET PASS=ALL
TABLE FILE ITEMBL
BY
ITNBR
WHERE HOUSE EQ 'BVA';
-*ON TABLE HOLD AS H_BVA FORMAT FOCUS INDEX ITNBR
ON TABLE HOLD AS H_BVA FORMAT ALPHA
END
JOIN
LEFT_OUTER EZPXREF.EZPXREF.MAPICSXREF IN EZPXREF TO UNIQUE H_BVA.H_BVA.ITNBR
IN H_BVA AS J0
END
TABLE FILE EZPXREF
PRINT
PART_NUMBER
PART_DESCRIPTION
ITNBR
BY MAPICSXREF
WHERE MAPICSXREF NE 'nomatch' OR '0';
ON TABLE NOTOTAL
ON TABLE HOLD AS H_NFD FORMAT FOCUS
END
-EXIT
0 NUMBER OF RECORDS IN TABLE= 51544 LINES= 51544
0 NUMBER OF RECORDS IN TABLE= 21526 LINES= 21526
Do you see why? I also tried Match, but couldn't get the desired result.