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,


Hua

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

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, 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,

Hua

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

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.

Thank you.

I need to sort the from field PART_NUMBER to get it working.

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

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

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

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,

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.


Thanks,

Hua