Hi,

I am having a small problem regarding arranging of different data from two different tables.

For example:

There are two tables Employee and Car
I want to extract out all the information of the cars owned by an employee. I want them to be clubbed together in the report

The output should be like below

Owners Information
Name, Emp No, Designation
Sam D’zouza, 100, Manager

Car Information
Model, Vehicle Number
Chevrolet, 1234
Audi, 5676
Ferrari, 6666

Owners Information
Name, Emp No, Designation
Tom Brown, 101, Sr. Engineer

Car Information
Model, Vehicle Number
Audi, 5555


And so on….

I am stuck on how to compare the employee name associated with each car.

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

Use subheadings for your displays.
JOIN EMP_NUMBER IN EMPLOYEE TO ALL EMP_NUMBER IN CAR
TABLE FILE EMPLOYEE
PRINT MODEL AS ''
VEHICLE_NUMBER AS ''
BY EMPL_NUMBER NOPRINT SUBHEAD
"Employee Information"
", , BY MODEL NOPRINT SUBHEAD
"Car Information"
"Model Vehicle Number"
END

You can then use your style sheet to assign fonts etc.

Swap,

Do you have the relationship between the employee# & vehicle# or something else to tie the owner and car together?

quote:

Originally posted by Hua:
Do you have the relationship between the employee# & vehicle# or something else to tie the owner and car together?

Yes infact, I do have a relationship between these two tables. I am joining it by Ids.

I tried to implement this by using MATCH function, but the ID is not matching up properly.

The match result is coming something like below

Emp Name Emp Id Car Name Emp Id
Jim 100 Mitsubishi 100
. 0 Lancer 100
. 0 Audi 100

you can see my problem,

the Match is assingning 0 value to the Emp Id field of the first table whereas I want it to be 100.

Are you using PRINT or SUM in the first part of the MATCH?

T

As it's Christmas, let me show you what I mean by the previous post.

First let us build some sample data. Jim likes trucks whilst Tim is into heavy armour -

EX -LINES 5 EDAPUT MASTER,SW_EMPFILE,CF,MEM
FILENAME=SW_EMPFILE, SUFFIX=FOC
SEGMENT=SEG01, SEGTYPE=S1, $
FIELDNAME=EMP_NO,   ALIAS=F01, USAGE=I3,  FIELDTYPE=I, $
FIELDNAME=EMP_NAME, ALIAS=F02, USAGE=A10, MISSING=ON, $

CREATE FILE SW_EMPFILE
MODIFY FILE SW_EMPFILE
FREEFORM EMP_NO EMP_NAME
DATA
100,JIM,$
200,TIM,$
END

EX -LINES 6 EDAPUT MASTER,SW_CARFILE,CF,MEM
FILENAME=SW_CAPFILE, SUFFIX=FOC
SEGMENT=SEG01, SEGTYPE=S1, $
FIELDNAME=EMP_NO,   ALIAS=F01, USAGE=I3,  FIELDTYPE=I, $
SEGMENT=SEG02, PARENT=SEG01, $
FIELDNAME=CAR_MAKE, ALIAS=F02, USAGE=A30, MISSING=ON, $

CREATE FILE SW_CARFILE
MODIFY FILE SW_CARFILE
FREEFORM EMP_NO CAR_MAKE
DATA
100,FORD,$
100,NISSAN,$
100,DODGE,$
200,ABRAHAM,$
200,SHERMAN,$
200,CHALLENGER,$
END

Now build a report using MATCH. Run this as is to begin with and then change the first PRINT to SUM instead and see the resultant output.

MATCH FILE SW_EMPFILE
PRINT EMP_NAME
-*  SUM EMP_NAME
   BY EMP_NO
RUN
FILE SW_CARFILE
PRINT CAR_MAKE
   BY EMP_NO
AFTER MATCH HOLD OLD
END

TABLE FILE HOLD
PRINT EMP_NAME
      CAR_MAKE
   BY EMP_NO
END

Now see how you can do the same with a join and therefore handle the data only once.

JOIN CLEAR *
JOIN EMP_NO IN SW_EMPFILE TO MULTIPLE EMP_NO IN SW_CARFILE AS J1

TABLE FILE SW_EMPFILE
PRINT EMP_NAME
      CAR_MAKE
   BY EMP_NO
END

I'll leave you to take Hua's suggestion on board for formatting.

Seasons greetings!

T

yes

its like

MATCH FILE EMP
PRINT *
BY EMP_ID NOPRINT
RUN
FILE CAR
PRINT *
BY EMP_ID NOPRINT
AFTER MATCH HOLD OLD-OR-NEW
ON TABLE HOLD AS H4
END

Also I want to process the input Data by Data i.e. Row by Row for my formatting to be correct

I think that I'll wait until you read the second part

T

You are all making it way more complicated than youe need to. Why use a MATCH when you can use a one to many join.
Apart from the small typo the code I suggested should do the job.
Have you tried it (with appropriate changes to column names etc?

This line
", , BY MODEL NOPRINT SUBHEAD
should be
BY MODEL NOPRINT SUBHEAD

You do not need to use MATCH.

I'll be trying your's and Tony's Suggestion suggestions and keep you posted. I am right now engaged in some other work.