Join flda in tab1 to fldb in tab2;

TABLE FILE TAB1
PRINT
FLD1
FLD2
FLD3
WHERE DATEFLD GE '20070501'
WHERE DATEFLD LE '20070601'
END
-EXIT

In the above table request, if the DATEFLD is YYMD in master file it works fine. If we have DATEFLD as HYYMDS it fails.

I thought of using DEFINE and convert the HYYMDS to YYMD using HDATE to do that filter but it takes more processing time.

Is there any way I can achieve this filter to work?

Put your DEFINE in the TAB1 master, then use that column for tests. Then, you don't have to put it in every fex...

Processing time shouldn't increase...

We dont want to fix the master file as if we move to different server and create new set of master files then we have to apply this change there also and also we should remember this change in future.

I want to fix this issue in code level.

Kamesh,

Are you able to use SQL?:

SQL
SELECT
FLD1
FLD2
FLD3
WHERE DATE(DATEFLD) BETWEEN '20070501' AND '20070601'
FROM TAB1;
TABLE
ON TABLE HOLD AS TAB_OUT
END
-EXIT

Just throwing out ideas....

Thanks for your thoughts.

I can but in this case I have to use only Master file.

Use the DT function in your where statement. It will translate properly to the Sql engine:

TABLE FILE TAB1
PRINT
FLD1
FLD2
FLD3
WHERE DATEFLD GE DT(2007-05-01 00:00:00);
WHERE DATEFLD LE DT(2007-06-01 23:59:59);
END
  

Your date field format HYYMDS prints 1999/02/05 02:05:25

- so to extract 2005 data
DATEFLD GE '2005/01/01 00:00:01' AND
DATEFLD LT '2006/01/01 00:00:01'

You get the idea.

Hope this helped !!

quote:

Originally posted by Kamesh:
Thanks for your thoughts.

I can but in this case I have to use only Master file.

Thanks everybody.

Dhagen suggestion worked.