Reg:- FOCUS query

Hi,

I have FOCUS installed on Main Frames version. 7.1.1.

This is the first time I am writing a FOCUS program and I have encountered the below situation. Please help.

All together I have 5 files

FILE contents
---- --------
File1 Contains the course id
Course1 Contains course id and course desc
Course2 Contains course id and course desc
Course3 Contains course id and course desc
course4 contains course id and course desc

Now I have to fetch a single record with correct description of the course depending on the course id. The course id in file1 may be present in more than one course files. The order in which I have to fetch the right description is from file course4, course3, course2, course1.

In this situation what I can do is join file1 with course4, if there is no match then join file1 with course3 so on. I am unable to make out how will I findout if a join has not fetched any record.

Thanks
Sandesh Gadgil

Posts: 16 | Registered: December 20, 2005

N.Selph

Guru

posted

Hide Post

I would do something like:

  
SET ASNAMES= ON
MATCH FILE FILE1
SUM various fields
BY COURSEID
RUN
FILE COURSE4
SUM DESCRIPTION AS 'DESC4'
BY COURSEID
AFTER MATCH HOLD OLD
RUN
FILE COURSE3
SUM DESCRIPTION AS 'DESC3'
BY COURSEID
AFTER MATCH HOLD OLD
RUN
FILE COURSE2
SUM DESCRIPTION AS 'DESC2'
BY COURSEID
AFTER MATCH HOLD OLD
RUN
FILE COURSE1
SUM DESCRIPTION AS 'DESC1'
BY COURSEID
AFTER MATCH HOLD OLD
END

DEFINE FILE HOLD
DESCRIPTION/A40=IF DESC4 NE '' THEN DESC4 ELSE IF DESC3 NE '' THEN DESC3 ELSE IF DESC2 NE '' THEN DESC2 ELSE DESC1;
END

-etc

(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)

Posts: 391 | Location: California | Registered: April 14, 2003

Ignored post by N.Selph posted

Show Post

Sandesh

Member

posted

Hide Post

Thanks Selph ! I will try it out and update the same.

Thanks
Sandesh Gadgil

Posts: 16 | Registered: December 20, 2005

Ignored post by Sandesh posted