Hi,
We have 2 hold files HOLD1 AND HOLD2.
From the HOLD1 we need to get distinct codes (cod_val).
HOLD2 has following structure
lowval_code
hival_code
cod_catagory
cod_description

So basically we need to get cod_category and cod_description for the distinct values of cod_val from HOLD1
SELECT
COD_CATEGORY ,
COD_DESCRIPTION
FROM HOLD2
WHERE COD_VAL(HOLD1) BETWEEN LOWVAL_CODE AND HIVAL_CODE

Please Reply.
Thanks

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

Ideally, your hold files would already have a dummy field containing only a blank. If not, then you will need to go through the extra steps of adding them to your existing hold files. Then you can join the two hold files on the dummy field and apply your WHERE condition. Something like this:

TABLE FILE HOLD1
 SUM COMPUTE BLANK/A1 = ' ';
 BY COD_VAL
 ON TABLE HOLD AS HOLD1X
END
-*
TABLE FILE HOLD2
 SUM
  FST.COD_CATEGORY
  FST.COD_DESCRIPTION
  COMPUTE BLANK/A1 = ' ';
 BY LOWVAL_CODE
 BY HIVAL_CODE
 ON TABLE HOLD AS HOLD2X
END
-*
JOIN CLEAR *
JOIN BLANK IN HOLD1X TO ALL BLANK IN HOLD2X AS J1
-*
TABLE FILE HOLD1X
 PRINT
  HOLD2.COD_CATEGORY
  HOLD2.COD_DESCRIPTION
 BY HOLD1_COD_VAL
 WHERE (HOLD1.COD_VAL FROM LOWVAL_CODE TO HIVAL_CODE);
END

It's possible the JOIN may not work correctly. In that case try changing the HOLD statement for HOLD2x to:

ON TABLE HOLD AS HOLD2X FORMAT FOCUS INDEX BLANK

Finally, if neither of the above produce the desired results, you may need to use a conditional JOIN: Using a Conditional Join.

It Worked with ON TABLE HOLD AS HOLD2X FORMAT FOCUS INDEX BLANK.

Thanks a lot