Date Issue - Topic

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?

WFConsultant

WF 8105M on Win7/Tomcat

Posts: 780 | Location: Florida | Registered: January 09, 2005

Tom Flynn

Expert

posted

Hide Post

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

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006

Ignored post by Tom Flynn posted

Show Post

Kamesh

Master

posted

Hide Post

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.

WFConsultant

WF 8105M on Win7/Tomcat

Posts: 780 | Location: Florida | Registered: January 09, 2005

Ignored post by Kamesh posted

Show Post

Tom Flynn

Expert

posted

Hide Post

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

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006

Ignored post by Tom Flynn posted

Show Post

Kamesh

Master

posted

Hide Post

Thanks for your thoughts.

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

WFConsultant

WF 8105M on Win7/Tomcat

Posts: 780 | Location: Florida | Registered: January 09, 2005

Ignored post by Kamesh posted

Show Post

dhagen

Virtuoso

posted

Hide Post

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

"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott

Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004

Ignored post by dhagen posted

Show Post

ypatel

Silver Member

posted

Hide Post

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